MySQL 锁实践

测试环境为MySQL5.7 

隔离级别：REPEATABLE-READ

自动提交：autocommit OFF

创建测试表：

CREATE TABLE `t1` (

  `id` int(11) DEFAULT NULL,

  `name` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1
insert into t1 (id,name) values (1,"mirror");
insert into t1 (id,name) values (2,"mirror");
insert into t1 (id,name) values (3,"mirror");
insert into t1 (id,name) values (4,"mirror");
insert into t1 (id,name) values (5,"mirror");
insert into t1 (id,name) values (6,"mirror");
insert into t1 (id,name) values (7,"mirror");
insert into t1 (id,name) values (8,"mirror");
insert into t1 (id,name) values (9,"mirror");

一、模拟测试：S锁和X锁

1. t1表的id和name无索引，session 1 执行select语句，确认session 2是否会行锁

Session 1：

root@localhost : mirro:02: >select * from t1 where id=2;
+------+--------+
| id   | name   |
+------+--------+
|    2 | mirror |
+------+--------+
1 row in set (0.00 sec)

session 2：

root@localhost : mirro:10: >update t1 set name='zhang' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

结论：

当直接执行select语句的sql是没有锁的，不影响dml和dql语句的执行

2.t1表的id和name无索引，session 1 执行select语句加share 锁，确认session 2是否会行锁

session 1 :

root@localhost : mirro:22: >select * from t1 where id=2 lock in share mode;
+------+--------+
| id   | name   |
+------+--------+
|    2 | mirror |
+------+--------+
1 row in set (0.00 sec)

Session 2:

root@localhost : mirro:22: >update t1 set name='zhang' where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

结论：

当执行select语句并增加share锁，会与x锁互斥

3. session 1 sql增加share锁 ，session 2增加share 锁

Session 1：

root@localhost : mirro:47: >select * from t1 where id=2 lock in share mode;

+------+--------+

| id   | name   |

+------+--------+

|    2 | mirror |

+------+--------+

1 row in set (0.00 sec)

Session 2：

root@localhost : mirro:43: >select * from t1 where id=2 lock in share mode;
+------+--------+
| id   | name   |
+------+--------+
|    2 | mirror |
+------+--------+
1 row in set (0.00 sec)

总结：

1. S锁和S锁是共享的
2. S锁和X锁是互斥的

二、模拟测试：X锁和X锁

1. t1表的id和name无索引，session 1 执行update语句where id=1确认session 2执行update是否成功

session 1:

root@localhost : mirro:24: >update t1 set name='zhang' where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

session 2 ：

root@localhost : mirro:24: >update t1 set name='zhang' where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@localhost : mirro:26: >update t1 set name='zhang' where id
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

结论：

当session 1执行基于where条件id update语句，session 2无法执行执行基于where条件id update语句
说明当执行dml语句的where条件没有索引，则会造成表锁，其它Session都无法执行dml语句，dql不受影响

2.t1表的id和name无索引，session 1 执行update语句where条件为id >3 确认session 2执行update是否成功

session 1:

root@localhost : mirro:25: >update t1 set name='zhang' where id>3;

Query OK, 6 rows affected (0.00 sec)

Rows matched: 6  Changed: 6  Warnings: 0

session 2:

root@localhost : mirro:24: >update t1 set name='zhang' where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@localhost : mirro:26: >update t1 set name='zhang' where id
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@localhost : mirro:24: >update t1 set name='zhang' where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@localhost : mirro:26: >insert into t1 (id,name) values (15,"www");
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

结论：

当session 1执行基于where条件范围更新 update语句，session 2无法执行基于该表的任何dml语句，包含insert语句
说明当执行dml语句的where条件没有索引的范围更新则会造成表锁，其它Session都无法执行dml语句，dql不受影响

总结：

X锁和X锁是互斥的