MySQL行锁

MySQL存储引擎的发展是从MyISAM到InnoDB，锁从表锁发展到行锁。行锁的出现从某种程序上是为了弥补表锁的不足。比如MyISAM不支持事务，InnoDB支持事务。表锁虽然开销小，加锁快，但高并发下性能低。行锁偏向于InnoDB存储引擎，虽然开销大，加锁慢，锁定粒度小，会出现死锁，但高并发下性能更高。事务和行锁都是在确保数据准确的基础上提升并发的处理能力。因此，InnoDB存储引擎相比MyISAM而言，更适合高并发场景，同时支持事务处理。InnoDB与MyISAM相比最大不同之处也在于这两点：支持事务、采用行级锁。

行锁优缺	描述
劣势	开销大、加锁慢、会出现死锁
优势	粒度小、发生锁冲突几率小、处理并发能力强

行锁是MySQL中粒度最细的一种锁机制，只对当前所操作的行进行加锁，行锁发生冲突的概率很低，其粒度最小，但加锁的代价最大。行锁有分为共享锁（S锁）和排他锁（X锁）。

行锁	描述
S锁	读锁，共享锁
X锁	写锁，排他锁

InnoDB的行锁是通过给索引项添加锁来实现的，这一点MySQL与Oracle不同，Oracle是通过在数据库中对相应的数据行加锁来实现。InnoDB行锁只有在通过索引条件检索数据时才能使用，否则会直接使用表级锁。需要注意的是行级锁一定要使用索引。

InnoDB行锁的加锁的方式是自动加锁

• 对于UPDATE、DELETE、INSERT操作，InnoDB会自动给涉及数据集添加排他锁
• 对于SELECT操作，InnoDB不会添加任何锁

操作	语句	自动加锁
DQL	SELECT	无
DML	INSERT/DELETE/UPDATE	X锁

InnoDB手工加锁方式

LOCK IN SHARE MODE与FOR UPDATE只能在事务内其作用，以保证当前会话事务锁定的行不会被其他会话修改。

手工加锁	语句	重点
S锁	SELECT * FROM tblname WHERE 1=1 LOCK IN SHARE MODE	LOCK IN SHARE MODE
X锁	SELECT * FROM tblname WHERE 1=1 FOR UPDATE	FOR UPDATE

测试数据

DROP TABLE IF EXISTS test.accounts;
CREATE TABLE IF NOT EXISTS test.accounts(
    id INT(11) unsigned NOT NULL AUTO_INCREMENT,
    account VARCHAR(32) NOT NULL DEFAULT "" COMMENT "账号",
    balance INT(11) NOT NULL DEFAULT 0 COMMENT "余额",
    PRIMARY KEY(id),
    KEY account_index (account) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '账户';

INSERT INTO test.accounts(account, balance) VALUES (
FLOOR(RAND()*(1000000-100000)+100000), 
FLOOR(RAND()*(10000-1000)+1000)
);

SELECT * FROM test.accounts;

---

例如：事务中手工为主键列添加共享锁后，其他事务只能读取无法修改此行记录。

-	会话A	会话B
1	SET AUTOCOMMIT=0; BEGIN;	SET AUTOCOMMIT = 0; BEGIN;
2	SELECT * FROM test.accounts WHERE id=1 LOCK IN SHARE MODE;	SELECT * FROM test.accounts WHERE id=1;
3	UPDATE test.accounts SET account="alice" WHERE id=1;（成功）	-
4	SELECT * FROM test.accounts WHERE id=1;（修改后）	SELECT * FROM test.accounts WHERE id=1;（修改前）
5	SHOW PROCESSLIST; SHOW ENGINE INNODB STATUS;	UPDATE test.accounts SET account="ben" WHERE id=1;（等待）
6	COMMIT;	执行更新

1. 会话A与会话B设置禁止事务自动提交后开启事务
2. 会话A事务为主键ID为1的行添加共享锁，此时会话B事务可以读取该记录。
3. 会话A事务修改主键ID为1的行成功。此时在会话A中查看会是修改后的数据，而在会话B中查看则是未修改的数据。
4. 会话B事务修改主键ID为1的行，进程阻塞处于等待状态，无法操作。若一直不操作，最终锁定等待会超时。

1205 - Lock wait timeout exceeded; try restarting transaction

5. 会话A事务提交事务后，会话B事务的修改操作会立即执行。

会话B事务阻塞时查看进程状态

mysql> SHOW PROCESSLIST;
+----+------+-----------------+------+---------+------+----------+-----------------------------------------------------+
| Id | User | Host            | db   | Command | Time | State    | Info                                                |
+----+------+-----------------+------+---------+------+----------+-----------------------------------------------------+
| 12 | root | localhost:51626 | test | Sleep   |  411 |          | NULL                                                |
| 30 | root | localhost:62186 | test | Query   |    0 | NULL     | SHOW PROCESSLIST                                    |
| 31 | root | localhost:62193 | test | Query   |    8 | Updating | UPDATE test.accounts SET account="ben" WHERE id = 1 |
+----+------+-----------------+------+---------+------+----------+-----------------------------------------------------+
3 rows in set

会话B事务阻塞时查看引擎状态

mysql> SHOW ENGINE INNODB STATUS;
------------
TRANSACTIONS
------------
Trx id counter 12BA66
Purge done for trx's n:o < 12BA64 undo n:o < 0
History list length 961
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 32, OS thread handle 0x6600, query id 517 localhost 127.0.0.1 root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0, not started
MySQL thread id 12, OS thread handle 0x6980, query id 497 localhost 127.0.0.1 root
---TRANSACTION 12BA65, ACTIVE 391 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 31, OS thread handle 0x51d8, query id 512 localhost 127.0.0.1 root Updating
UPDATE test.accounts SET account="ben" WHERE id = 1
Trx read view will not see trx with id >= 12BA66, sees < 12BA64
------- TRX HAS BEEN WAITING 49 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2768 n bits 72 index `PRIMARY` of table `test`.`accounts` trx id 12BA65 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 00000012ba64; asc      d;;
 2: len 7; hex 530000025b0c14; asc S   [  ;;
 3: len 5; hex 616c696365; asc alice;;
 4: len 4; hex 80001883; asc     ;;

---

例如：LOCK IN SHARE MODE具有很高的风险，如果两个事务同时持有指定主键的S锁，将导致当前会话无法进行更新进而阻塞。

-	会话A	会话B
1	SET AUTOCOMMIT=0;	SET AUTOCOMMIT = 0;
2	BEGIN;	BEGIN;
3	SELECT * FROM test.accounts WHERE id=1 LOCK IN MODE;	SELECT * FROM test.accounts WHERE id=1 LOCK IN MODE;
4	UPDATE test.accounts SET account="root" WHERE id=1;（阻塞等待）	UPDATE test.accounts SET account="admin" WHERE id=1;（死锁提示）
5	SELECT * FROM test.accounts WHERE id=1;（修改后）	SELECT * FROM test.accounts WHERE id=1;（原始值）
6	COMMIT	-
7	SELECT * FROM test.accounts WHERE id=1;（修改后）	SELECT * FROM test.accounts WHERE id=1;（修改后）

死锁提示：MySQL会检测到发生死锁，最终会中断当前事务中更新语句的执行，重新开启新的事务。

1213 - Deadlock found when trying to get lock; try restarting transaction

---

FOR UPDATE添加排他锁不会产生死锁，因为会话添加X锁后其他会话除了能读取其他操作都是禁止的。

-	会话A	会话B
1	SET AUTOCOMMIT=0;	SET AUTOCOMMIT=0;
2	BEGIN;	BEGIN;
3	SELECT * FROM test.accounts WHERE id=1 FOR UPDATE;	-
4	SELECT * FROM test.accounts WHERE id=1;	SELECT * FROM test.accounts WHERE id=1;
5	UPDATE test.accounts SET account="admin" WHERE id=1;	UPDATE test.accounts SET account="root" WHERE id=1;（阻塞等待）
6	SELECT * FROM test.accounts WHERE id=1;	SELECT * FROM test.accounts WHERE id=1;
7	COMMIT	-

阻塞等待出现错误提示信息

1205 - Lock wait timeout exceeded; try restarting transaction

使用注意

• 会话的事务不能对已经添加了X锁的行再添加S锁
• 添加X锁的行的事务若一直不退出将会导致其他进行此行操作的会话一直阻塞

---

两阶段加锁策略

MySQL添加行锁会使用两阶段加锁策略，两阶段加锁协议表示整个事务分为两个阶段，前一个阶段为加锁，后一阶段为解锁。在加锁阶段事务只能加锁、可操作数据、但不能解锁，直到事务释放第一个锁后就进入解锁阶段。解锁极端只能解锁、可操作数据、但不能加锁。

两阶段锁协议使得事务具有较高的并发度，因为解锁不必发生在事务结尾。两阶段解锁策略的缺点是没有解决死锁的问题，因为它在加锁阶段没有顺序要求。比如两个事务分别申请了A和B锁，接着又申请对方的锁，此时进入死锁状态。

---

什么时候添加行锁呢？

正常SELECT是不会添加行锁的，只会加上MDL的读锁，即使SELET语句是全表扫描也不会添加行锁，只不过全表扫描查询较慢，但并不会因为锁的问题而对其他操作进行阻塞。

例如：对主键为1的记录添加行锁（共享锁）

SELECT * FROM t WHERE id=1 IN SHARE MODE;

例如：对主键为1的记录添加行锁（排他锁，叉锁）

SELECT * FROM t WHERE id=1 FOR UPDATE;

例如：对主键为1的记录添加行锁（排他锁，叉锁）

UPDATE t SET colname=1 WHERE id=1

例如：对非主键的字段所在记录添加行锁

UPDATE t SET fieldname=1 WHERE colname=1

• 若colname没有索引则添加的是普通表锁
• 若colname是非唯一索引则添加行锁
• 若colname是唯一索引则添加行锁的排他锁

需要注意的是在添加行锁之前，对所有列取得操作时首先会添加上MDL得读锁。

---

添加行锁后会带来什么影响呢？

行锁存在的意义时为了提高并发度，取代以前整表加锁而引起同一时刻只能有一个线程对数据表进行增删改的操作。

例如：事务开始时（begin）没有任何行锁被持有，只有当前具体操作，依次请求MDL读锁。事务B的UPDATE操作会被阻塞，因为ID为1记录的行锁被事务A锁持有。所有当前事务持有的行锁，在语句执行完都不会释放直到COMMIT提交之后才会释放。

#	TransactionA	TransactionB
1	BEGIN;
2	UPDATE tbl SET col = col + 1 WHERE id = 1;
3	UPDATE tbl SET col = col + 2 WHERE id = 2;
4		BEGIN;
5		UPDATE tbl SET col = col + 3 WHERE id = 1;
6	COMMIT;

越是并发度高的数据表，越要靠事务的后面写，因为持有行锁时间短，影响并发的时间阅读。