MySQL锁模型与事物 博客分类： SQL 锁事物
MySQL中不同的存储引擎支持不同的锁机制，比如，MySAM和MEMORY存储引擎采用表级锁，InnoDB支持表级锁和行级锁（默认采用行级锁），BDB（被InnoDB取代）支持表级锁和页面锁。
查看mysq提供的存储引擎：mysql> show engines;
查看mysql当前默认的存储引擎：mysql> show variables like '%storage_engine%';
查看某个表用的引擎：mysql> show create table 表名;
一、表级锁
1、表级锁特点
开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。当需要频繁对大部分数据做 GROUP BY 操作或者需要频繁扫描整个表时，推荐使用表级锁。
命令mysql> show status like 'Table%';
Table_locks_immediate 指的是能够立即获得表级锁的次数，而Table_locks_waited指的是不能立即获取表级锁而需要等待的次数。如果 Table_locks_waited的值比较大的话，则说明存在着较严重的表级锁争用情况，这是可能需要创建一个专有的缓存表，或者通过其它方式来减小表的大小，或者降低表级锁命令调用的频率。
2、表级锁的锁模式
读锁：成功申请读锁的前提是当前没有线程对该表使用写锁，否则该语句会被阻塞。申请读锁成功后，其他线程也可以对该表进行读操作，但不允许有线程对其进行写操作，包括当前线程。 用法：申请——LOCK TABLE table_name [ AS alias_name ] READ，释放——UNLOCK tables。
写锁：成功申请写锁的前提是当前没有线程对表加读锁和其他写锁，否则会被阻塞。写锁可以加优先级，当多个线程同时申请多种锁（LOW_PRIORITY,READ,WRITE）时，LOW_PRIORITY的优先级最低。 用法：申请——LOCK TABLE table_name [AS alias_name] [ LOW_PRIORITY ] WRITE。
3、如何给表加锁
MyISAM执行SELECT前会自动把涉及的所有的表加读锁，在执行UPDATE、DELETE、INSERT前会自动把涉及的所有的表加写锁。用户一般不需要直接用LOCK TABLE命令给MyISAM表加锁。
显式地给MyISAM表加锁，一般是为了模拟事物操作，实现在某一个时间点多个表的一致性读取。例如，order表记录了每个订单的总金额(total)，order_detail记录了每个订单中每个产品的金额小计(subtotal)，如果需要检查俩个表的金额是否一致，则：
Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Unlock tables;
注：local选项作用是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录
4、一个表锁导致阻塞的例子
|
Session1 |
Session2 |
|
CREATE TABLE my_contacts # 创建表联系人 ( id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR (30) NOT NULL, gender CHAR(1) NOT NULL DEFAULT 'M', #'M'或'F' birthday DATE, # 1980-09-15 phone VARCHAR (11) NOT NULL, information BLOB ) ENGINE = MYISAM ;
INSERT INTO my_contacts(NAME,gender,birthday,phone,information) VALUES ('ZhangSan','F','1990-09-09','13513513513','Friend'); INSERT INTO my_contacts(NAME,gender,birthday,phone,information) VALUES ('LiSi','F','1991-08-12','18989107021','strange boy'); |
|
|
LOCK TABLE my_contacts WRITE; |
|
|
SELECT * FROM my_contacts WHERE NAME="ZhangSan"; 返回查询结果 |
SELECT * FROM my_contacts WHERE NAME="LiSi"; 阻塞… … |
|
UNLOCK TABLES; |
阻塞… … |
|
|
返回查询结果 |
5、注意事项
Lock table时，如果查询语句中用到别名以及其他的表，那么别名和其他的表均需要锁住。如果锁住了别名，那么查询语句中就不能直接用表名了。在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。
LOCK TABLE my_contacts READ;
SELECT a.name ,b.math FROM my_contacts a, scores b WHERE a.`name`=b.`name`;
以上查询语句中，my_contacts被锁住了，但使用了别名a，a没有被锁住；表scores也没有被锁住，且scores也用了别名，修改为：
LOCK TABLE my_contacts as a READ, scores as b READ;
SELECT a.name ,b.math FROM my_contacts a, scores b WHERE a.`name`=b.`name`;
6、并发插入
一般而言，MyISAM表的读和写是串行的。在一定条件下，MyISAM表也支持查询和插入并发执行。
MyISAM有一个系统变量concurrent_insert，专用于控制并行插入的行为，其值可以为0、1或2。为0时，不允许并发插入；为1时，（默认设置），如果MyISAM表中没有空洞（即表的中间没有被删除的行），则允许一个进程读表，另一个进程在表尾插入；为2时，允许在表尾并发插入记录。
|
Session1 |
Session2 |
|
SHOW VARIABLES LIKE "%current_insert%"; current_insert的值为AUTO，（1） |
|
|
LOCK TABLE my_contacts READ LOCAL; 当前session可以查询，不能更新；其它session可以并发插入（如果无空洞），不能删除更新 |
|
|
INSERT INTO my_contacts (NAME,gender,birthday,phone,information) VALUES ('LiHong','F','1960-01-01','18956234756',''); 插入失败，my_contacts被locked； UPDATE my_contacts SET gender = 'M' WHERE NAME = 'ZhangSan'; 更新失败，my_contacts被locked； |
INSERT INTO my_contacts (NAME,gender,birthday,phone,information) VALUES ('WangHui','M','1990-01-01','13758694231',''); 并发插入成功；
|
|
SELECT id, gender FROM my_contacts WHERE NAME = 'ZhangSan'; 查询当前session内的记录成功； SELECT * FROM my_contacts WHERE NAME = 'WangHui'; 无法查询到并发插入的记录； |
UPDATE my_contacts SET gender = 'M' WHERE NAME = 'LiSi'; 更新操作被阻塞… … |
|
UNLOCK TABLES; |
被阻塞… … |
|
SELECT * FROM my_contacts WHERE NAME = 'WangHui'; 查询其它session插入的记录成功 |
更新记录成功 |
当一个表获得READ LOCAL锁后，该线程可以对表进行查询，不能更新，插入等，但其它线程可以并发插入，（concurrent_insert=2或者concurrent_insert=1且无空洞），但不能删除和更新。
MyISAM的并发插入特性可用来解决对同一表查询和插入的锁争用。例如，设置concurrent_insert=2，且通过空闲时段执行OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录产生的中间空洞。
7、MyISAM的粒调度
MySQL一般认为写请求比度请求更重要，所以一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，写进程会先获得锁，不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这时，可以通过一些设置来调节MyISAM 的调度行为。
-
通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
-
通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
-
通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
-
给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。
需要注意的是，一些需要长时间运行的查询操作，也会使写进程“饿死”！应用中应尽量避免长时间运行的查询操作，可以通过使用中间表等措施对SQL语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。
二、行级锁
InnoDB与MyISAM的最大不同点是：1、支持事物（Transaction），2、支持行级锁。锁定粒度最细的一种锁，能大大减少数据库操作的冲突，由于其粒度小，加锁的开销最大。
1、事物
事务是由一组SQL语句组成的逻辑处理单元，具有四个属性，即ACID： -
原子性（Atomicity）：对数据的修改，要么全都执行，要么全都不执行。
-
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引、双向链表）是正确的。
-
隔离性（Isolation）：事务在不受外部并发操作影响，事务处理过程中的中间状态对外不可见，反之亦然。
-
持久性（Durable）：事务完成后，它对于数据的修改是永久的，即使出现系统故障。
事务的并发执行带来的问题：
-
更新丢失：多个事物更新同一行时，由于每个事务都不知道其他事务的存在，就会导致最后的更新覆盖了由其他事务所做的更新。
-
脏读：一个事务正对一条记录进行修改，在提交事物之前，该记录的数据处于不一致状态，如果另一事物不加控制地读取同一条记录，就会读到 “脏”数据。
-
不可重复读：一个事务读取以前读过的数据，却发现该数据已发生了改变，甚至被删除。
-
幻读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据。
事务的隔离性
MyISAM不支持事物，InnoDB支持事物。定义隔离级别的语句如下：
SET TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED | READ COMMITTED |REPEATABLE READ |SERIALIZABLE ]查询事物的隔离级别：
1.查看当前会话隔离级别：select @@tx_isolation;
2.查看系统当前隔离级别：select @@global.tx_isolation;
3.设置当前会话隔离级别：set session transaction isolatin level repeatable read;
4.设置系统当前隔离级别：set global transaction isolation level repeatable read;
5.命令行开始事务：set autocommit=off 或者 start transaction
各个级别存在问题如下：
|
隔离级 |
脏读 |
不可重读 |
幻读 |
|
读未提交(Read uncommitted) |
可能 |
可能 |
可能 |
|
读提交(Read committed) |
不可能 |
可能 |
可能 |
|
可重复读(Repeatable read) |
不可能 |
不可能 |
可能 |
|
可串行化(Serializable) |
不可能 |
不可能 |
不可能 |
注：Oracle支持read commited、serializable以及自定义的read only；SQL Server支持以上四个以及自定义的“快照”；MySQL支持以上四个级别，默认为repeatable read。
Read uncommitted存在脏读
|
Session1 |
Session2 |
|
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 设置为读未提交 SELECT @@session.tx_isolation,@@global.tx_isolation;
START TRANSACTION; SET autocommit = 0; 开始事物 |
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 设置为读未提交
START TRANSACTION; SET autocommit = 0; 开始事物 |
|
SELECT * FROM my_contacts; |
|
|
|
INSERT INTO my_contacts(NAME,gender,birthday,phone,information) VALUES ('ZhaoXin','M','1990-01-01','1585652123',''); Session2插入记录，但是没有提交 |
|
SELECT * FROM my_contacts; 查询到Session2插入的记录。（脏读） |
|
|
|
ROLLBACK; 回滚提交 |
|
COMMIT; 提交 |
|
Read uncommited不会出现脏读，但是不可重复读
|
Session1 |
Session2 |
|
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 设置读提交 START TRANSACTION; SET autocommit = 0; 开始事物 |
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 设置读提交 START TRANSACTION; SET autocommit = 0; 开始事物 |
|
SELECT * FROM my_contacts; |
|
|
|
INSERT INTO my_contacts(NAME,gender,birthday,phone,information) VALUES ('ZhaoXin','M','1990-01-01','1585652123',''); Session2插入记录，但是没有提交 |
|
SELECT * FROM my_contacts; 查询不到Session2 插入的记录（不存在脏读） |
|
|
|
COMMIT; 提交，插入成功 |
|
SELECT * FROM my_contacts; 查询到Session2 插入的记录（不可重读） |
|
|
COMMIT; 提交 |
|
Repeatable Read不存在不可重读，但存在幻读
|
Session1 |
Session2 |
|
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; 设置重复读
START TRANSACTION; SET autocommit = 0; 开始事物 |
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; 设置重复读
START TRANSACTION; SET autocommit = 0; 开始事物 |
|
SELECT * FROM my_contacts; |
|
|
|
INSERT INTO my_contacts(NAME,gender,birthday,phone,information) VALUES ('ZhaoXin','M','1990-01-01','1585652123',''); Session2插入记录，但是没有提交 |
|
SELECT * FROM my_contacts; 查询不到Session2 插入的记录（不存在脏读） |
|
|
|
COMMIT; 提交，插入成功 |
|
SELECT * FROM my_contacts; 查询不到Session2 插入的记录（当前session内多次查询的结果保持不变，可重复读） |
|
|
COMMIT; 提交 |
|
|
SELECT * FROM my_contacts; 查询到Session2 插入的记录 |
|
|
Session1 |
Session2 |
|
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; 设置重复读
START TRANSACTION; SET autocommit = 0; 开始事物 |
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; 设置重复读
START TRANSACTION; SET autocommit = 0; 开始事物 |
|
SELECT * FROM my_contacts; |
|
|
|
INSERT INTO my_contacts(id,NAME,gender,birthday,phone,information) VALUES (5,'ZhaoXin','M','1990-01-01','1585652123',''); |
|
|
COMMIT; 提交，插入成功 |
|
SELECT * FROM my_contacts; 查询不到Session2 插入的记录（当前session内多次查询的结果保持不变，可重复读） |
|
|
INSERT INTO my_contacts(id,NAME,gender,birthday,phone,information) VALUES (5,'ZhouQi','M','1990-01-01','13966623200',''); 不知道id=5被session2插入记录，导致Duplicate entry '5' for key 'PRIMARY' |
|
Repeatable Read下的幻读现象可以用间隙锁解决，即先把某个范围内的（例如id=5）不存在的记录锁住，其它事物插入记录时就被阻塞，当前事物就可以正常插入。
Serializable不存在幻读，它是串行化执行读和写、写与写。
|
Session1 |
Session2 |
|
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; 设置可串行化
START TRANSACTION; SET autocommit = 0; 开始事物 |
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; 设置可串行化
START TRANSACTION; SET autocommit = 0; 开始事物 |
|
SELECT * FROM my_contacts; |
|
|
|
INSERT INTO my_contacts(id,NAME,gender,birthday,phone,information) VALUES (5,'ZhaoXin','M','1990-01-01','1585652123',''); 被阻塞… … |
|
COMMIT; |
|
|
|
阻塞解除 |
|
SELECT * FROM my_contacts; 被阻塞… … |
|
|
|
COMMIT; |
|
阻塞解除 |
|
2、InnoDB的行级锁的锁模型
-
共享锁(shared lock，S)： SELECT ...LOCK IN SHARE MODE; 当没有其他线程对查询结果集中的任何一行使用排他锁时，可以对查询结果中的每行都加共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据，但是不能获取该数据集合的排它锁。
-
排它锁(exclusive lock，X)：SELECT ...LOCK FOR UPDATE; 当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功对查询结果中的每行都加排他锁，否则会被阻塞。获得排它锁的事务可以更新数据，而其他事务不能获取相同数据集的共享读锁和排他写锁。
-
意向共享锁（IS）：事务在给一个数据行加共享锁前必须先取得该表的IS锁。
-
意向排他锁（IX）：事务在给一个数据行加排他锁前必须先取得该表的IX锁。
统一数据集的两个行级锁的兼容性：
|
|
S |
IS |
X |
IX |
|
S |
兼容 |
兼容 |
冲突 |
冲突 |
|
IS |
|
兼容 |
冲突 |
兼容 |
|
X |
|
|
冲突 |
冲突 |
|
IX |
|
|
|
兼容 |
InnoDB共享锁的例子
|
Session1 |
Session2 |
|
alter table my_contacts engine=innodb; SET autocommit = 0; SELECT * FROM my_contacts WHERE id=2; |
SET autocommit = 0; SELECT * FROM my_contacts WHERE id=2; |
|
SELECT * FROM my_contacts WHERE id=2 LOCK IN SHARE MODE; 加共享锁成功 |
|
|
|
SELECT * FROM my_contacts WHERE id=2 LOCK IN SHARE MODE; 加共享锁成功 INSERT INTO my_contacts(NAME,gender,birthday,phone,information) VALUES ('ZhouLiang','F','1990-01-01','13645685552',''); 插入数据（没有被锁住）成功 |
|
UPDATE my_contacts SET gender='M' WHERE id = 2; 更行数据（被锁住），等待 |
|
|
|
UPDATE my_contacts SET gender='M' WHERE id = 2; 更新数据（被锁住），造成死锁，执行失败，释放锁 |
|
获得锁后更行成功 |
|
InnoDB排它锁的例子
|
Session1 |
Session2 |
|
SET autocommit = 0; SELECT * FROM my_contacts WHERE id=2; |
SET autocommit = 0; SELECT * FROM my_contacts WHERE id=2; |
|
SELECT * FROM my_contacts WHERE id=2 FOR UPDATE; 申请排它锁成功 |
|
|
|
SELECT * FROM my_contacts WHERE id=2;查询成功；可以查询，更新、加锁会阻塞 SELECT * FROM my_contacts WHERE id=2 LOCK IN SHARE MODE; 加共享锁，阻塞… … |
|
UPDATE my_contacts SET gender='M' WHERE id = 2; 更新操作，成功 COMMIT; 释放锁 |
|
|
|
阻塞解除，添加共享锁成功 |
3、InnoDB行级锁争用情况
mysql> show status like 'innodb_row_lock%';
如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。
mysql> CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
mysql> Show innodb status\G;
停止监视器：mysql> DROP TABLE innodb_monitor;
在SHOW INNODB STATUS的显示内容中，会有详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等，便于进行进一步的分析。长时间打开打开监视器会导致.err文件变得非常大，所以确认问题原因后，要记得删除监控表以关闭监视器。
4、InnoDB行级锁的实现方式
InnoDB行锁是通过给索引项加锁来实现的，这一点与Oracle不同，Oracle是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。
|
Session1 |
Session2 |
|
CREATE TABLE tab_no_index(id INT,NAME VARCHAR(10)) ENGINE=INNODB; INSERT INTO tab_no_index VALUES(1,'1'),(2,'2'),(3,'3'),(4,'4'); 创建无索引表 SET autocommit=0; SELECT * FROM tab_no_index WHERE id = 1 ; |
SET autocommit=0; SELECT * FROM tab_no_index WHERE id = 2; |
|
SELECT * FROM tab_no_index WHERE id = 1 FOR UPDATE; 对id=1设置行级锁，由于无索引，导致实际上设置了表级锁 |
|
|
|
SELECT * FROM tab_no_index WHERE id = 2 FOR UPDATE; 被阻塞了… … |
|
Session1 |
Session2 |
|
CREATE TABLE tab_with_index(id INT,NAME VARCHAR(10)) ENGINE=INNODB; ALTER TABLE tab_with_index ADD INDEX id(id); INSERT INTO tab_with_index VALUES(1,'1'),(2,'2'),(3,'3'),(4,'4'); 创建有索引表 SET autocommit=0; SELECT * FROM tab_with_index WHERE id = 1 ; |
SET autocommit=0; SELECT * FROM tab_with_index WHERE id = 2; |
|
SELECT * FROM tab_with_index WHERE id = 1 FOR UPDATE; 对id=1设置行级锁，设置成功 |
|
|
|
SELECT * FROM tab_with_index WHERE id = 2 FOR UPDATE; 对id=2申请行级锁成功 |
由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
|
Session1 |
Session2 |
|
insert into tab_with_index values(1,'4'); set autocommit=0; |
set autocommit=0; |
|
select * from tab_with_index where id = 1 and name = '1' for update; 申请行级锁成功，id有索引，name没有索引 |
|
|
|
SELECT * FROM tab_with_index WHERE id = 1 AND NAME = '4' FOR UPDATE; 尽管查询的行记录与session1中的不同，但是使用了相同的索引，所以需要等待，阻塞… … |
当表有多个索引（无论是主键索引、唯一索引或普通索引）的时候，不同的事务可以使用不同的索引锁定不同的行。
|
Session1 |
Session2 |
|
alter table tab_with_index add index name(name); id和name均有索引 set autocommit=0; |
set autocommit=0; |
|
select * from tab_with_index where id = 1 for update; 申请行级锁成功，(1,’1’),(1,’4’)被锁定，即id=1，name=’1’，name=’4’被锁定 |
|
|
|
select * from tab_with_index where name = '2' for update; name=’2’没有被锁定，申请排它锁成功 |
|
|
SELECT * FROM tab_with_index WHERE id=4 AND NAME = '4' FOR UPDATE; 尽管查询的记录中与session1的记录无交集，但是name=’4’被锁定了，阻塞… … |
是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了通过用explain检查SQL的执行计划，以确认是否真正使用了索引。
ALTER TABLE tab_no_index ADD INDEX NAME(NAME);
EXPLAIN SELECT * FROM tab_no_index WHERE NAME = 1; #有索引，但是没有使用
EXPLAIN SELECT * FROM tab_with_index WHERE NAME = '1'; #使用了索引
5、间隙锁（Next-Key）
当使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB不仅会给符合条件的记录的索引项加锁，而且还会对键值在条件范围内但并不存在的记录加锁，这种锁机制叫间隙锁（Next-Key锁）。例如，Select * from my_contacts where id > 5 for update;会将id>5的所有记录（即使不存在）加锁。
InnoDB使用间隙锁的目的，一是为了防止幻读，以满足相关隔离级别的要求，如果上面的select语句不使用间隙锁，如果其他事务插入id大于5的记录，那么本事务如果再次执行上述语句，就会发生幻读；另一方面是为了满足其恢复和复制的需要。
显然，使用范围条件检索并锁定记录时，间隙锁会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尽量避免使用范围条件.
|
Session1 |
Session2 |
|
select @@tx_isolation; 隔离级别为REPEATABLE-READ set autocommit = 0; |
select @@tx_isolation; 隔离级别为REPEATABLE-READ set autocommit = 0; |
|
select * from temp where id = 6 for update; 当前没有id=6的记录，但是该不存在的记录仍然被锁住 |
|
|
|
insert into temp(id,...) values(6,...); 阻塞… … |
|
rollback; |
|
|
|
Session1回退后释放了Next-Key锁，当前session获得锁并成功插入记录 |
恢复和复制对InnoDB锁机制的影响MySQL
通过BINLOG录执行成功的INSERT、UPDATE、DELETE等更新数据的SQL语句，并由此实现MySQL数据库的恢复和主从复制。MySQL的恢复是SQL语句级的，也就是重新执行BINLOG中的SQL语句。MySQL的Binlog是按照事务提交的先后顺序记录的，恢复也是按这个顺序进行的。由此可知，MySQL的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录（不允许出现幻读）。这也是许多情况下，无论在Read Commited或是Repeatable Read隔离级别下，InnoDB都要使用间隙锁。
另外，对于“insert into target_tab select * from source_tab where ...”和“create table new_tab ...select ... From source_tab where ...(CTAS)”这种SQL语句，用户并没有对source_tab做任何更新操作，但MySQL对这种SQL语句做了特别处理。
|
Session1 |
Session2 |
|
mysql> set autocommit = 0; mysql> select * from target_tab; mysql> select * from source_tab where name = '1'; |
mysql> set autocommit = 0; mysql> select * from target_tab; mysql> select * from source_tab where name = '1'; |
|
insert into target_tab select d1,name from source_tab where name = '1'; |
|
|
|
update source_tab set name = '1' where name = '8'; 等待… … 因为source_table被session1锁住了 |
|
commit; |
|
|
|
commit; |
这里InnoDB给source_tab加了共享锁，为什么要这么做呢？其原因是为了保证恢复和复制的正确性。因为不加锁的话，如果在上述语句执行过程中，其他事务对source_tab做了更新操作，就可能导致数据恢复的结果错误。
为了演示这一点，重复一下前面的例子，不同的是在session1执行事务前，先将系统变量 innodb_locks_unsafe_for_binlog的值设置为“on”（其默认值为off），这时执行update source_tab set name = '1' where name = '8'时不会被锁住。最后执行的结果是：source_tab的内容update了，target_tab插入update前的内容，（也就是所期望的先执行session1的insert，后执行session2的update）。分析binlog发现update语句在insert前执行，如果按照binlog恢复数据，那么恢复的数据与实际应用不符。
这也就是为什么没有使用MySQL的多版本数据一致性读技术，而是用共享锁锁住source_tab。
附注：一致性读是相对于脏读而言的，如果查询一个有10000条记录的表T需要10min，在9:00读表T，9:10返回结果，但是9:05的时候另外一个事物删除了表T的一条记录，那么当前事物的查询结果是10000条记录还是9999条记录呢？如果是9999条，则发生了脏读，如果是10000条，则发生了一致性读。
INSERT...SELECT...和 CREATE TABLE...SELECT...语句，可能会阻止对源表的并发更新，造成对源表锁的等待。如果查询比较复杂的话，会造成严重的性能问题，在应用中应尽量避免使用。如果必须要实现这样的逻辑，要么牺牲binlog的恢复数据，要么组合间接实现该逻辑。
InnoDB存储引擎中不同SQL在不同隔离级别下锁比较
|
Read Uncommited |
Read Commited |
Repeatable Read |
Serializable |
||
|
SQL |
条件 |
|
|
|
|
|
select |
相等 |
None locks |
Consisten read/None lock |
Consisten read/None lock |
Share locks |
|
范围 |
None locks |
Consisten read/None lock |
Consisten read/None lock |
Share Next-Key |
|
|
update |
相等 |
exclusive locks |
exclusive locks |
exclusive locks |
Exclusive locks |
|
范围 |
exclusive next-key |
exclusive next-key |
exclusive next-key |
exclusive next-key |
|
|
Insert |
N/A |
exclusive locks |
exclusive locks |
exclusive locks |
exclusive locks |
|
replace |
无键冲突 |
exclusive locks |
exclusive locks |
exclusive locks |
exclusive locks |
|
键冲突 |
exclusive next-key |
exclusive next-key |
exclusive next-key |
exclusive next-key |
|
|
delete |
相等 |
exclusive locks |
exclusive locks |
exclusive locks |
exclusive locks |
|
范围 |
exclusive next-key |
exclusive next-key |
exclusive next-key |
exclusive next-key |
|
|
Select ... from ... Lock in share mode |
相等 |
Share locks |
Share locks |
Share locks |
Share locks |
|
范围 |
Share locks |
Share locks |
Share Next-Key |
Share Next-Key |
|
|
Select * from ... For update |
相等 |
exclusive locks |
exclusive locks |
exclusive locks |
exclusive locks |
|
范围 |
exclusive locks |
Share locks |
exclusive next-key |
exclusive next-key |
|
|
Insert into ... Select ... （指源表锁） |
innodb_locks_unsafe_for_binlog=off |
Share Next-Key |
Share Next-Key |
Share Next-Key |
Share Next-Key |
|
innodb_locks_unsafe_for_binlog=on |
None locks |
Consisten read/None lock |
Consisten read/None lock |
Share Next-Key |
|
|
create table ... Select ... （指源表锁） |
innodb_locks_unsafe_for_binlog=off |
Share Next-Key |
Share Next-Key |
Share Next-Key |
Share Next-Key |
|
innodb_locks_unsafe_for_binlog=on |
None locks |
Consisten read/None lock |
Consisten read/None lock |
对于许多SQL，隔离级别越高，InnoDB给记录集加的锁就越严格（尤其是使用范围条件的时候），产生锁冲突的可能性也就越高，从而对并发性事务处理性能的影响也就越大。因此，应该尽量使用较低的隔离级别，以减少锁争用的机率。实际上，通过优化事务逻辑，大部分应用使用Read Commited隔离级别就足够了。对于一些确实需要更高隔离级别的事务，可以通过在程序中执行SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ或SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE动态改变隔离级别的方式满足需求。
6、什么时候使用表锁
对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。
1、事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
2、事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。
在InnoDB下，使用表锁要注意以下两点。
1、使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的。仅当autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB才能自动识别涉及表级锁的死锁；。
2、用 LOCK TABLES对InnoDB表加锁时，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁； COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。正确的方式见如下语句：
SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;
MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。
InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，了在InnoDB中发生死锁是可能的。
|
Session1 |
Session2 |
|
mysql> set autocommit = 0; mysql> select * from table_1 where where id=1 for update; |
mysql> set autocommit = 0; mysql> select * from table_2 where id=1 for update; |
|
select * from table_2 where id =1 for update; 因Session2已取得排他锁，等待 |
|
|
|
mysql> select * from table_1 where where id=1 for update; 死锁 |
在上面的例子中，两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁或表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要设置锁等待超时参数 innodb_lock_wait_timeout来解决。但是如果有大量事物，仍然会造成挂死，拖累性能。
通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句，绝大部分死锁都可以避免。
1、如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
2、批量方式处理数据时，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
|
session1 |
session2 |
|
mysql> set autocommit=0; |
mysql> set autocommit=0; |
|
mysql> select first_name,last_name from actor where actor_id = 1 for update; |
|
|
|
mysql> select first_name,last_name from actor where actor_id = 3 for update; |
|
mysql> select first_name,last_name from actor where actor_id = 3 for update; 等待 |
|
|
|
mysql> select first_name,last_name from actor where actor_id = 1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
|
等待解除 |
|
3、在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁。
4、在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。
|
session1 |
session2 |
|
mysql> select @@tx_isolation; | REPEATABLE-READ | mysql> set autocommit = 0; |
mysql> select @@tx_isolation; | REPEATABLE-READ | mysql> set autocommit = 0; |
|
对不存在的记录加排它锁成功 mysql> select actor_id,first_name,last_name from actor where actor_id = 201 for update; |
|
|
|
对不存在的记录加排它锁成功 mysql> select actor_id,first_name,last_name from actor where actor_id = 201 for update; |
|
因为其他session也对该记录加了锁，所以当前的插入会等待： mysql> insert into actor (actor_id , first_name , last_name) values(201,'Lisa','Tom'); |
|
|
|
mysql> insert into actor (actor_id, first_name , last_name) values(201,'Lisa','Tom'); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
|
插入成功 |
|
5、当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。
对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁
|
Session1 |
Session2 |
Session3 |
|
mysql> select @@tx_isolation; | READ-COMMITTED | mysql> set autocommit=0; |
mysql> select @@tx_isolation; | READ-COMMITTED | mysql> set autocommit=0; |
mysql> select @@tx_isolation; | READ-COMMITTED | mysql> set autocommit=0; |
|
mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update; 空记录 |
mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update; 空记录 |
|
|
mysql> insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom'); 成功插入 |
|
|
|
|
mysql> insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom'); 等待锁 |
|
|
mysql> commit; |
|
|
|
|
Session2获得锁，发现插入记录主键重，这个时候抛出了异常，但是并没有释放锁： mysql> insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom'); ERROR 1062 (23000): Duplicate entry '201' for key 'PRIMARY' |
|
|
|
|
Session3申请获锁，因为session_2已经锁定该记录，所以session_3需要等待： mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update; 等待 |
|
|
这个时候，如果Session2直接对记录进行更新操作，则会抛出死锁的异常： mysql> update actor set last_name='Lan' where actor_id = 201; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
|
|
|
|
Session2释放锁后，Session3获得锁： mysql> select first_name, last_name from actor where actor_id = 201 for update; |
尽管通过上面介绍的设计和SQL优化等措施，可以大大减少死锁，但死锁很难完全避免。因此，在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。
如果出现死锁，可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的SQL语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。