MySQL锁的应用

我不是女神ヾ 2021-09-17 18:14 281阅读 0赞

在MySQL中，处理并发读或者写时，可以通过实现一个由两种类型的锁组成的锁系统。这两种类型的锁通常被称为：共享锁（Share Lock）和排他锁（Exclusive Lock），也叫读锁（Read Lock）和写锁（Writer Lock）。按锁粒度可以分为：表锁（Table Lock）和行级锁（Row Lock）。

表锁（Table Lock）：表锁是MySQL中最基本的锁策略，并且是开销最小的策略。

行级锁（Row Lock）：行级锁可以最大程度地支持并发处理（同时也带来了最大的锁开销）。

**注意：**MyISAM只有表锁，没有行锁，锁又分为读锁和写锁。而InnoDB拥有表锁和行锁。

在MySQL中，InnoDB和BDB类型表可以支持事务处理，但是MySQL中MyISAM类型表并不能支持事务处理，对于某些应用该类型的数据表，用户可以选择应用表锁定来替代事务。这种应用表锁定来替代事务的事件被称为伪事务。使用表锁来锁定表的操作，可以加强非事务表在执行过程中的安全性和稳定性。

## 1、数据库读写中的并发事务问题 ##

（1）脏读（Dirty Read）：在事务的执行过程中，读取到了其他事务的未提交的数据，即读到了脏数据。

（2）不可重复读（Unrepeatable Read）：在事务的执行过程中，读到了其他事务修改后的数据，换句话说在该事务中的不同时间点读取到了不一致的数据，即不可重复读。

（3）幻读/虚读（Phantom Read）：在事务的执行过程中，读取到了其他事务对记录数修改后的数据，对同一张表的两次查询的 COUNT(\*) 不一致。

**不可重复读与幻读的区别：**

不可重复读：强调的是数据内容的不一致，主要针对 UPDATE 的修改。

幻读：强调的是记录数的不一致，主要针对 INSERT/DELETE 的修改。

## 2、用表锁定代替事务 ##

在MySQL的MyISAM类型数据表中，并不支持COMMIT（提交）和ROLLBACK（回滚）命令。当用户对数据库执行插入、删除、更新等操作时，这些变化的数据都被立刻保存在磁盘中。这样，在多用户环境中，会导致诸多问题，为了避免同一时间有多个用户对数据库中指定表进行操作。可以应用表锁定来避免在用户操作数据表过程中受到干扰。当且仅当该用户释放表的操作锁定后，其他用户才可以访问这些修改后的数据表。

为指定数据表添加锁定，其语法如下。

LOCK TABLE table_name1 lock_type1,table_name2 lock_type2,...

参数说明：

（1）table\_name：被锁定的表名。

（2）lock\_type：锁定类型，该类型包括以读方式（READ）锁定表；以写方式（WRITE）锁定表。

### 2.1 以读方式锁定数据表 ###

以读方式锁定数据表，该方式是设置锁定用户的其他操作，如删除、插入、更新都不被允许，直到用户进行解锁操作。

**示例：**以读方式锁定tb\_user（用户信息表），并尝试往该表中插入数据。

LOCK TABLE tb_user READ;

当执行读方式锁定表后，尝试往该表中插入数据。

提示错误信息：\[Err\] 1099 - Table 'tb\_user' was locked with a READ lock and can't be updated

### 2.2 以写方式锁定数据表 ###

以写方式锁定数据表，该方式设置用户可以修改数据表中数据，但是除自己以外其他会话中的用户不能进行任何操作。

**示例：**以写方式锁定tb\_user（用户信息表）。

LOCK TABLE tb_user WRITE;

当执行写方式锁定表后，只有当前会话的用户可以执行查看或操作；但其他会话中的用户对该表的查看或操作都处于“等待中”的状态，只有等待该表解锁后，才能继续执行。

### 2.3 解锁 ###

用户完成对锁定数据表的操作后，需要对该表进行解锁操作，释放该表的锁定状态，其语法如下。

UNLOCK TABLES;

使用UNLOCK TABLES命令后，将会释放所有当前处于锁定状态的数据表。

## 3、应用表锁实现伪事务 ##

**实现伪事务的一般步骤如下：**

（1）对数据库中的数据表进行锁定操作，可以对多个表做不同的方式锁定。

（2）执行数据库操作，向锁定的数据表中执行添加、删除、修改操等操作。

（3）释放锁定的数据表，以便让正在队列中等待查看或操作的其他用户可以浏览数据表中的数据或对操作表执行各种数据的操作。

## 4、死锁的概念与避免 ##

死锁，即当两个或者多个处于不同序列的用户打算同时更新某相同的数据库时，因互相等待对方释放权限而导致双方一直处于等待状态。在实际应用中，两个不同序列的客户打算同时对数据执行操作，极有可能产生死锁。更具体的讲，当两个事务相互等待操作对方释放所持有的资源，而导致两个事务都无法操作对方持有的资源，这样无限期的等待被称为死锁。

不过，MySQL的InnoDB表处理程序具有检查死锁这一功能，如果该处理程序发现用户在操作过程中产生死锁，该处理程序立刻通过撤销操作来撤销其中一个事务，以便使死锁消失。这样就可以使另一个事务获取对方所占有的资源而执行逻辑操作。