MySQL的在RC和RR模式下的锁

InnoDB的锁机制：数据库使用所是为了支持更好的并发，提供数据的完整性和一致性。InnoDB是一个支持锁的存储引擎，锁的类型有：共享锁（S）、排它锁（X）、意向共享锁（IS）、意向排它锁（IX）。为了支持更好的并发，InnoDB提供了非锁定读：不需要等待访问行上的锁释放，读取行的一个快照。该方法是通过InnoDB的一个特写：MVCC实现的。InnoDB的锁分类：Record Lock：行锁：单个行记录上的行锁Gap Lock：间隙锁，锁定一个范围，但不包括记录本身Next-Key Lock：Gap+Record Lock，锁定一个范围，并且锁定记录本身

无索引+RC/RR当对无索引的字段进行更新时（RR级别），通过锁主键的方式，来锁住所有记录，RC级别不会锁所有记录。构建表及初始化数据：REPEATABLE-READ（RR）默认级别SessionASessionBroot@localhost[zjkj]:10:53:18>prompt A>>PROMPT set to ‘A>>’A>>select @@session.tx_isolation;root@localhost[(none)]:11:02:58>prompt B>>PROMPT set to ‘B>>’B>>select @@session.tx_isolation;A>>begin;Query OK, 0 rows affected (0.00 sec)B>>begin;Query OK, 0 rows affected (0.00 sec)A>>select * from t_none;+—-+——–+| id | mem_id |+—-+——–+| 1 | 1 || 3 | 3 || 5 | 5 || 9 | 9 || 11 | 11 |+—-+——–+5 rows in set (0.00 sec)B>>select * from t_none;+—-+——–+| id | mem_id |+—-+——–+| 1 | 1 || 3 | 3 || 5 | 5 || 9 | 9 || 11 | 11 |+—-+——–+5 rows in set (0.00 sec)
A>>select * from t_none where mem_id=3 for update;+—-+——–+| id | mem_id |+—-+——–+| 3 | 3 |+—-+——–+1 row in set (0.01 sec)B>>insert into t_none values(2,2);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionB>>delete from t_none where id=9;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionshow engin inondb status部分输出：————TRANSACTIONS————Trx id counter 10661Purge done for trx’s n:o History list length 351Total number of lock structs in row lock hash table 2LIST OF TRANSACTIONS FOR EACH SESSION:—TRANSACTION 10588, not startedMySQL thread id 4, OS thread handle 0x7f6f5085c700, query id 339 localhost root initshow engine innodb status—TRANSACTION 10660, ACTIVE 17 sec insertingmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)MySQL thread id 11, OS thread handle 0x7f6f508de700, query id 338 localhost root updateinsert into t_none values(2,2)——- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 68 page no 3 n bits 72 index `PRIMARY` of table `test`.`t_none` trx id 10660 lock_mode X locks gapbefore rec insert intention waiting结论：通过上面很容易的看到，没有通过索引forupdate时，当进行增删改都会锁住，MySQL内部会通过基于锁默认主键方式，对所有记录加X锁。下面是RC级别的实验ReadCommitted级别（RC）SessionASessionBA>>set @@session.tx_isolation=”read-committed”;Query OK, 0 rows affected (0.00 sec)B>>set @@session.tx_isolation=”read-committed”;Query OK, 0 rows affected (0.00 sec)A>>select @@session.tx_isolation;+————————+| @@session.tx_isolation |+————————+| READ-COMMITTED |+————————+1 row in set (0.00 sec)B>>select @@session.tx_isolation;+————————+| @@session.tx_isolation |+————————+| READ-COMMITTED |+————————+1 row in set (0.01 sec)A>>begin;Query OK, 0 rows affected (0.00 sec)B>>begin;Query OK, 0 rows affected (0.00 sec)A>>select * from t_none where mem_id=3 for update;+—-+——–+| id | mem_id |+—-+——–+| 3 | 3 |+—-+——–+1 row in set (0.01 sec)B>>insert into t_none values(2,2);Query OK, 1 row affected (0.01 sec)B>>select * from t_none;+—-+——–+| id | mem_id |+—-+——–+| 1 | 1 || 2 | 2 || 3 | 3 || 5 | 5 || 9 | 9 || 11 | 11 |+—-+——–+6 rows in set (0.00 secA>>rollback;Query OK, 0 rows affected (0.00 sec)B>>rollback;Query OK, 0 rows affected (0.00 sec)结论：在RC级别下，事务B是可以进行增删改（除被锁定的记录本身）非唯一索引+RR/RC 在RR级别下，InnoDB对于非唯一索引会加Gap Lock（也即锁定一个区间），而在RC级别下无。构造初始化表及数据：
SessionASessionBroot@localhost[(none)]:06:01:59>use test;root@localhost[zjkj]:10:53:18>prompt A>>PROMPT set to ‘A>>’root@localhost[(none)]:06:01:59>use test;root@localhost[(none)]:11:02:58>prompt B>>PROMPT set to ‘B>>’A>>select @@session.tx_isolation;+————————+| @@session.tx_isolation |+————————+| REPEATABLE-READ |+————————+1 row in set (0.00 sec)B>>select @@session.tx_isolation;+————————+| @@session.tx_isolation |+————————+| REPEATABLE-READ |+————————+1 row in set (0.02 sec)A>>begin;Query OK, 0 rows affected (0.00 sec)B>>begin;Query OK, 0 rows affected (0.00 sec)A>>select * from t_idx;+—-+——–+| id | mem_id |+—-+——–+| 1 | 1 || 3 | 3 || 5 | 5 || 9 | 9 || 11 | 11 |+—-+——–+5 rows in set (0.04 sec)B>>select * from t_idx;+—-+——–+| id | mem_id |+—-+——–+| 1 | 1 || 3 | 3 || 5 | 5 || 9 | 9 || 11 | 11 |+—-+——–+5 rows in set (0.00 sec)A>>select * from t_idx where mem_id=3 for update;+—-+——–+| id | mem_id |+—-+——–+| 3 | 3 |+—-+——–+1 row in set (0.05 sec)B>>insert into t_idx values(2,2);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction#问题？这里为什么会出现阻塞呢？B>>insert into t_idx values(4,4);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction#问题？这里为什么会出现阻塞呢？B>>insert into t_idx values(3,3);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionB>>insert into t_idx values(5,5);ERROR 1062 (23000): Duplicate entry ‘5’ for key ‘PRIMARY’B>>insert into t_idx values(1,1);ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘PRIMARY’#######下面插入全部可以######B>>insert into t_idx values(6,6);Query OK, 1 row affected (0.00 sec)B>>insert into t_idx values(7,7);B>>insert into t_idx values(8,8);Query OK, 1 row affected (0.01 sec)B>>insert into t_idx values(12,12);Query OK, 1 row affected (0.00 sec)B>>select * from t_idx;+—-+——–+| id | mem_id |+—-+——–+| 1 | 1 || 3 | 3 || 5 | 5 || 6 | 6 || 7 | 7 || 8 | 8 || 9 | 9 || 11 | 11 || 12 | 12 |+—-+——–+9 rows in set (0.00 sec)————TRANSACTIONS————Trx id counter 11044Purge done for trx’s n:o History list length 372Total number of lock structs in row lock hash table 5LIST OF TRANSACTIONS FOR EACH SESSION:—TRANSACTION 0, not startedMySQL thread id 3, OS thread handle 0x7fd0430df700, query id 47 localhost root initshow engine innodb status—TRANSACTION 11039, ACTIVE 228 sec insertingmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 4MySQL thread id 1, OS thread handle 0x7fd064099700, query id 45 localhost root up开发云主机域名dateinsert into t_idx values(4,4)Trx read view will not see trx with id >= 11040, sees ——- TRX HAS BEEN WAITING 22 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 70 page no 4 n bits 80 index `idx_mem_id` of table `test`.`t_idx` trx id 11039 lock_mode X locks gapbefore rec insert intention waitinSessionASessionBA>>rollback;Query OK, 0 rows affected (0.00 sec)B>>rollback;Query OK, 0 rows affected (0.00 sec)A>>set @@session.tx_isolation=”read-committed”;Query OK, 0 rows affected (0.00 sec)B>>set @@session.tx_isolation=”read-committed”;Query OK, 0 rows affected (0.00 sec)A>>select @@session.tx_isolation;+————————+| @@session.tx_isolation |+————————+| READ-COMMITTED |+————————+1 row in set (0.00 sec)B>>select @@session.tx_isolation;+————————+| @@session.tx_isolation |+————————+| READ-COMMITTED |+————————+1 row in set (0.01 sec)A>>begin;Query OK, 0 rows affected (0.00 sec)B>>begin;Query OK, 0 rows affected (0.00 sec)A>>select * from t_idx where mem_id=3 for update;+—-+——–+| id | mem_id |+—-+——–+| 1 | 3 || 3 | 3 |+—-+——–+2 rows in set (0.00 sec)B>>insert into t_idx values(1,1);ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘PRIMARY’B>>insert into t_idx values(2,2);Query OK, 1 row affected (0.00 sec)B>>insert into t_idx values(3,3);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionB>>insert into t_idx values(4,4);Query OK, 1 row affected (0.01 sec)
唯一索引+RR/RC构造初始化表及数据：root@localhost[(none)]:10:04:34>use test;root@localhost[test]:10:04:41>prompt A>>PROMPT set to ‘A>>’root@localhost[(none)]:10:04:37>use test;root@localhost[test]:10:04:52>prompt B>>PROMPT set to ‘B>>’A>>select @@session.tx_isolation;+————————+| @@session.tx_isolation |+————————+| REPEATABLE-READ |+————————+1 row in set (0.01 sec)B>>select @@session.tx_isolation;+————————+| @@session.tx_isolation |+————————+| REPEATABLE-READ |+————————+1 row in set (0.00 sec)A>>begin;Query OK, 0 rows affected (0.00 sec)B>>begin;Query OK, 0 rows affected (0.00 sec)A>>select * from t_pk;+—-+——–+| id | mem_id |+—-+——–+| 1 | 1 || 3 | 3 || 5 | 5 || 9 | 9 || 11 | 11 |+—-+——–+5 rows in set (0.00 sec)B>>select * from t_pk;+—-+——–+| id | mem_id |+—-+——–+| 1 | 1 || 3 | 3 || 5 | 5 || 9 | 9 || 11 | 11 |+—-+——–+5 rows in set (0.00 sec)A>>select * from t_pk where mem_id=3 for update;+—-+——–+| id | mem_id |+—-+——–+| 3 | 3 |+—-+——–+1 row in set (0.00 sec)B>>insert into t_pk values(2,2);Query OK, 1 row affected (0.00 sec)B>>insert into t_pk values(4,4);Query OK, 1 row affected (0.00 sec)B>>insert into t_pk values(3,3);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionB>>insert into t_pk values(5,5);ERROR 1062 (23000): Duplicate entry ‘5’ for key ‘PRIMARY’B>>insert into t_pk values(7,7);Query OK, 1 row affected (0.00 sec)同理，我们可以推导出主键也是一样的。实验的话我就略了，其实就是将上面的mem_id改成id即可。A>>rollback;Query OK, 0 rows affected (0.00 sec)B>>rollback;Query OK, 0 rows affected (0.00 sec)A>>begin;Query OK, 0 rows affected (0.00 secB>>begin;Query OK, 0 rows affected (0.00 sec)A>>select * from t_pk where id=3 for update;+—-+——–+| id | mem_id |+—-+——–+| 3 | 3 |+—-+——–+1 row in set (0.00 sec)B>>insert into t_pk values(2,2);Query OK, 1 row affected (0.00 sec)B>>insert into t_pk values(4,4);Query OK, 1 row affected (0.00 sec)A>>rollback;Query OK, 0 rows affected (0.00 sec)B>>rollback;Query OK, 0 rows affected (0.00 sec)A>>set @@session.tx_isolation=”read-committed”;Query OK, 0 rows affected (0.01 sec)B>>set @@session.tx_isolation=”read-committed”;Query OK, 0 rows affected (0.00 sec)A>>select @@session.tx_isolation;+————————+| @@session.tx_isolation |+————————+| READ-COMMITTED |+————————+1 row in set (0.00 sec)B>>select @@session.tx_isolation;+————————+| @@session.tx_isolation |+————————+| READ-COMMITTED |+————————+1 row in set (0.00 sec)A>>begin;Query OK, 0 rows affected (0.00 sec)B>>begin;Query OK, 0 rows affected (0.00 sec)A>>select * from t_pk;+—-+——–+| id | mem_id |+—-+——–+| 1 | 1 || 3 | 3 || 5 | 5 || 9 | 9 || 11 | 11 |+—-+——–+5 rows in set (0.00 sec)B>>select * from t_pk;+—-+——–+| id | mem_id |+—-+——–+| 1 | 1 || 3 | 3 || 5 | 5 || 9 | 9 || 11 | 11 |+—-+——–+5 rows in set (0.00 sec)A>>select * from t_pk where mem_id=3 for update;+—-+——–+| id | mem_id |+—-+——–+| 3 | 3 |+—-+——–+1 row in set (0.00 sec)B>>insert into t_pk values(2,2);Query OK, 1 row affected (0.00 sec)B>>insert into t_pk values(4,4),(6,6),(10,10);Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0主键+RR/RC这跟唯一索引+RR/RC是一样的，请参看上面的唯一索引+RR/RC。

相关推荐: Percona-XtraDB-Cluster的编译安装

环境：操作系统centos7.4，mysql 安装在/data/mysql下，mysql属于mysql用户，pxc-5.7.191.1 下载pxc-5.7.19mkdir -p /home/sunx/tools wget -P /home/sunx/tools…