mysql 进阶 (四) 聊聊 mysql 中的事务锁机制

一、语法

SELECT...FOR UPDATE 语句的语法如下：

SELECT ... FOR UPDATE [OF column_list][WAIT n|NOWAIT][SKIP LOCKED]; 

其中：OF 子句用于指定即将更新的列，即锁定行上的特定列。WAIT 子句指定等待其他用户释放锁的秒数，防止无限期的等待。

“使用FOR UPDATE WAIT”子句的优点如下：

• 防止无限期地等待被锁定的行； 

• 允许应用程序中对锁的等待时间进行更多的控制。

• 对于交互式应用程序非常有用，因为这些用户不能等待不确定；

• 若使用了skip locked，则可以越过锁定的行，不会报告由wait n 引发的‘资源忙’异常报告。

注意事项：FOR UPDATE 仅适用于 InnoDB，且必须在事务区块(BEGIN/COMMIT)中才能生效。作用：锁定该语句所选择到的对象。防止在选择之后别的地方修改这些对象造成数据不一致。要保证在统计（查询）执行过程中，记录不被其他用户更新，则可以使用For update子句进行加锁。这样，在这个锁释放前其他用户不能对这些记录作 update、delete 和加锁。

Select daptno from dept Where deptno=25 For update；

如果你使用了FOR UPDATE来对表进行加锁，则必须用commit来释放加锁的记录。

二、锁分类

锁分成两类：加锁范围子句和加锁行为子句。

加锁范围子句：在select…for update之后，可以使用of子句选择对select的特定数据表进行加锁操作。默认情况下，不使用of子句表示在select所有的数据表中加锁。

加锁行为子句：当我们进行for update的操作时，与普通select存在很大不同。一般select是不需要考虑数据是否被锁定，最多根据多版本一致读的特性读取之前的版本。

规则： for UPDATE语句将锁住查询结果中的元组，这些元组将不能被其他事务的UPDATE，delete和for UPDATE操作，直到本事务提交。UPDATE语句将锁住查询结果中的元组，这些元组将不能被其他事务的UPDATE，delete和for UPDATE操作，直到本事务提交。

三、应用场景

那么，什么时候需要使用for update？就是那些需要业务层面数据独占时，可以考虑使用for update。场景上，比如火车票订票，在屏幕上显示余票，而真正进行出票时，需要重新确定一下这个数据没有被其他客户端修改。所以，在这个确认过程中，可以使用for update。这是统一的解决方案方案问题，需要前期有所准备。由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL 才会执行Row lock (只锁住被选取的资料表) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。

举例 1：select * from t for update 会等待行锁释放之后，返回查询结果。select * from t for update nowait 不等待行锁释放，提示锁冲突，不返回结果select * from t for update wait 5 等待 5 秒，若行锁仍未释放，则提示锁冲突，不返回结果select * from t for update skip locked 查询返回查询结果，但忽略有行锁的记录。

四、数据一致性问题

锁是数据库中的一个非常重要的概念，它主要用于多用户环境下保证数据库完整性和一致性。 我们知道，多个用户能够同时操纵同一个数据库中的数据，会发生数据不一致现象。即如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题。这些问题包括：丢失更新、脏读、不可重复读和幻觉读：

1．当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。例如，两个编辑人员制作了同一文档的电子复本。每个编辑人员独立地更改其复本，然后保存更改后的复本，这样就覆盖了原始文档。最后保存其更改复本的编辑人员覆盖了第一个编辑人员所做的更改。如果在第一个编辑人员完成之后第二个编辑人员才能进行更改，则可以避免该问题。

2. 脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。例如，一个编辑人员正在更改电子文档。在更改过程中，另一个编辑人员复制了该文档（该复本包含到目前为止所做的全部更改）并将其分发给预期的用户。此后，第一个编辑人员认为目前所做的更改是错误的，于是删除了所做的编辑并保存了文档。分发给用户的文档包含不再存在的编辑内容，并且这些编辑内容应认为从未存在过。如果在第一个编辑人员确定最终更改前任何人都不能读取更改的文档，则可以避免该问题。

3．不可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。

4．幻觉读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。

所以，处理多用户并发访问的方法是加锁。锁是防止其他事务访问指定的资源控制、实现并发控制的一种主要手段。当一个用户锁住数据库中的某个对象时，其他用户就不能再访问该对象。加锁对并发访问的影响体现在锁的粒度上。为了控制锁定的资源，应该首先了解系统的空间管理。

五、应用示例

例 1: (明确指定主键，并且有此笔资料，row lock)

SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

例 2: (明确指定主键，若查无此笔资料，无 lock)

SELECT * FROM products WHERE id='-1' FOR UPDATE;

例 2: (无主键，table lock)

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例 3: (主键不明确，table lock)

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

例 4: (主键不明确，table lock)

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注意事项

• FOR UPDATE仅适用于 InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。

• 要测试锁定的状况，可以利用MySQL的Command Mode ，开二个视窗来做测试。

• MyAsim 只支持表级锁，InnerDB支持行级锁。

添加了(行级锁/表级锁)锁的数据不能被其它事务再锁定，也不被其它事务修改（修改、删除）是表级锁时，不管是否查询到记录，都会锁定表。

此外，如果 A 与 B 都对表 id 进行查询但查询不到记录，则 A 与 B 在查询上不会进行 row 锁，但 A 与 B 都会获取排它锁，此时 A 再插入一条记录的话则会因为 B 已经有锁而处于等待中，此时 B 再插入一条同样的数据则会抛出 Deadlock found when trying to get lock; try restarting transaction 然后释放锁，此时 A 就获得了锁而插入成功。

​