共享锁与排它锁

1.测试数据准备

 

CREATE TABLE `test` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

INSERT INTO `test` (`id`, `a`, `b`)
VALUES
	(1, 5, 1),
	(2, 10, 2),
	(3, 15, 3),
	(4, 20, 4),
	(5, 25, 5);

2.共享锁（S锁，MyISAM 叫做读锁）

添加方式：SELECT … LOCK IN SHARE MODE

SELECT * FROM test WHERE id = 1 LOCK IN SHARE MODE；

说明：事务A中添加共享锁后，事务A可对数据进行读写操作，其他事务可对数据进行读操作或也添加共享锁。若事务AB同时使用共享锁。那么事务AB都不能对该数据进行写操作。

 

测试场景一：更新阻塞。A事务添加共享锁，B事务进行读、写操作

事务A	事务B	说明
BEGIN;	BEGIN;	开启事务
SELECT * FROM test WHERE id = 1 LOCK IN SHARE MODE;		事务A，添加共享锁
	SELECT * FROM test WHERE id = 1;	事务B，查询成功（见图片2.1）
	update test set b = 100 where id = 1;	事务B，update操作阻塞（见图片2.2）
COMMIT;		事务A提交事务后，释放锁； 事务B update成功。
	COMMIT;

 

 

 

 

 

 

 

 

 

 

 

 

 

图片2.1   事务B，可查询

 

 

 图片2.2   事务B update操作阻塞

 

 

 测试场景二：死锁 。事务A添加共享锁，事务B也添加共享锁，事务A进行更新操作，事务B也进行更新操作

 

事务A	事务B	说明
BEGIN;	BEGIN;	开启事务
SELECT * FROM test WHERE id = 1 LOCK IN SHARE MODE;		事务A，添加共享锁
	SELECT * FROM test WHERE id = 1 LOCK IN SHARE MODE;	事务B，添加共享锁成功（见图片2.3）
update test set b = 100 where id = 1;		事务A，进行update操作阻塞(见图片2.4)
	update test set b = 101 where id = 1;	事务B，进行update操作，发生死锁，抛出异常（Deadlock found when trying to get lock; try restarting transaction），事务回滚并释放锁(见图片2.5)； 事务A，update更新成功
COMMIT;

 

 

 

 

 

 

 

 

 

 

 

 

图片 2.3   事务B添加共享锁成功

 

 

图片 2.4  事务A，进行update操作阻塞

 

 

图片2.5  事务B，进行update操作，发生死锁

 

排它锁（X锁，MyISAM 叫做写锁）

添加方式：SELECT … FOR UPDATE;

SELECT * FROM test WHERE id = 1 FOR UPDATE; 

说明：update,delete,insert 操作会自动添加排他锁，添加排他锁后，不能再添加其他的锁。事务A中添加排它锁。事务A可对数据进行读写操作。事务B只能对数据进行读操作。

 

 测试场景一：添加其他的锁等待。事务A添加排它锁，事务B也添加排它锁或共享锁。

事务A	事务B	说明
BEGIN;	BEGIN;
SELECT * FROM test WHERE id = 1 FOR UPDATE;		事务A，添加排他锁
	SELECT * FROM test WHERE id = 1 LOCK IN SHARE MODE;	事务B，添加共享锁，等待（见图片3.1）
COMMIT;		事务A，提交事务，释放锁； 事务B，添加锁成功
	COMMIT;

 

 

 

 

 

 

 

 

 

图3.1  事务B添加，共享锁（或排它锁）等待