016、锁实验
MySQL锁实验
MySQL的锁是基于索引的。
锁定算法，MySQL的innodb存储引擎有3种锁的算法：
- Record lock：单个行记录上的锁
- Gap lock：间隙锁，锁定一个范围，但不包括记录本身
- Next-key lock：Gap lock + record lock锁定一个范围，并且锁定记录本身。Next-key lock是为了解决幻读，这是innodb默认的锁算法。
在innodb中，基于主键的行锁只有record lock的，没有gap lock，因为主键可以锁定唯一记录。
在innodb中，基于非唯一索引的辅助索引的锁，除了record lock，还有gap lock，也就是next-key lock。
行锁升级为表锁的情况：在innodb中，如果会话是基于没有索引列的条件修改数据(where条件中的列没有加索引)时，在RR隔离级别下，有可能会导致所有的行记录被锁住，升级为表锁。
阻塞：因为不同锁之间的兼容性关系，在某些时刻，一个事务中的锁需要等待另一个事务中的锁释放它占用的资源。在innodb存储引擎源码中，用mutex数据结构来实现锁。在访问资源前需要用mutex_enter函数进行申请，在访问或修改完毕后立即执行mutex_exit函数。当一个资源被一个事务占有时，另一个事务执行mutex_enter函数就会发生等待，这既是阻塞。
事务阻塞等待时间：innodb存储引擎使用innodb_lock_wait_timeout来控制等待时间，默认50s。
mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.00 sec)实验一：Record lock
基于主键的DML操作只会锁单个记录，实验过程如下，创建实验表：
mysql> create table t(
-> a int(11) not null,
-> b varchar(20) default null,
-> c varchar(10) default null,
-> primary key(a),
-> key b(b)
-> );
Query OK, 0 rows affected (0.33 sec)
--该表中主键没有满足主键三个特性：非空，自增，唯一插入测试数据：
mysql> insert into t values(1,'aa','bb');
Query OK, 1 row affected (0.32 sec)
mysql> insert into t values(2,'cc','dd');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t values(3,'ee','ff');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | aa | bb |
| 2 | cc | dd |
| 3 | ee | ff |
+---+------+------+
3 rows in set (0.00 sec)开启两个事务，会话1和会话2，会话1更新数据，先不提交，会话2更新同一行数据：
mysql> begin;--会话1开启事务
Query OK, 0 rows affected (0.00 sec)
mysql> begin;--会话2开启事务
Query OK, 0 rows affected (0.00 sec)
mysql> update t set b='aaa' where a=1; --会话1更新数据
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set b='aaaa' where a=1; --会话2更新数据，发现一直在等待，等到默认超时时间时，结束
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
--会话2查询数据，并没有更新
mysql> select * from t;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | aa | bb |
| 2 | cc | dd |
| 3 | ee | ff |
+---+------+------+
3 rows in set (0.00 sec)会话2更新不同于会话1的行，并不会有锁等待：
mysql> update t set b='ccc' where a=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0说明基于主键的DML操作锁定单个记录。
这就是第一种锁算法，Record lock：单个行记录上的锁。
实验二：行锁升级为表锁
行锁升级为表锁的情况：在innodb中，如果会话是基于没有索引列的条件修改数据时，在RR隔离级别下，有可能会导致所有的行记录被锁住，升级为表锁。
创建测试表，插入测试数据：
mysql> create table t(id int);
Query OK, 0 rows affected (0.21 sec)
mysql> insert into t values(1),(2),(3),(4);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)会话1开启事务，并进行更新操作，先不提交：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t set id=111 where id=1;
Query OK, 1 row affected (0.31 sec)
Rows matched: 1 Changed: 1 Warnings: 0会话2开启事务，更新不同行操作：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t set id=222 where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction会话3查看锁堵塞(在会话2等待超时之前查看)：
mysql> use information_schema;
Database changed
mysql> select * from INNODB_LOCKS;
+---------------+-------------+-----------+-----------+------------+-----------------+------------+-----------+----------+----------------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+------------+-----------------+------------+-----------+----------+----------------+
| 275745:87:3:2 | 275745 | X | RECORD | `test`.`t` | GEN_CLUST_INDEX | 87 | 3 | 2 | 0x000000000B00 |
| 275744:87:3:2 | 275744 | X | RECORD | `test`.`t` | GEN_CLUST_INDEX | 87 | 3 | 2 | 0x000000000B00 |
+---------------+-------------+-----------+-----------+------------+-----------------+------------+-----------+----------+----------------+
2 rows in set (0.00 sec)
mysql> select * from INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 275745 | 275745:87:3:2 | 275744 | 275744:87:3:2 |
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.00 sec)以上操作发现，更新不同行的数据也会有锁等待现象。
结论：mysql innodb的锁是加在索引身上的，如果该表没有主键，索引的情况。innodb会由行锁升级为表锁(会锁定所有行的记录)。
实验三：Gap lock和next-key lock
创建实验表，并插入数据：
mysql> create table t(
-> a int,
-> primary key(a)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t values(1),(2),(3),(4),(7),(8);
Query OK, 6 rows affected (0.06 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from t;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 7 |
| 8 |
+---+
6 rows in set (0.00 sec)会话1开启事务，并以共享锁的形式查询：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where a<6 lock in share mode;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.00 sec)会话2开启事务，向表中插入数据，该数据满足会话1的查询条件：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t select 5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t select 6;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction会话3查询锁等待(在会话2等待超时之前查看)：
mysql> select * from INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 275773 | 275773:88:3:6 | 275771 | 275771:88:3:6 |
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.00 sec)
mysql> select * from INNODB_LOCKS;
+---------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 275773:88:3:6 | 275773 | X,GAP | RECORD | `test`.`t` | PRIMARY | 88 | 3 | 6 | 7 |
| 275771:88:3:6 | 275771 | S | RECORD | `test`.`t` | PRIMARY | 88 | 3 | 6 | 7 |
+---------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
2 rows in set (0.00 sec)这种情况下，不论插入的记录是5还是6，都会被锁定，因为在next-key lock算法下，锁定的是小于6这个数值区间的所有数值。但是插入数值9是可以的，因为该记录不在锁定的范围内。
mysql> insert into t select 9;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0而对于单个值的索引查询，不需要用到Gap lock，只需要加一个record lock即可，因此innodb存储引擎会自己选一个最小的算法模型。
以上步骤验证了间隙锁的特性：
GAP间隙锁：锁定范围，不包含记录的本身。
next-key lock：既锁定范围，又锁定记录的本身。 lock in share mode 避免出现幻读现象。
实验四：死锁
如果程序是串行的，那么不可能发生死锁，死锁只发生与并发情况，数据库就是一个并发进行着的程序，因此可能发生死锁。
innodb存储引擎有一个后台的锁监控线程，该线程负责查看可能的死锁问题，并自动告知用户。
死锁产生的原因：会话A和会话B的资源互相在等待。
大多数的死锁，innodb存储引擎自己可以侦测到，不需要人为进行干预。
发生死锁后，会话B事务抛出死锁异常，并且自动回滚，这时会话A马上得到之前会话B锁定的资源。
演示死锁的产生，实验过程如下，创建实验表：
mysql> create table t(
-> id int not null auto_increment,
-> username varchar(20) default null,
-> primary key(id),
-> key username(username)
-> );
Query OK, 0 rows affected (0.06 sec)插入测试数据：
mysql> insert into t values(2,'aa');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t values(4,'aa');
Query OK, 1 row affected (0.03 sec)
mysql> select * from t;
+----+----------+
| id | username |
+----+----------+
| 2 | aa |
| 4 | aa |
+----+----------+
2 rows in set (0.00 sec)会话1开启事务，执行更新操作，更新id=2的数据，先不提交：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t set username='aaa' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0会话2开启事务，执行更新操作，id=4的数据，先不提交：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t set username='aaa' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0到目前为止，两个会话锁定的资源为不同的行，没有冲突。
会话1更新数据，更新id=4的数据(id=4的数据当前被会话2锁定)：
mysql> update t set username='bb' where id=4;
--一直在等待会话2更新数据，更新id=2的数据(id=2的数据当前被会话1锁定)：
mysql> update t set username='ccc' where id=2;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction发现死锁，会话2自动回滚，会话1执行更新操作：
mysql> update t set username='bb' where id=4;
Query OK, 1 row affected (10.93 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t;
+----+----------+
| id | username |
+----+----------+
| 2 | aaa |
| 4 | bb |
+----+----------+
2 rows in set (0.00 sec)结论：出现死锁，数据库自动回滚。不会等待。
死锁出现的原因：两个会话互相请求(交叉请求)对方已锁定的资源，进行DML操作时发生。
监控死锁
查看innodb存储引擎状态可以查看到死锁的信息，在操作系统层面结合grep过滤：
[root@localhost ~]# mysql -uroot -proot -e "show engine innodb status \G;"|grep DEAD
Warning: Using a password on the command line interface can be insecure.
LATEST DETECTED DEADLOCK死锁建议放在监控中，实时查看。
如何提高insert插入的效率
假如现在有一个sql脚本，里面都是insert语句，数据量有1000万行，如何快速插入到数据库中。
1、在数据库中调用脚本，而不是把脚本内容复制粘贴执行：
mysql> source /u01/scripts/1.sql2、由于MySQL数据库是自动提交的，可以在脚本开头加begin;在脚本结尾加commit，将这1000万数据放在一个事务中，这样就避免了事务的频繁提交。
浙公网安备 33010602011771号