MySQL基础篇(06)：事务管理，锁机制案例详解

本文源码：[GitHub·点这里](https://github.com/cicadasmile/mysql-data-base) || [GitEE·点这里](https://github.com/cicadasmile/mysql-data-base) # 一、锁概念简介 ## 1、基础描述 锁机制核心功能是用来协调多个会话中多线程并发访问相同资源时，资源的占用问题。锁机制是一个非常大的模块，贯彻MySQL的几大核心难点模块：索引，锁机制，事务。这里是基于MySQL5.6演示的几种典型场景，对面MySQL这几块问题时，有分析流程和思路是比较关键的。在MySQL中常见这些锁概念：共享读锁、排它写锁 ; 表锁、行锁、间隙锁。 ## 2、存储引擎和锁 - MyISAM引擎：基于读写两种模式，支持表级锁 ; - InnoDB引擎：支持行级别读写锁，跨行的间隙锁,InnoDB也支持表锁 ; ## 3、锁操作API - LOCK TABLE name [READ,WRITE] ;加表锁 - UNLOCK TABLES ; 释放标所 # 二、MyISAM锁机制 ## 1、基础描述 MySQL的表级锁有两种模式：共享读锁（Read-Lock）和排它写锁（Write-Lock）。针对MyISAM表的读操作，不会阻塞其他线程对同一表的读请求，但阻塞对同一表的写请求；针对MyISAM表的写操作，会阻塞其他线程对同一表的读和写操作；MyISAM引擎读写操作之间，以及写与写操作之间是串行化。当一次会话线程获取表的写锁后，只有当前持有锁的会话线程可以对表进行操作。其它线程的读、写操作都会等待，直到锁被释放为止。 ## 2、验证案例 基于上面的表锁机制特点，使用下面两个案例验证。 - **基础表结构** ``` CREATE TABLE `dc_user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id', `user_name` varchar(20) DEFAULT NULL COMMENT '用户名', `tell_phone` varchar(20) DEFAULT NULL COMMENT '手机号', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户表'; CREATE TABLE `dc_user_info` ( `user_id` int(11) NOT NULL COMMENT '用户ID', `city` varchar(20) DEFAULT NULL COMMENT '城市', `country` varchar(20) DEFAULT NULL COMMENT '国家', PRIMARY KEY (`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户信息表'; ``` - **共享读锁**  **会话窗口一** ```sql -- 1、加读锁 LOCK TABLE dc_user READ ; -- 2、当前会话查询，OK SELECT * FROM dc_user ; -- 4、当前会话写入，Error INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998'); -- 6、查询其他表,Error SELECT * FROM dc_user_info ; -- 7、释放锁 UNLOCK TABLES ; ``` **会话窗口二** ```sql -- 3、其他会话查询，OK SELECT * FROM dc_user ; -- 5、其他会话写入，Error INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998'); -- 8、再次执行写入读取，OK INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998'); SELECT * FROM dc_user ; ``` 这里验证表锁的共享读机制。 - **排它写锁**  这里验证表锁的排它写机制。 - **查询锁争用** 通过下面语句查看配置， ```sql show status like 'table%'; ``` Table_locks_waited的值越大，锁争用情况越严重，效率则越低下。 ## 3、并发写入问题 针对排它写锁的测试案例再说明：在一定条件下，MyISAM表也支持查询和插入操作的并发执行。通过配置系统变量concurrent_insert的值[0,1,2]，可以实现并发写入。 - concurrent_insert=0，禁止并发写入; - concurrent_insert=1，默认配置AUTO，在MyISAM表中没有空洞，即表的中间没有被删除的行，例如[1,2,3],删除2之后[1,,3]，则允许在读表的同时，另一个线程从表尾写入记录。 - concurrent_insert=2，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。 在下面的例子中，session_1获得了一个表的READ LOCAL锁，该线程可以对表进行查询操作，但不能对表进行更新操作；其他的线程（session_2），虽然不能对表进行删除和更新操作，但却可以对该表进行并发插入操作，这里假设该表中间不存在空洞。 ## 4、优先级问题 MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。但是当一个读操作和写操作同时请求，写数据会优先获得锁，这一机制可以通过配置修改，指定配置参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。 通过执行命令SET - LOW_PRIORITY_UPDATES=1，使该会话的写操作优先级降低。 - 指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。 ## 5、表锁应用 数据一致性校验问题，比如销售量+剩余库存=货品总量，在校验时就要在一次会话中同时锁住订单表和库存表，免得在读取订单表的时候，库存表被修改，导致数据误差出现。 # 三、InnoDB锁机制 ## 1、事务基础概念 - **事务概念** 事务是指作为单个逻辑工作单元执行的一系列操作（SQL语句）。这些操作要么全部成功，要么全部不成功。 - **事务特性ACID** **原子性(Atomicity)**：事务中的多个操作要么都成功要么都失败 **一致性(consistency)**：事务的执行的前后数据的完整性保持一致 **隔离性(isolation)**：事务执行的过程中,不应该受到其他事务的干扰 **持久性(durability)**：事务一旦结束,数据就持久到数据库 - **事务问题** **脏读**：一个事务读到另一个事务没有提交的数据 **不可重复读**：一个事务前后多次读取相同数据，数据内容不一致，update场景问题 **虚读(幻读)**：一个事务前后多次读取，数据总量不一致，insert场景问题 - **隔离级别** **read uncommitted**:事务可以读取另一个未提交事务的数据。 **read committed**：事务要等另一个事务提交后才能读取数据,解决脏读。 **repeatable read**：在开始读取数据时,事务开启,不再允许修改操作,解决：脏读、不可重复读。 **serializable**：最高事务隔离级别，事务串行化顺序执行，解决脏读、不可重复读、幻读。但是效率低下，耗数据库性能。 ## 2、锁机制描述 InnoDB与MyISAM的最大不同有两点：一是支持事务TRANSACTION,二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来新问题：并发，死锁等。 - 共享锁：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上共享锁，则事务T可以读A但不能修改A，其他事务只能再对A加共享锁，而不能加写锁，直到T释放A上的共享锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。 - 排他锁：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的资源的共享读锁和排他锁。若事务T对数据对象A加上写锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的写锁。 ## 3、验证案例 - **基础表结构** ```sql CREATE TABLE `dc_user_in01` ( `id` int(11) DEFAULT NULL COMMENT 'id', `user_name` varchar(20) DEFAULT NULL COMMENT '用户名', `tell_phone` varchar(20) DEFAULT NULL COMMENT '手机号' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'; CREATE TABLE `dc_user_in02` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id', `user_name` varchar(20) DEFAULT NULL COMMENT '用户名', `tell_phone` varchar(20) DEFAULT NULL COMMENT '手机号', PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='用户表'; ``` **`注意结构`**：表dc_user_in01主键没有索引。表dc_user_in02主键有索引，但是都使用INNODB存储引擎，下面验证案例会有不同。 - **无索引结构表**  **会话窗口一** ```sql -- 1、关闭自动提交 SET AUTOCOMMIT = 0 ; -- 2、查询id=1，OK SELECT * FROM dc_user_in01 WHERE id=1 ; -- 3、添加写锁失败 SELECT * FROM dc_user_in01 WHERE id=1 FOR UPDATE ; -- 4、恢复事务提交 SET AUTOCOMMIT = 1 ; ``` **会话窗口二** ```sql -- 1、关闭自动提交 SET AUTOCOMMIT = 0 ; -- 2、查询id=2，OK SELECT * FROM dc_user_in01 WHERE id=2 ; -- 3、写入失败(等待) INSERT INTO dc_user_in01 (id,user_name,tell_phone) VALUES (3,'lock01','13267788998'); -- 4、写锁失败(等待) SELECT * FROM dc_user_in01 WHERE id=2 FOR UPDATE ; -- 5、恢复事务提交 SET AUTOCOMMIT=1 ; ``` - **索引结构表**  **会话窗口一** ```sql -- 1、关闭自动提交 SET AUTOCOMMIT = 0 ; -- 2、查询id=1，OK SELECT * FROM dc_user_in02 WHERE id=1 ; -- 3、添加写锁成功 SELECT * FROM dc_user_in02 WHERE id=1 FOR UPDATE ; -- 执行到这里，再执行窗口2 -- 4、恢复事务提交 SET AUTOCOMMIT = 1 ; ``` **会话窗口二** ```sql -- 1、关闭自动提交 SET AUTOCOMMIT = 0 ; -- 2、查询id=2，OK SELECT * FROM dc_user_in02 WHERE id=2 ; -- 3、查询id=1，OK，加读锁 SELECT * FROM dc_user_in02 WHERE id=1 ; -- 4、写入成功 INSERT INTO dc_user_in02 (user_name,tell_phone) VALUES ('lock01','13267788998'); -- 5、加写锁成功，id为2的 SELECT * FROM dc_user_in02 WHERE id=2 FOR UPDATE ; -- 6、加写锁失败(等待)，占用id为1的 SELECT * FROM dc_user_in02 WHERE id=1 FOR UPDATE ; -- 7、恢复事务提交 SET AUTOCOMMIT=1 ; ``` - **索引失效问题** 这里要注意索引是否被使用问题，在很多查询中，可能因为种种原因导致索引不执行。 ```sql explain SELECT * FROM dc_user_in02 WHERE id=1 ; ``` - **查询锁争用** ```sql show status like 'innodb_row_lock%'; ``` Innodb_row_lock_waits和Innodb_row_lock_time_avg的值越大，锁争用情况越严重，效率则越低下。 ## 4、Next-Key锁 - **官方文档说明** 为了防止幻读，InnoDB使用了一种名为Next-Key锁定的算法，它将记录锁和间隙锁定结合在一起即：InnoDB在执行行级锁的时候，会用这种方式-扫描索引记录，会在符合索引条件的记录上加共享锁或者独占锁。 ```sql [Next-Key]=[Record-lock]+[Gap-lock] ``` 如果说上面的几种锁机制给人的感觉是昏天暗地，那个这个Next-Key算法就会叫人怀疑人生。 - **验证案例** 这里主要验证Gap-lock间隙锁的存在机制。 ``` CREATE TABLE `dc_gap` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id', `id_index` int(11) NOT NULL COMMENT 'index', PRIMARY KEY (`id`), KEY `id_index` (`id_index`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='间隙表'; INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('1', '2'); INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('3', '4'); INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('6', '7'); INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('8', '7'); INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('9', '9'); ```  **会话窗口一** ```sql -- 1、开始事务 START TRANSACTION ; -- 3、锁定id_index=7的两条记录 SELECT * FROM dc_gap WHERE id_index=7 FOR UPDATE ; -- 9、提交 COMMIT ; ``` **会话窗口二** ```sql -- 2、开始事务 START TRANSACTION ; -- 4、写入等待,id_index=6 INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('4', '6'); -- 5、写入等待,id_index=4 INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('4', '4'); -- 6、写入成功,id_index=3 INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('4', '3'); -- 7、写入等待,id_index=9 INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('7', '9'); -- 8、写入成功,id_index=10 INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('7', '10'); ``` 7向上到4有间隙，7向下到9有间隙，所以间隙锁定[4,9]，且包含首尾值。 ## 5、Dead-Lock锁 - **基础描述** 两个或者多个事务在同一个资源上相互占用，并请求锁定对方占用的资源，从而导致死循环现象，也就是死锁。 - **验证案例**  **会话窗口一** ```sql -- 1、开启事务 START TRANSACTION ; -- 3、占用id=6的资源 SELECT * FROM dc_gap WHERE id=6 FOR UPDATE ; -- 5、占用id=9的资源等待 SELECT * FROM dc_gap WHERE id=9 FOR UPDATE ; ``` **会话窗口二** ```sql -- 2、开启事务 START TRANSACTION ; -- 4、占用id=9的资源 SELECT * FROM dc_gap WHERE id=9 FOR UPDATE ; -- 6、占用id=6的资源抛死锁 SELECT * FROM dc_gap WHERE id=6 FOR UPDATE ; ``` `补刀一句`：数据库实现各种死锁检测机制，或者死锁超时等待结束，InnoDB存储引擎在检测到死锁后，会立即返回错误，不然两个事务会隔空对望，一眼万年。 `注意`：死锁在事务型业务中，是无法绝对避免的，锁定资源少，粒度细，尽量避免该情况出现。 # 四、源代码地址 ``` GitHub·地址 https://github.com/cicadasmile/mysql-data-base GitEE·地址 https://gitee.com/cicadasmile/mysql-data-base ```