mysql小白系列_07 锁与事务

1.MySQL参数autocommit生产环境设1还是0？为什么？

2.MySQL参数tx_isolation生产环境上大多数是设什么值，为什么？

3.与MySQL锁相关的有哪些因素？

1.MySQL参数autocommit生产环境设1还是0？为什么？

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)
  • 查看select @@autocommit;
  • 临时修改set autocommit=0;
  • 自动提交，innodb引擎特有，未提交的数据仅存在buffer中
  • 提交后的数据写入数据文件
  • 使用了begin;，autocommit参数不生效
  • 建议设置为1，并且使用三段式写法begin...dml...commit;
  • select带有metadata lock，如果为0（未提交），会阻塞DDL操作

2.MySQL参数tx_isolation生产环境上大多数是设什么值，为什么？

https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html#isolevel_serializable

mysql> show variables like '%tx_isolation%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
  • 读未提交 READ-UNCOMMITED
  • 读已提交 READ-COMMITTED
  • 可重复读 REPEATABLE-READ
  • 串行化 SERIALIZABLE
读未提交 READ-UNCOMMITED

一个事务可以读取其他事务未提交的结果
允许脏读、幻读、不可重复读，隔离级别最低，并发最高
实际应用基本不用

  1. A会话设置事务隔离级别为READ-UNCOMMITTED
mysql A>set tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql A>select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)
  1. A/B会话查看相同的表数据，此时表数据一致
mysql A>select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yzw  |
|    2 | yzw2 |
+------+------+
2 rows in set (0.00 sec)

mysql B>select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yzw  |
|    2 | yzw2 |
+------+------+
2 rows in set (0.00 sec)
  1. B会话更新记录但不提交
mysql B>start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql B>update t1 set name='yzw1' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql B>select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yzw1 |
|    2 | yzw2 |
+------+------+
2 rows in set (0.00 sec)
  1. A会话查看结果
mysql A>select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yzw1 |
|    2 | yzw2 |
+------+------+
2 rows in set (0.00 sec)

此时A会话读到了B会话开启的未提交的事务

  1. B会话执行rollback
mysql B>rollback;
Query OK, 0 rows affected (0.00 sec)

mysql B>
mysql B>select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yzw  |
|    2 | yzw2 |
+------+------+
2 rows in set (0.00 sec)
  1. A会话再查看数据
mysql A>select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yzw  |
|    2 | yzw2 |
+------+------+
2 rows in set (0.00 sec)

B会话回滚后，A会话读到了初始数据

读已提交 READ-COMMITTED
  • 一个事务能读取其他事务已经提交的结果，与上面相反
  • 用得最多，并发高，生产环境使用此级别，
  • oracle/mssql/pg用的是此级别
  • 无法解决不可重复读和幻读
  1. A会话设置事务隔离级别为READ-COMMITTED
mysql A>set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql A>SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)
  1. A/B会话查看相同的表数据，此时表数据一致
mysql A>select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yzw  |
|    2 | yzw2 |
+------+------+
2 rows in set (0.00 sec)

mysql B>select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yzw  |
|    2 | yzw2 |
+------+------+
2 rows in set (0.00 sec)
  1. B会话更新记录但不提交
mysql B>start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql B>update t1 set name='yzw1' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql B>select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yzw1 |
|    2 | yzw2 |
+------+------+
2 rows in set (0.00 sec)

此时B会话已经看到数据更改

  1. A会话查看结果
mysql A>select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yzw  |
|    2 | yzw2 |
+------+------+
2 rows in set (0.00 sec)

此时A会话无法读到B会话开启的未提交的事务

  1. B会话执行commit;
mysql B>commit;
Query OK, 0 rows affected (0.02 sec)

mysql B>select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yzw1 |
|    2 | yzw2 |
+------+------+
2 rows in set (0.00 sec)
  1. A会话再查看数据
mysql A>select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yzw1  |
|    2 | yzw2 |
+------+------+
2 rows in set (0.00 sec)

B会话提交后，完成一个事务，A会话读取到B会话做的提交

可重复读 REPEATABLE-READ

可以读取到已提交的结果，mysql默认级别
并发能力比读已提交稍弱，真正的隔离级别，并解决了读已提交无法解决的不可重复读和幻读的问题
与READ_COMMITTED区别是，REPEATABLE-READ开启的情况下，无论其他事务是否提交了新的变更，同一条件的查询返回结果都是一致的
也就是即使数据更新了，原来会话的事务中，相同的查询条件前后查看的结果一样

  1. B会话更新记录但不提交
mysql B>start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql B>update t1 set name='yzw1' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql B>select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yzw11|
|    2 | yzw2 |
+------+------+
2 rows in set (0.00 sec)

此时B会话已经看到数据更改

  1. A会话设置事务隔离级别为REPEATABLE-READ
mysql A>set tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql A>SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql A>start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql A>select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yzw1 |
|    2 | yzw2 |
+------+------+
2 rows in set (0.00 sec)

此时A会话无法读到B会话开启的未提交的事务

  1. B会话执行commit;
mysql B>commit;
Query OK, 0 rows affected (0.02 sec)

mysql B>select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | yzw11 |
|    2 | yzw2  |
+------+-------+
2 rows in set (0.00 sec)
  1. A会话再查看数据
mysql A>select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yzw1 |
|    2 | yzw2 |
+------+------+
2 rows in set (0.00 sec)

B会话提交后，完成一个事务，但是在A会话的同一个事务中，还是无法读取到B会话做的提交

  1. A会话此时提交事务
mysql A>commit;
Query OK, 0 rows affected (0.00 sec)

mysql A>select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | yzw11 |
|    2 | yzw2  |
+------+-------+
2 rows in set (0.00 sec)

A会话提交后，可以读取到B会话做的提交

串行化 SERIALIZABLE

与REPEATABLE-READ相似，只会读取其他事务已经提交的内容
不同的是，如果autocommit为false，select会隐式转化为select ... lock in share mode

  1. B会话更新记录但不提交
mysql B>start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql B>update t1 set name='yzw111' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql B>select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | yzw111 |
|    2 | yzw2   |
+------+--------+
2 rows in set (0.00 sec)

此时B会话已经看到数据更改

  1. A会话设置事务隔离级别为SERIALIZABLE，并且修改autocommit=0，再查看被修改的数据
mysql A>set tx_isolation='SERIALIZABLE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql A>SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql A>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql A>select * from t1;

此时A会话被阻塞，因为隐式加了lock in share mode

  1. B会话执行commit;
mysql B>commit;
Query OK, 0 rows affected (0.02 sec)

mysql B>select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | yzw111 |
|    2 | yzw2   |
+------+--------+

2 rows in set (0.00 sec)
  1. A会话再查看数据
mysql A>select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | yzw111 |
|    2 | yzw2   |
+------+--------+

2 rows in set (0.00 sec)
4种隔离级别能解决的并发问题
隔离级别 dirty read（脏读） unrepeatable read（不可重复读） phantom read（幻读）
read uncommitted Y Y Y
read committed X Y Y
reapeatable read X X X
serializable X X X

3.与MySQL锁相关的有哪些因素？

锁 用于多个事务访问同一个对象时根据这些操作访问同一对象的先后次序给事务排序

  • redo和undo解决原子性A
  • undo解决一致性C
  • lock解决隔离性I
  • redo解决次就行D
锁的4种模式
  1. IS-意向共享锁 事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁，给表加锁
  2. IX-意向排它锁 事务打算给数据行加行排它锁，事务在给一个数据行加排它锁前必须先取得该表的IX锁，给表加锁
  3. S-共享锁 允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁，行锁，阻止排它锁
  4. X-排它锁 允许获得排它锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他写锁
锁兼容性列表
兼容性 IS S IX X
IS 兼容 兼容 兼容 冲突
S 兼容 兼容 冲突 冲突
IX 兼容 冲突 兼容 冲突
X 冲突 冲突 冲突 冲突
行锁的3种范围
  1. record lock-行记录锁
  2. gap lock-间隙锁 针对普通索引（二级索引）
  3. next-key lock-下一键锁，索引记录锁以及索引记录之间的间隙锁，二者的组合锁
不同数据库锁的实现
DB lock type
innodb 行级锁
oracle 行级锁
myisam 表锁
MSSQL 行级锁、锁升级
lock与latch的区别
type lock latch
对象 事务 线程
保护 数据库对象 内存结构对象
持续时间 长-毫秒级 短-微秒级
模式 表锁行锁 互斥（0/1）
死锁 有 无

显式加锁

  • S select * from tabname where ... lock in share mode;
  • X select * from tabname where ... for update;

意向锁是innodb引擎自己增加，不需要干预
update/delete/insert自动加X锁，普通select不加任何锁

共享锁例子
session A session B
myslq A>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

myslq A>select * from t1 where id=1;
+------+-------+-------+
| id | name1 | name2 |
+------+-------+-------+
| 1  | yzw1    | yzw11   |
+------+-------+-------+
1 row in set (0.00 sec)		 mysql B>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

myslq A>select * from t1 where id=1;
+------+-------+-------+
| id | name1 | name2 |
+------+-------+-------+
| 1  | yzw1    | yzw11   |
+------+-------+-------+
1 row in set (0.00 sec)
session A给ID=1的记录增加S锁：
myslq A>select * from t1 where id=1 lock in share mode;
+------+-------+-------+
| id | name1 | name2 |
+------+-------+-------+
| 1  | yzw1    | yzw11   |
+------+-------+-------+
1 row in set (0.00 sec)		  
  session B进行查询并加S锁：
myslq A>select * from t1 where id=1 lock in share mode;
+------+-------+-------+
| id | name1 | name2 |
+------+-------+-------+
| 1  | yzw1    | yzw11   |
+------+-------+-------+
1 row in set (0.00 sec)
S锁兼容，并不会互相阻塞
session A更新记录，此时给表加IX锁，并给行加X锁：
myslq A>update t1 set name1='YZW1' where id=1;
因为session B此时持有S锁，session A无法加上X锁，因此进入等待X锁		  
  session B也对同样的行进行更新操作：
mysql B>update t1 set name2='YZW11' where id=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
检测到死锁退出
session A获得X锁后，更新成功：
Query OK, 1 row affected (1.39 sec)
Rows matched: 1 Changed: 1 Warnings: 0		  
排它锁例子
session A session B
mysql A>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql A>select * from t1 where id=2;
+------+------+
| id     | name  |
+------+------+
|   2    | yzw2   |
+------+------+
1 row in set (0.00 sec)		 mysql A>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql A>select * from t1 where id=2;
+------+------+
| id     | name  |
+------+------+
|   2    | yzw2   |
+------+------+
1 row in set (0.00 sec)
session A加X锁：
mysql A>select * from t1 where id=2 for update;
+------+------+
| id     | name  |
+------+------+
|   2    | yzw2   |
+------+------+
1 row in set (0.00 sec)		  
  session B可以进行正常查询：
mysql A>select * from t1 where id=2;
+------+------+
| id     | name  |
+------+------+
|   2    | yzw2   |
+------+------+
1 row in set (0.00 sec)
  加S锁被阻塞，锁超时退出：
mysql B>select * from t1 where id=2 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  加X锁被阻塞，锁超时退出：
mysql B>select * from t1 where id=2 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
对锁定记录进行操作并提交，释放锁：
mysql A>update t1 set name='YZW22' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql A>commit;
Query OK, 0 rows affected (0.01 sec)		  
  session A释放锁后，session B可以正常加S锁或者X锁，且不会被阻塞
行锁分析
1. 创建测试数据
create table t2(id int,name varchar(10),primary key(id),key(name));
insert into t2 values(1,'A'),(3,'A'),(5,'C'),(7,'G'),(10,'I');
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  3 | A    |
|  5 | C    |
|  7 | G    |
| 10 | I    |
+----+------+
5 rows in set (0.00 sec)
  • 这里的GAP lock一共有6个，1,5,10前后各1个
  • record lock一共有6个，1,3,5,7,10
  • next-key lock，比如1，包含1前面的0和1后面的2，也就是2个间隙
2.测试record lock

测试1

  • session A
mysql A>begin;
Query OK, 0 rows affected (0.00 sec)

mysql A>select * from t2 where NAME='C' for update;
+----+------+
| id | name |
+----+------+
|  5 | C    |
+----+------+
1 row in set (0.00 sec)

name是普通/二级索引，且是RR级别隔离，最终在主键索引上会给id=5加上record lock，前后加上gap lock

  • session B
mysql B>begin;
Query OK, 0 rows affected (0.00 sec)

mysql B>select * from t2 where id=5 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session A的X锁和session B的S锁冲突

mysql> select * from information_schema.innodb_locks;
+------------------------+-----------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id                | lock_trx_id     | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+------------------------+-----------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 421469287688928:45:3:4 | 421469287688928 | S         | RECORD    | `db1`.`t2` | PRIMARY    |         45 |         3 |        4 | 5         |
| 19207:45:3:4           | 19207           | X         | RECORD    | `db1`.`t2` | PRIMARY    |         45 |         3 |        4 | 5         |
+------------------------+-----------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

测试2

  • session A
mysql A>begin;
Query OK, 0 rows affected (0.00 sec)

mysql A>select * from t2 where id=5 and name='C' for update;
+----+------+
| id | name |
+----+------+
|  5 | C    |
+----+------+
1 row in set (0.00 sec)

记录存在，加X锁，record lock 和前后 gap lock

  • session B
mysql B>begin;
Query OK, 0 rows affected (0.00 sec)

mysql B>select * from t2 where id=5 and name='B' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

记录不存在，位置在C的前面，C前后已经被加了gap lock，因此被阻塞

mysql> select * from information_schema.innodb_locks;
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id      | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 19210:45:3:4 | 19210       | X         | RECORD    | `db1`.`t2` | PRIMARY    |         45 |         3 |        4 | 5         |
| 19209:45:3:4 | 19209       | X         | RECORD    | `db1`.`t2` | PRIMARY    |         45 |         3 |        4 | 5         |
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)
3.测试gap lock

测试1

  • session A
mysql A>begin;
Query OK, 0 rows affected (0.00 sec)

mysql A>select * from t2 where name='C' for update;
+----+------+
| id | name |
+----+------+
|  5 | C    |
+----+------+
1 row in set (4.18 sec)

给ID=5的行加X锁，同时给4/6加间隙锁

  • session B
mysql B>insert into t2 values(4,'C');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql B>insert into t2 values(6,'C');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

5前后已经被加了间隙锁，插入重复的值(4，C)在id=5的前面，(6，C)在id=5的后面，插入被阻塞

mysql> mysql> select * from information_schema.innodb_locks;
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id      | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 19219:45:4:4 | 19219       | X,GAP     | RECORD    | `db1`.`t2` | name       |         45 |         4 |        4 | 'C', 5    |
| 19218:45:4:4 | 19218       | X         | RECORD    | `db1`.`t2` | name       |         45 |         4 |        4 | 'C', 5    |
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

mysql> select * from information_schema.innodb_locks;
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id      | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 19219:45:4:5 | 19219       | X,GAP     | RECORD    | `db1`.`t2` | name       |         45 |         4 |        5 | 'G', 7    |
| 19218:45:4:5 | 19218       | X,GAP     | RECORD    | `db1`.`t2` | name       |         45 |         4 |        5 | 'G', 7    |
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

查看谁阻塞谁

mysql> SELECT
    ->   r.trx_id waiting_trx_id,
    ->   r.trx_mysql_thread_id waiting_thread,
    ->   r.trx_query waiting_query,
    ->   b.trx_id blocking_trx_id,
    ->   b.trx_mysql_thread_id blocking_thread,
    ->   b.trx_query blocking_query
    -> FROM       information_schema.innodb_lock_waits w
    -> INNER JOIN information_schema.innodb_trx b
    ->   ON b.trx_id = w.blocking_trx_id
    -> INNER JOIN information_schema.innodb_trx r
    ->   ON r.trx_id = w.requesting_trx_id;
+----------------+----------------+------------------------------+-----------------+-----------------+----------------+
| waiting_trx_id | waiting_thread | waiting_query                | blocking_trx_id | blocking_thread | blocking_query |
+----------------+----------------+------------------------------+-----------------+-----------------+----------------+
| 19219          |              2 | insert into t2 values(6,'C') | 19218           |               3 | NULL           |
+----------------+----------------+------------------------------+-----------------+-----------------+----------------+
1 row in set, 1 warning (0.00 sec)

测试2

  • session A
mysql A>begin;
Query OK, 0 rows affected (0.00 sec)

mysql A>select * from t2 where name='C' for update;
+----+------+
| id | name |
+----+------+
|  5 | C    |
+----+------+
1 row in set (0.00 sec)

主键5加X锁，主键4/6、二级索引B/D/E/F加间隙锁

  • session B
mysql B>begin;
Query OK, 0 rows affected (0.00 sec)

mysql B>insert into t2 values(2,'B');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql B>insert into t2 values(8,'F');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql B>insert into t2 values(6,'G');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql B>insert into t2 values(2,'A');
Query OK, 1 row affected (0.00 sec)

mysql B>insert into t2 values(8,'D');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql B>insert into t2 values(8,'E');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql B>insert into t2 values(8,'F');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql B>insert into t2 values(11,'D');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

RR(REPEATABLE-READ)模式才会出现这种情况，避免出现幻读

RR模式下有哪些锁测试例子
  1. 创建测试数据
create table t4( \
id int(11) not null default '0', \
user_id int(11) default null, \
user_name varchar(10) default null, \
create_time varchar(10) default null, \
address varchar(10) default null, \
primary key(id), \
key idx_time_name (create_time,user_name) );

insert into t4 values(1,101,'tom','20161010',''), \
(4,104,'joe','20160305',''), \
(6,106,'tom','20161231',''), \
(8,108,'tom','20160515','sh'), \
(10,110,'tom','20160101',''), \
(100,200,'jack','20161120','');

mysql A>select * from t4;
+-----+---------+-----------+-------------+---------+
| id  | user_id | user_name | create_time | address |
+-----+---------+-----------+-------------+---------+
|   1 |     101 | tom       | 20161010    |         |
|   4 |     104 | joe       | 20160305    |         |
|   6 |     106 | tom       | 20161231    |         |
|   8 |     108 | tom       | 20160515    | sh      |
|  10 |     110 | tom       | 20160101    |         |
| 100 |     200 | jack      | 20161120    |         |
+-----+---------+-----------+-------------+---------+
6 rows in set (0.00 sec)

id为主键，create_time和user_name为组合索引

select * from t4 \
where create_time > '20160101' \
and create_time < '20161120' \
and user_name='tom' \
and address != '' for update;

mysql A>select * from t4 \
    -> where create_time > '20160101' \
    -> and create_time < '20161120' \
    -> and user_name='tom' \
    -> and address != '' for update;
+----+---------+-----------+-------------+---------+
| id | user_id | user_name | create_time | address |
+----+---------+-----------+-------------+---------+
|  8 |     108 | tom       | 20160515    | sh      |
+----+---------+-----------+-------------+---------+
1 row in set (0.00 sec)

最终符合条件的只有1条数据

借用一张图分析
image

  • 这里索引走的是普通组合索引，因此根据create_time首先定位到3条数据，根据user_name定位到最终1条数据

mysql是聚簇索引，数据存放在主键索引的叶子节点上，二级索引只保存索引结构，最终定位回主键索引

  • 在idx_time_name索引上根据create_time升序排序，给3条数据加了X锁，总共4个gap lock
  • 从二级索引定位到主键索引上的1/4/8三条数据加了X锁，主键索引上不考虑gap lock
MDL锁

metadata lock 用于解决或者保证DDL操作与DML操作之间一致性，在Server级实现

  • session A
mysql A>begin;
Query OK, 0 rows affected (0.00 sec)

mysql A>select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | yzw111 |
|    2 | YZW22  |
+------+--------+
2 rows in set (0.00 sec)
  • session B
mysql B>begin;
Query OK, 0 rows affected (0.00 sec)

mysql B>drop table t1;
  • session C
mysql C>select * from t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

此时B/C会话阻塞，因为A持有MDL锁，锁模式为shared_read,B持有X锁
一旦Acommit，B成功drop掉表，C报表不存在

解决meta data lock
  • 减少线上DDL操作
  • 线上DB不要随便做alter table；
  • kill掉DDL会话
mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info             |
+----+------+-----------+------+---------+------+---------------------------------+------------------+
|  4 | root | localhost | db1  | Query   |   16 | Waiting for table metadata lock | drop table t1    |
|  7 | root | localhost | db1  | Sleep   |   22 |                                 | NULL             |
|  8 | root | localhost | db1  | Query   |   12 | Waiting for table metadata lock | select * from t1 |
|  9 | root | localhost | NULL | Query   |    0 | starting                        | show processlist |
+----+------+-----------+------+---------+------+---------------------------------+------------------+
4 rows in set (0.00 sec)

mysql> kill 7;
Query OK, 0 rows affected (0.00 sec)
死锁
  • 产生回路 两个或者两个以上的事务执行过程中，分别持有对方需要的锁
  • 加锁顺序不一致 两个或者两个以上事务同一时刻并发执行，因争夺资源而造成的一种互相等待
回路
  • 创建数据
mysql A>create table t1( id int(11) not null default '0', \
    -> name varchar(10) default null, \
    -> primary key (id));
Query OK, 0 rows affected (0.03 sec)

mysql A>insert into t1 values (1,'AAAA'),(2,'BBBB');
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql A>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | AAAA |
|  2 | BBBB |
+----+------+
2 rows in set (0.00 sec)
session A session B
mysql A>begin;
Query OK, 0 rows affected (0.00 sec)

mysql A>update t1 set name='aaaa' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
持有一个X锁		  
  mysql B>begin;
Query OK, 0 rows affected (0.00 sec)

mysql B>update t1 set name='bbbb' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
也持有一个X锁
mysql A>update t1 set name='CCCC' where id=2;
Query OK, 1 row affected (0.77 sec)
Rows matched: 1 Changed: 1 Warnings: 0
再获得一个X锁		  
  mysql B>update t1 set name='DDDD' where id=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
产生死锁
并发

借用一张图
image

  • 第一个session根据二级索引name查询加X锁，首先给id=1的记录加锁
  • 第二个session根据二级索引age查询加X锁，首先给id=6的记录加锁

此时两个session准备给各自第二条记录加X锁时，发现已经有X锁了，各自进入阻塞状态

减少死锁的方法
  1. 数据库自动检测死锁，优先回滚小事务
  2. innodb_lock_wait_timeout = 3
  3. 尽快提交事务，事务粒度越小越不容易发生死锁
  4. 降低隔离级别，使用RC提高并发降低死锁概率
  5. 多行记录、跨表时，保持事务操作顺序
  6. 优化索引、SQL，减少扫描/锁范围，降低概率
查找锁
  1. show engine innodb status\G;

只能查看最后一个死锁的信息

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-02-24 01:28:01 0x7f91647b7700
*** (1) TRANSACTION:
TRANSACTION 18793, ACTIVE 38 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 140262433064704, query id 257 localhost root updating
update t1 set name='CCCC' where id=2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 48 page no 3 n bits 72 index PRIMARY of table `db1`.`t1` trx id 18793 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000000496a; asc     Ij;;
 2: len 7; hex 4f000001a602fb; asc O      ;;
 3: len 4; hex 62626262; asc bbbb;;

*** (2) TRANSACTION:
TRANSACTION 18794, ACTIVE 37 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140262432798464, query id 258 localhost root updating
update t1 set name='DDDD' where id=1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 48 page no 3 n bits 72 index PRIMARY of table `db1`.`t1` trx id 18794 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000000496a; asc     Ij;;
 2: len 7; hex 4f000001a602fb; asc O      ;;
 3: len 4; hex 62626262; asc bbbb;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 48 page no 3 n bits 72 index PRIMARY of table `db1`.`t1` trx id 18794 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000004969; asc     Ii;;
 2: len 7; hex 4e00000166035b; asc N   f [;;
 3: len 4; hex 61616161; asc aaaa;;
  1. 将所有死锁信息打印到日志innodb_print_all_deadlocks
  2. 查看锁

https://dev.mysql.com/doc/refman/5.6/en/innodb-information-schema-examples.html

sql查询谁阻塞谁

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;

查看被阻塞的SQL信息

mysql> select trx_id,trx_state,trx_started,trx_wait_started,trx_mysql_thread_id,trx_query from information_schema.innodb_trx;
+--------+-----------+---------------------+---------------------+---------------------+------------------------------+
| trx_id | trx_state | trx_started         | trx_wait_started    | trx_mysql_thread_id | trx_query                    |
+--------+-----------+---------------------+---------------------+---------------------+------------------------------+
| 19219  | LOCK WAIT | 2018-02-25 10:57:44 | 2018-02-25 11:31:39 |                   2 | insert into t2 values(6,'C') |
| 19218  | RUNNING   | 2018-02-25 10:57:35 | NULL                |                   3 | NULL                         |
+--------+-----------+---------------------+---------------------+---------------------+------------------------------+
2 rows in set (0.00 sec)
  • trx_id innodb存储引擎内部唯一的事务ID
  • trx_state 当前事务的状态
  • trx_started 当前事务的开始时间
  • trx_wait_started 事务等待开始的时间
  • trx_mysql_thread_id mysql的线程ID，也就是shop processlist的显示结果
  • trx_query 事务运行的sql语句

查看获取锁类型

mysql> select * from information_schema.innodb_locks;
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id      | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 19219:45:4:5 | 19219       | X,GAP     | RECORD    | `db1`.`t2` | name       |         45 |         4 |        5 | 'G', 7    |
| 19218:45:4:5 | 19218       | X,GAP     | RECORD    | `db1`.`t2` | name       |         45 |         4 |        5 | 'G', 7    |
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)
  • lock_id 锁的id
  • lock_trx_id 事务id
  • lock_mode 锁的模式
  • lock_type 锁的类型，是表锁还是行锁
  • lock_table 要加锁的表
  • lock_index 锁的索引
  • lock_space innodb存储引擎表空间的ID号
  • lock_page 被锁住的页的数量，如果是表锁，该值是NULL
  • lock_rec 被锁住的行的数量，如果是表锁，该值是NULL
  • lock_data 被锁住的行的主键值，如果是表锁，该值是NULL

查看申请资源的ID号

mysql> desc information_schema.innodb_lock_waits;
+-------------------+-------------+------+-----+---------+-------+
| Field             | Type        | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | NO   |     |         |       |
| requested_lock_id | varchar(81) | NO   |     |         |       |
| blocking_trx_id   | varchar(18) | NO   |     |         |       |
| blocking_lock_id  | varchar(81) | NO   |     |         |       |
+-------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from  information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 19219             | 19219:45:4:5      | 19218           | 19218:45:4:5     |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)
  • requesting_trx_id 申请锁资源的事务ID
  • requesting_lock_id 申请的锁的ID
  • blocking_trx_id 被阻塞的事务ID
  • blocking_lock_id 被阻塞的锁的ID
posted @ 2018-02-24 23:52  Jenvid  阅读(216)  评论(0)  编辑  收藏  举报