第19章：MySQL之锁
第19章：MySQL之锁
目录
修订日期：2021-01-08
一. 锁
1.1. 锁的介绍
-
什么是锁
- 对共享资源进行并发访问
- 提供数据的完整性和一致性
-
每个数据库的锁的实现完全不同
- MyISAM
表锁 - InnoDB
行锁（与Oracle的行锁不同） - MSSQL
行级锁with 锁升级
- MyISAM
-
latch
- mutex
- rw-lock
-
锁的区别
-
-
latch是针对程序内部的资源（比如：全局变量）的锁的定义，而这里的lock针对的是数据库的事物 -
lock由latch来保证和实现
-
1.2. latch锁的查看
-- 主要给内核开发人员给予帮助
mysql root@localhost:mytest> show engine innodb mutex;
+--------+-----------------------------+-----------+
| Type | Name | Status |
+--------+-----------------------------+-----------+
| InnoDB | rwlock: fil0fil.cc:1381 | waits=1 |
| InnoDB | rwlock: fil0fil.cc:1381 | waits=2 |
| InnoDB | rwlock: dict0dict.cc:2687 | waits=12 |
| InnoDB | rwlock: dict0dict.cc:1184 | waits=166 |
| InnoDB | rwlock: fil0fil.cc:1381 | waits=5 |
| InnoDB | rwlock: log0log.cc:838 | waits=905 |
| InnoDB | rwlock: btr0sea.cc:195 | waits=1 |
| InnoDB | sum rwlock: buf0buf.cc:1460 | waits=825 |
+--------+-----------------------------+-----------+
8 rows in set
Time: 0.056s
mysql root@localhost:mytest>
1.3. 锁的类型
- S 行级共享锁
- X 行级排它锁锁
- IS
- IX
- AI 自增锁
1.4. 意向锁介绍
- 揭示下一层级请求的锁的类型
- IS：事物想要获得一张表中某几行的共享锁
- IX：事物想要获得一张表中某几行的排他锁
- InnoDB存储引擎中意向锁都是
表锁
-
假如此时有
事物tx1需要在记录A上进行加X锁：- 在该记录所在的
数据库上加一把意向锁IX - 在该记录所在的
表上加一把意向锁IX - 在该记录所在的
页上加一把意向锁IX - 最后在该
记录A上加上一把X锁
- 在该记录所在的
-
假如此时有
事物tx2需要对记录B（假设和记录A在同一个页中）加S锁：- 在该记录所在的
数据库上加一把意向锁IS - 在该记录所在的
表上加一把意向锁IS - 在该记录所在的
页上加一把意向锁IS - 最后在该
记录B上加上一把S锁
- 在该记录所在的
-
加锁是从上往下，一层一层进行加的锁兼容 X IX S IS X 冲突 冲突 冲突 冲突 IX 冲突 兼容冲突 兼容S 冲突 冲突 兼容兼容IS 冲突 兼容兼容兼容
意向锁都是相互兼容的，因为意向锁表示的是下一层在请求什么类型的锁
-
假如此时有
事物tx3需要在记录A上进行加S锁：- 在该记录所在的
数据库上加一把意向锁IS - 在该记录所在的
表上加一把意向锁IS - 在该记录所在的
页上加一把意向锁IS - 发现该记录被锁定
（ tx1的X锁 ），那么tx3需要等待，直到tx1进行commit
- 在该记录所在的
1.5. 意向锁的作用
-
意向锁是为了实现多粒度的锁，表示在数据库中不但能实现行级别的锁，还可以实现页级别的锁、表级别的锁以及库级别的锁。 -
如果没有意向锁，当你去锁一张表的时候，你就需要对表下的所有记录都进行加锁操作，且对其他事物刚刚插入的记录（游标已经扫过的范围）就没法在上面加锁了，此时就没有实现锁表的功能。
上述锁的操作都是在
内存中，不会放在数据库中。且大部分加的都是意向锁，都是兼容的。释放操作则是从记录锁开始从下往上进行释放
InnoDB没有数据库级别的锁，也没有页级别的锁（InnoDB只能在表和记录上加锁），所以InnoDB的 意向锁 只能加在表上，即InnoDB存储引擎中意向锁都是表锁
1.6. 加锁以及查看
1.6.1 加锁测试
- 终端1
mysql root@localhost:mytest> show create table c;
+-------+--------------------------------------+
| Table | Create Table |
+-------+--------------------------------------+
| c | CREATE TABLE `c` ( |
| | `id` int(11) NOT NULL, |
| | `num` int(4) DEFAULT NULL, |
| | PRIMARY KEY (`id`) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------+
1 row in set
Time: 0.009s
mysql root@localhost:mytest> select * from c;
+----+-----+
| id | num |
+----+-----+
| 0 | 10 |
| 2 | 20 |
| 3 | 20 |
+----+-----+
3 rows in set
Time: 0.010s
mysql root@localhost:mytest> begin; -- 开始一个事物
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from c where id=2 for update; -- 加上排他锁
+----+-----+
| id | num |
+----+-----+
| 2 | 20 |
+----+-----+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>
- 终端2
mysql gcdb@localhost:mytest> show variables like 'innodb_status_output_locks';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_status_output_locks | ON | --默认是OFF；配置为ON，输出锁状态
+----------------------------+-------+
1 row in set
Time: 0.012s
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-22 14:31:15 0x7f254c5af700 INNODB MONITOR OUTPUT |
| | | ===================================== |
----------省略其他输出---------
| | | MySQL thread id 294, OS thread handle 139797830776576, query id 1174080 localhost root |
| | | TABLE LOCK table `mytest`.`c` trx id 1887424 lock mode IX | -- 在 `mytest`.`c`表上加上了意向锁IX（TABLE LOCK）
| | | RECORD LOCKS space id 3469 page no 3 n bits 72 index PRIMARY of table `mytest`.`c` trx id 1887424 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 0000001cccb4; asc ;; |
| | | 2: len 7; hex a500000042011d; asc B ;; |
| | | 3: len 4; hex 80000014; asc ;; |
| | | |
| | | -------- |
| | | FILE I/O |
| | | -------- |
----------省略其他输出---------
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
-- Record lock : 表示是锁住的记录
-- heap no 3 PHYSICAL RECORD: n_fields 4: 表示锁住记录的heap no 为3的物理记录，由4个列组成
-- compact format : 表示这条记录存储的格式（Dynamic其实也是compact的格式）
-- info bits : 0 -- 表示这条记录没有被删除; 非0 -- 表示被修改或者被删除（32）
输出上述信息的前提是
innodb_status_output_locks = 1,可在配置文件中设置打开，不会影响运行时的性能,只有在show engine innodb status时才会使用
- 终端1
mysql root@localhost:mytest> commit -- 提交事物, 提交后就释放了锁，同时在终端2上就看不到锁的信息了
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest>
注意如果直接select * from t5 where a=2 for update;终端2是看不到的，因为mysql默认
自动提交事物
1.6.2 INNODB_TRX
- 终端1
mysql root@localhost:mytest> begin; -- 开始一个事物
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from c where id=2 for update; -- 加上排他锁
+----+-----+
| id | num |
+----+-----+
| 2 | 20 |
+----+-----+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>
- 终端2
mysql gcdb@localhost:mytest> use information_schema;
You are now connected to database "information_schema" as user "gcdb"
Time: 0.003s
mysql gcdb@localhost:information_schema> select * from INNODB_TRX\G
***************************[ 1. row ]***************************
trx_id | 1887425
trx_state | RUNNING
trx_started | 2018-01-22 14:47:50
trx_requested_lock_id | <null>
trx_wait_started | <null>
trx_weight | 2
trx_mysql_thread_id | 294
trx_query | <null> -- 事物运行的SQL语句，这里是NULL,因为他是指当前运行的SQL语句,如果运行完了，就看不到了
trx_operation_state | <null>
trx_tables_in_use | 0
trx_tables_locked | 1
trx_lock_structs | 2
trx_lock_memory_bytes | 1136
trx_rows_locked | 1
trx_rows_modified | 0
trx_concurrency_tickets | 0
trx_isolation_level | REPEATABLE READ -- 当前数据库隔离级别是可重复读
trx_unique_checks | 1
trx_foreign_key_checks | 1
trx_last_foreign_key_error | <null>
trx_adaptive_hash_latched | 0
trx_adaptive_hash_timeout | 0
trx_is_read_only | 0
trx_autocommit_non_locking | 0
1 row in set
Time: 0.012s
mysql gcdb@localhost:information_schema>
- INNODB_TRX 表
| Column name | Description |
|---|---|
| trx_id | 事务ID |
| trx_state | 事务状态： |
| trx_started | 事务开始时间； |
| trx_requested_lock_id | 事务等待的锁的ID（如果事务状态不是LOCK WAIT，这个字段是NULL），详细的锁的信息可以连查INNODB_LOCKS表 |
| trx_wait_started | 事务开始等待的时间 |
| trx_weight | 事务的权重，反映了一个事务修改和锁住的行数。当发生死锁回滚的时候，优先选择该值最小的进行回滚 |
| trx_mysql_thread_id | 事务线程ID |
| trx_query | 事务执行的语句 |
| trx_operation_state | 事务当操作的类型,如果有的话 否则为NULL |
| trx_tables_in_use | 事务中有多少个表被使用 |
| trx_tables_locked | 事务拥有多少个锁，当前事物有行锁在上面( 因为那些是行锁,不是表锁,表仍旧可以读取和写入通过多个事务，尽管一些记录被锁定) |
| trx_lock_structs | 事务保留的锁的数量 |
| trx_lock_memory_bytes | 事务锁住的内存大小（B） |
| trx_rows_locked | 事务锁住的行数 |
| trx_rows_modified | 事务更改的行数 |
| trx_concurrency_tickets | 事务并发票数 |
| trx_isolation_level | 事务隔离级别 |
| trx_unique_checks | 是否唯一性检查 |
| trx_foreign_key_checks | 外键检测 是否开启 |
| trx_last_foreign_key_error | 最后的外键错误 |
| trx_adaptive_hash_latched | 自适应散列索引是否被当前事务锁定 |
| trx_adaptive_hash_timeout | 是否立即放弃自适应散列索引的搜索latched，或者保留来自MySQL的调用。 |
1.6.3 INNODB_LOCKS和INNODB_LOCK_WAITS
- 终端2
--下面两个表是要一起对比查看才有意义的，当前没有两个事物（仅终端1中一个事物）进行相互阻塞和等待，所以目前两个表是空的
mysql gcdb@localhost:information_schema> 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 |
+---------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
0 rows in set
Time: 0.010s
mysql gcdb@localhost:information_schema> select * from `INNODB_LOCK_WAITS`;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
0 rows in set
Time: 0.011s
mysql gcdb@localhost:information_schema>
- 终端3
mysql root@localhost:sys> use mytest;
You are now connected to database "mytest" as user "root"
Time: 0.003s
mysql root@localhost:mytest> set innodb_lock_wait_timeout=60; -- 设置锁等待时间为60秒，方便看到锁的信息，线上根据实际情况自行修改
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from c where id=2 lock in share mode; -- 这里如果超时时间设置短，可能来不及看
-- 可通过innodb_lock_wait_timeout进行设置
-- 此时hang住在这里，进行等待，因为终端1中 for update 还没有commit
- 终端2
mysql gcdb@localhost:information_schema> select * from INNODB_LOCKS \G;
***************************[ 1. row ]***************************
lock_id | 421284540508896:3469:3:3 -- 锁ID
lock_trx_id | 421284540508896 --事物ID
lock_mode | S --显示为S锁
lock_type | RECORD
lock_table | `mytest`.`c`
lock_index | PRIMARY
lock_space | 3469
lock_page | 3
lock_rec | 3
lock_data | 2
***************************[ 2. row ]***************************
lock_id | 1887425:3469:3:3
lock_trx_id | 1887425 --事物ID
lock_mode | X --显示为X锁
lock_type | RECORD
lock_table | `mytest`.`c`
lock_index | PRIMARY
lock_space | 3469
lock_page | 3
lock_rec | 3
lock_data | 2
2 rows in set
Time: 0.012s
mysql gcdb@localhost:information_schema> select * from `INNODB_LOCK_WAITS`\G;
***************************[ 1. row ]***************************
requesting_trx_id | 421284540508896 -- 请求的事物ID （S锁），终端3
requested_lock_id | 421284540508896:3469:3:3 -- 由txid，space，page_no，heap_no组成
blocking_trx_id | 1887425 -- 阻塞上面请求的事物ID（X锁），终端1
blocking_lock_id | 1887425:3469:3:3
1 row in set
Time: 0.011s
mysql gcdb@localhost:information_schema>
--mysql5.7查看锁信息
--继续执行 终端1/终端3 内的SQL语句，使其中有一个线程发生阻塞
mysql gcdb@localhost:information_schema> use sys;
You are now connected to database "sys" as user "gcdb"
mysql gcdb@localhost:sys> select * from innodb_lock_waits\G;
***************************[ 1. row ]***************************
wait_started | 2018-01-22 15:50:43 -- 开始的时间
wait_age | 0:00:26 -- 等待的时间
wait_age_secs | 26 -- 等待秒数
locked_table | `mytest`.`c` -- 锁住的表（意向锁）
locked_index | PRIMARY -- 锁住的是系统生成的聚集索引
locked_type | RECORD -- 锁的类型，记录锁
waiting_trx_id | 421284540508896
waiting_trx_started | 2018-01-22 15:50:43
waiting_trx_age | 0:00:26
waiting_trx_rows_locked | 1
waiting_trx_rows_modified | 0
waiting_pid | 302
waiting_query | select * from c where id=2 lock in share mode -- 等待语句
waiting_lock_id | 421284540508896:3469:3:3 -- 事物ID：space：page_No：heap_no
waiting_lock_mode | S -- 等待（请求）的锁的类型
blocking_trx_id | 1887425
blocking_pid | 294
blocking_query | <null>
blocking_lock_id | 1887425:3469:3:3
blocking_lock_mode | X -- 阻塞的锁的类型
blocking_trx_started | 2018-01-22 14:47:50
blocking_trx_age | 1:03:19
blocking_trx_rows_locked | 1
blocking_trx_rows_modified | 0
sql_kill_blocking_query | KILL QUERY 294 -- 解决建议
sql_kill_blocking_connection | KILL 294
1 row in set
Time: 0.014s
mysql gcdb@localhost:sys>
MySQL 5.6 通过导入sys库，也可以支持该视图
锁都是 锁在索引 上的，无论是主键还是二级索引，通过 locked_index 进行查看
当超过 innodb_lock_wait_timeout 设置的阈值，等待（请求）的事物就会报锁超时。在某些业务场景下，锁超时是无法避免的。
-
INNODB_LOCKS表
Column name Description lock_id 锁ID lock_trx_id 事务ID, 可以连INNODB_TRX表查事务详情 lock_mode 锁的模式： S,X,IS,IX,S_GAP,X_GAP,IS_GAP,IX_GAP, orAUTO_INClock_type 锁的类型，行级锁 或者表级锁 lock_table 加锁的表 lock_index 如果是lock_type='RECORD' 行级锁 ,为锁住的索引，如果是表锁为null lock_space 如果是lock_type='RECORD' 行级锁 ,为锁住对象的Tablespace ID，如果是表锁为null lock_page 如果是lock_type='RECORD' 行级锁 ,为锁住页号，如果是表锁为null lock_rec 如果是lock_type='RECORD' 行级锁 ,为锁住页号，如果是表锁为null lock_data 事务锁住的主键值，若是表锁，则该值为null -
INNODB_LOCK_WAITS表
Column name Description requesting_trx_id 申请锁资源的事务ID requesting_lock_id 申请的锁的ID blocking_trx_id 租塞的事务ID blocking_lock_id 租塞的锁的ID
二. 锁与并发
- locking （锁）
- concurrency control （并发控制）
- isolation （隔离级别）
- serializability （序列化）
以上四个其实在数据库中讲的是同一个概念； 锁是用来实现 并发控制 ， 并发控制 用来实现 隔离级别 ， 隔离级别 是通过 锁 来控制的， 锁 的目的为了使得事物之间的执行是 序列化 的
2.1. 事物隔离级别
-
1.READ UNCOMMITED
-
2.READ COMMITTED
- Oracle、DB2、Microsoft SQL Server （默认）
- 解决
脏读（ANSI SQL）
-
3.REPEATABLE READ
- InnoDB（默认）
- 解决
脏读 、不可重复读（ANSI SQL） - InnoDB中的RR解决了
幻读问题（实现了事物的隔离级别）
-
4.SERIALIZABLE
- 解决
脏读 、 不可重复读 和 幻读（ANSI SQL）
- 解决
2.1.1 隔离性
- 什么是隔离性?
一个事物所做的修改，对其他事物是不可见的，好似是串行执行的
隔离
级别越低，事物请求的锁越少或者保持锁的时间就越短。
- 终端1
mysql root@localhost:mytest> create table t_rc (a int, b int, c int);
Query OK, 0 rows affected
Time: 0.009s
mysql root@localhost:mytest>
mysql root@localhost:mytest> begin --开始一个事物
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> insert into t_rc values(1,2,3),(2,3,4); --插入但是未提交
Query OK, 2 rows affected
Time: 0.002s
mysql root@localhost:mytest>
- 终端2
mysql gcdb@localhost:sys> use mytest;
You are now connected to database "mytest" as user "gcdb"
Time: 0.002s
mysql gcdb@localhost:mytest> select * from t_rc; --没有查询到数据
+---+---+---+
| a | b | c |
+---+---+---+
0 rows in set
Time: 0.009s
mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> insert into t_rc values(3,4,5);
Query OK, 1 row affected
Time: 0.001s
mysql gcdb@localhost:mytest> commit; --终端2，事物提交
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> select * from t_rc; --终端2，事物提交后查询到只有一条
+---+---+---+
| a | b | c |
+---+---+---+
| 3 | 4 | 5 |
+---+---+---+
1 row in set
Time: 0.010s
mysql gcdb@localhost:mytest>
- 终端1
--终端1 直接查询可以看到insert数据
mysql root@localhost:mytest> select * from t_rc;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
| 2 | 3 | 4 |
| 3 | 4 | 5 |
+---+---+---+
3 rows in set
Time: 0.010s
mysql root@localhost:mytest> commit; --终端1，提交之后，终端2才能看到
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest>
- 终端2
mysql gcdb@localhost:mytest> select * from t_rc; --终端1未提交，只能查询自己的
+---+---+---+
| a | b | c |
+---+---+---+
| 3 | 4 | 5 |
+---+---+---+
1 row in set
Time: 0.010s
mysql gcdb@localhost:mytest> select * from t_rc; --终端1提交之后，再终端2可以查询到所以数据
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
| 2 | 3 | 4 |
| 3 | 4 | 5 |
+---+---+---+
3 rows in set
Time: 0.010s
mysql gcdb@localhost:mytest>
--符合事物隔离性
mysql gcdb@localhost:mytest> select @@tx_isolation; -- 查看当前事物的隔离级别
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ | --mysql默认隔离级别
+-----------------+
1 row in set
Time: 0.010s
mysql gcdb@localhost:mytest>
注意：线上环境一般使用 READ-COMMITTED
2.1.2 脏读
- 终端1
mysql root@localhost:mytest> create table t_ruc (a int, b int, c int);
Query OK, 0 rows affected
Time: 0.007s
mysql root@localhost:mytest> set tx_isolation="read-uncommitted";
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set
Time: 0.010s
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> insert into t_ruc values(10,20,30),(20,30,40);
Query OK, 2 rows affected
Time: 0.002s
--事物未提交
- 终端2
mysql gcdb@localhost:mytest> set tx_isolation='read-uncommitted';
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> select * from t_ruc;
+----+----+----+
| a | b | c |
+----+----+----+
| 10 | 20 | 30 |
| 20 | 30 | 40 | --终端2里面，事物2能看到事物1中插入的数据，但是事物1还未提交
+----+----+----+
2 rows in set
Time: 0.010s
mysql gcdb@localhost:mytest> insert into t_ruc values(30,40,50); --事物2也插入一行数据
Query OK, 1 row affected
Time: 0.001s
mysql gcdb@localhost:mytest>
- 终端1
mysql root@localhost:mytest> select * from t_ruc;
+----+----+----+
| a | b | c |
+----+----+----+
| 10 | 20 | 30 |
| 20 | 30 | 40 |
| 30 | 40 | 50 | --终端2中的事物2还没有commit，在会话1中的事物1就能读取到数据，这就是脏读
+----+----+----+
3 rows in set
Time: 0.010s
mysql root@localhost:mytest>
2.1.3 不可重复读
- 终端1
mysql root@localhost:mytest> set tx_isolation='read-committed';
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set
Time: 0.010s
mysql root@localhost:mytest> create table t_rco (a int, b int, c int);
Query OK, 0 rows affected
Time: 0.007s
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> insert into t_rco values(100,200,300);
Query OK, 1 row affected
Time: 0.002s
mysql root@localhost:mytest> commit; -- 此时提交事物1，就可以在t_rco表中查询到数据
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from t_rco; --查询到数据
+-----+-----+-----+
| a | b | c |
+-----+-----+-----+
| 100 | 200 | 300 |
+-----+-----+-----+
1 row in set
Time: 0.010s
- 终端2
mysql gcdb@localhost:mytest> set tx_isolation='read-committed';
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set
Time: 0.010s
mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> update t_rco set b=222 and c=111 where a=100;
Query OK, 1 row affected
Time: 0.001s
--终端2的insert事物未提交
- 终端1
mysql root@localhost:mytest> select * from t_rco;
+-----+-----+-----+
| a | b | c |
+-----+-----+-----+
| 100 | 200 | 300 | --终端1,没有查询到之前终端2的insert事物，只有一行数据，同时也说明RC隔离级别解决了脏读问题
+-----+-----+-----+
1 row in set
- 终端2
mysql gcdb@localhost:mytest> rollback; -- 回滚事务
Query OK, 0 rows affected
Time: 0.007s
mysql gcdb@localhost:mytest> begin; --从新开启一个事务
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> update t_rco set b=222, c=111 where a=100; --更新记录
Query OK, 1 row affected
Time: 0.001s
mysql gcdb@localhost:mytest> commit; --提交事物
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest>
- 终端1
mysql root@localhost:mytest> select * from t_rco;
+-----+-----+-----+
| a | b | c |
+-----+-----+-----+
| 100 | 222 | 111 | --查询到终端2的update事务所更新的记录
+-----+-----+-----+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>
2.1.4 幻读
- 终端1
mysql root@localhost:mytest> begin; --开启新事务
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from t_rco;
+-----+-----+-----+
| a | b | c |
+-----+-----+-----+
| 100 | 222 | 111 |
+-----+-----+-----+
1 row in set
Time: 0.010s
--事务未提交
- 终端2
mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> insert into t_rco values(400,400,400);
Query OK, 1 row affected
Time: 0.001s
mysql gcdb@localhost:mytest> commit; -- 终端2中的事物已提交
Query OK, 0 rows affected
Time: 0.001s
- 终端1
mysql root@localhost:mytest> select * from t_rco;
+-----+-----+-----+
| a | b | c |
+-----+-----+-----+
| 100 | 222 | 111 |
| 400 | 400 | 400 | -- 终端1中的事物没提交，就看到了终端2中的事物提交的结果
+-----+-----+-----+
2 rows in set
Time: 0.010s
- 不可重复读：在一个事物中，针对同一条记录，执行两次相同的SQL得到的结果不一样
- 幻读：在一个事物中，执行两次相同的SQL，得到了不同的结果集（新增了部分记录或者缺失了部分记录）（不是同一条记
三. 锁的算法
- Record Lock （行锁）
行锁锁定单条索引记录，锁住的是索引，而非记录本身，即使没有索引定义表, InnoDB创建一个隐藏的聚集索引，并使用此索引进行记录锁定.
- Gap Lock （间隙锁）
间隙锁锁定是索引记录之间的间隙的锁定，也可以是最后一个索引记录之前或之后的间隙的锁定，并不包括该索引记录本身；
对于使用唯一索引锁定行以搜索唯一行的语句，不需要使用间隙锁定。（这不包括搜索条件仅包含多列唯一索引中的某些列的情况;在这种情况下，会发生间隙锁定）
- Next-Key Lock
- 索引记录上的
记录锁和索引记录之前的间隙上的间隙锁的组合，锁定一个范围，并且锁定记录本身
- 索引记录上的
3.1. Record Lock
表t_nums,A列有记录10,20,30,40,50（
A列为主键）
| A |
|---|
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
-
假如t_nums表A列有
记录10,20,30,40,50（只有这三个记录），且在记录30上加锁- 对于在
记录30上加锁，表示该记录上加了行锁 [30, 30]：只锁定记录30本身
- 对于在
-
终端1
mysql root@localhost:mytest> create table t_nums (a int primary key);
Query OK, 0 rows affected
Time: 0.007s
mysql root@localhost:mytest> insert into t_nums values (10),(20),(30),(40),(50);
Query OK, 5 rows affected
Time: 0.002s
mysql root@localhost:mytest> select * from t_nums;
+----+
| a |
+----+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
+----+
5 rows in set
Time: 0.010s
- 终端2
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-23 13:35:45 0x7f255412c700 INNODB MONITOR OUTPUT |
| | | ===================================== |
| | | Per second averages calculated from the last 3 seconds |
----------省略其他输出---------
| | | ------------ |
| | | TRANSACTIONS |
| | | ------------ |
| | | Trx id counter 1887535 |
| | | Purge done for trx's no < 1887534 undo n:o < 0 state: running but idle |
| | | History list length 51 |
| | | LIST OF TRANSACTIONS FOR EACH SESSION: |
| | | ---TRANSACTION 421284540508896, not started |
| | | 0 lock struct(s), heap size 1136, 0 row lock(s) |
| | | ---TRANSACTION 1887534, ACTIVE 4888 sec |
| | | 2 lock struct(s), heap size 1136, 1 row lock(s) |
| | | MySQL thread id 309, OS thread handle 139798173144832, query id 1174341 localhost root |
| | | TABLE LOCK table `mytest`.`t_nums` trx id 1887534 lock mode IX |
| | | RECORD LOCKS space id 3474 page no 3 n bits 72 index PRIMARY of table `mytest`.`t_nums` trx id 1887534 lock_mode X locks rec but not gap |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 |
| | | 0: len 4; hex 8000001e; asc ;; | --hex 8000001e; 1e表示十六进制30
| | | 1: len 6; hex 0000001ccd29; asc );; |
| | | 2: len 7; hex d800000034012a; asc 4 *;; |
3.2. Gap Lock
-
假如t_nums表A列有
记录10,20,30,40,50（只有这三个记录），且在记录30上加锁- 对于在
记录30上加锁，表示在记录10到记录30之间加锁（30的前面个记录10,20） - (10, 30) ：锁定该范围，
不包含两个边界（不能插入10、30等在这个范围内的数据，但是可以对10和30做删除或者修改） - Gap Lock解决了
幻读问题
- 对于在
-
终端1
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from t_nums where a<=30 for update; --记录30前面加锁
+----+
| a |
+----+
| 10 |
| 20 |
| 30 |
+----+
3 rows in set
Time: 0.010s
mysql root@localhost:mytest>
- 终端2
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-23 13:49:20 0x7f255412c700 INNODB MONITOR OUTPUT |
| | | ===================================== |
----------省略其他输出---------
| | | MySQL thread id 309, OS thread handle 139798173144832, query id 1174346 localhost root |
| | | TABLE LOCK table `mytest`.`t_nums` trx id 1887535 lock mode IX |
| | | RECORD LOCKS space id 3474 page no 3 n bits 72 index PRIMARY of table `mytest`.`t_nums` trx id 1887535 lock_mode X locks rec but not gap |
| | | Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 |
| | | 0: len 4; hex 8000000a; asc ;; | --hex 8000000a; a表示十六进制10
| | | 1: len 6; hex 0000001ccd29; asc );; |
| | | 2: len 7; hex d8000000340110; asc 4 ;; |
| | | |
| | | Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 |
| | | 0: len 4; hex 80000014; asc ;; | --hex 80000014; 14表示十六进制20
| | | 1: len 6; hex 0000001ccd29; asc );; |
| | | 2: len 7; hex d800000034011d; asc 4 ;; |
| | | |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 |
| | | 0: len 4; hex 8000001e; asc ;; |
| | | 1: len 6; hex 0000001ccd29; asc );; | --hex 8000001e; 1e表示十六进制30
| | | 2: len 7; hex d800000034012a; asc 4 *;; |
| | | |
| | | -------- |
--锁定了三行记录（10.20.30）
3.3. Next-Key Lock
-
假如t_nums表A列有
记录10,20,30,40,50（只有这三个记录）），且在记录30上加锁- 对于在
记录30上加锁，表示在记录10到记录30范围加锁（Gap Lock）的同时，同时在记录30上也行锁 - (10, 30] ：
锁定该范围，且包含记录30本身（不能插入11、18等在这个范围的数据，同时对记录30不能删除或修改，记录10可以删除或修改）
- 对于在
-
注意：锁住的是索引
-
终端1
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from t_nums where a<=30 for update;
+----+
| a |
+----+
| 10 |
| 20 |
| 30 |
+----+
3 rows in set
Time: 0.010s
mysql root@localhost:mytest>
- 终端2
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+--------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+--------------------------------------------------------------------------------------------------------------------+
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-23 15:33:53 0x7f255412c700 INNODB MONITOR OUTPUT |
| | | ===================================== |
| | | Per second averages calculated from the last 7 seconds |
----------省略其他输出---------
| | | MySQL thread id 317, OS thread handle 139797762930432, query id 1174397 localhost root |
| | | TABLE LOCK table `mytest`.`t_nums` trx id 1887557 lock mode IX |
| | | RECORD LOCKS space id 3474 page no 3 n bits 80 index PRIMARY of table `mytest`.`t_nums` trx id 1887557 lock_mode X | --主键上加X锁， 表示的是Next Key lock
| | | Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 |
| | | 0: len 4; hex 8000000a; asc ;; | --hex 8000000a; a表示十六进制10
| | | 1: len 6; hex 0000001ccd29; asc );; |
| | | 2: len 7; hex d8000000340110; asc 4 ;; |
| | | |
| | | Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 |
| | | 0: len 4; hex 80000014; asc ;; | --hex 80000014; a表示十六进制20
| | | 1: len 6; hex 0000001ccd29; asc );; |
| | | 2: len 7; hex d800000034011d; asc 4 ;; |
| | | |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 |
| | | 0: len 4; hex 8000001e; asc ;; | --hex 8000001e; 1e表示十六进制30
| | | 1: len 6; hex 0000001ccd29; asc );; |
| | | 2: len 7; hex d800000034012a; asc 4 *;; |
| | | |
| | | Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 |
| | | 0: len 4; hex 80000028; asc (;; | --hex 80000028; 28表示十六进制40
| | | 1: len 6; hex 0000001ccd29; asc );; |
| | | 2: len 7; hex d8000000340137; asc 4 7;; |
| | | |
3.4 隔离级别和锁
3.4.1 REPEATABLE-READ级别
- 终端1
mysql root@localhost:mytest> create table t_rc (a int primary key,b int);
Query OK, 0 rows affected
Time: 0.007s
mysql root@localhost:mytest> show create table t_rc;
+-------+--------------------------------------+
| Table | Create Table |
+-------+--------------------------------------+
| t_rc | CREATE TABLE `t_rc` ( |
| | `a` int(11) NOT NULL, |
| | `b` int(11) DEFAULT NULL, |
| | PRIMARY KEY (`a`) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------+
1 row in set
Time: 0.010s
mysql root@localhost:mytest> insert into t_rc(a,b) values(1,10),(2,20),(5,50),(10,100);
Query OK, 4 rows affected
Time: 0.002s
mysql root@localhost:mytest> select * from t_rc;
+----+-----+
| a | b |
+----+-----+
| 1 | 10 |
| 2 | 20 |
| 5 | 50 |
| 10 | 100 |
+----+-----+
4 rows in set
Time: 0.010s
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> delete from t_rc where a<6;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 3 rows affected
Time: 0.001s
mysql root@localhost:mytest>
- 终端2
mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> insert into t_rc values(4,20);
--等待插入
- 终端3
mysql root@localhost:mytest> show engine innodb status;
+--------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-25 08:51:37 0x7f2554069700 INNODB MONITOR OUTPUT |
| | | ===================================== |
----------省略其他输出---------
| | | mysql tables in use 1, locked 1 |
| | | LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) |
| | | MySQL thread id 8335, OS thread handle 139797762664192, query id 1198737 localhost gcdb update |
| | | insert into t_rc values(4,20) | --insert语句
| | | ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED: | --事务锁等待4s
| | | RECORD LOCKS space id 3475 page no 3 n bits 72 index PRIMARY of table `mytest`.`t_rc` trx id 1887637 lock_mode X locks gap before rec insert intention waiting | --插入意向锁（记录锁）
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 |
| | | 0: len 4; hex 80000005; asc ;; |
| | | 1: len 6; hex 0000001ccd90; asc ;; | --这个行锁锁定5
| | | 2: len 7; hex 3c0000003b0460; asc < ; `;; |
| | | 3: len 4; hex 80000032; asc 2;; |
| | | |
| | | ------------------ |
| | | TABLE LOCK table `mytest`.`t_rc` trx id 1887637 lock mode IX |
| | | RECORD LOCKS space id 3475 page no 3 n bits 72 index PRIMARY of table `mytest`.`t_rc` trx id 1887637 lock_mode X locks gap before rec insert intention waiting |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 |
| | | 0: len 4; hex 80000005; asc ;; | --这个行锁锁定5
| | | 1: len 6; hex 0000001ccd90; asc ;; |
| | | 2: len 7; hex 3c0000003b0460; asc < ; `;; |
| | | 3: len 4; hex 80000032; asc 2;; |
| | | |
| | | ---TRANSACTION 1887632, ACTIVE 26 sec |
| | | 2 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 3 |
| | | MySQL thread id 8365, OS thread handle 139798172346112, query id 1198727 localhost root |
| | | TABLE LOCK table `mytest`.`t_rc` trx id 1887632 lock mode IX | --在给主键行上加X锁之前，先要在表上加意向锁IX
| | | RECORD LOCKS space id 3475 page no 3 n bits 72 index PRIMARY of table `mytest`.`t_rc` trx id 1887632 lock_mode X |
| | | Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 |
| | | 0: len 4; hex 80000001; asc ;; | --这个行锁锁定1
| | | 1: len 6; hex 0000001ccd90; asc ;; |
| | | 2: len 7; hex 3c0000003b041a; asc < ; ;; |
| | | 3: len 4; hex 8000000a; asc ;; |
| | | |
| | | Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 |
| | | 0: len 4; hex 80000002; asc ;; | --这个行锁锁定2
| | | 1: len 6; hex 0000001ccd90; asc ;; |
| | | 2: len 7; hex 3c0000003b043d; asc < ; =;; |
| | | 3: len 4; hex 80000014; asc ;; |
| | | |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 |
| | | 0: len 4; hex 80000005; asc ;; | --这个行锁锁定5
| | | 1: len 6; hex 0000001ccd90; asc ;; |
| | | 2: len 7; hex 3c0000003b0460; asc < ; `;; |
| | | 3: len 4; hex 80000032; asc 2;; |
| | | |
| | | Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 |
| | | 0: len 4; hex 8000000a; asc ;; | --这个行锁锁定10
| | | 1: len 6; hex 0000001ccd58; asc X;; |
| | | 2: len 7; hex f6000006c40137; asc 7;; |
| | | 3: len 4; hex 80000064; asc d;; |
| | | |
即在
REPEATABLE-READ的隔离级别下，锁住的是锁住（-∞，1],（1,2],（2,5],（5,10]是因为在REPEATABLE-READ级别下，插入（4,20）将从表（B+树）的第一个记录（1）开始比对，一直比对等于（4）为止。
- 总结:
-
REPEATABLE-READ级别下的锁默认为Next-Key Lock
-
REPEATABLE-READ会对游标打开的所有的记录进行加锁
-
3.4.2 READ-COMMITTED级别
- 终端1
mysql root@localhost:mytest> rollback;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> set tx_isolation="READ-COMMITTED";
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set
Time: 0.010s
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from t_rc where a<6 for update; -- a为是主键，且返回的记录只有一条
+---+----+
| a | b |
+---+----+
| 1 | 10 |
| 2 | 20 |
| 5 | 50 |
+---+----+
3 rows in set
Time: 0.011s
mysql root@localhost:mytest>
- 终端2
mysql gcdb@localhost:mytest> rollback;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> set tx_isolation="READ-COMMITTED";
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set
Time: 0.011s
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+----------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+----------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-25 11:22:22 0x7f2533fbe700 INNODB MONITOR OUTPUT |
| | | ===================================== |
|----------省略其他输出---------
| | | MySQL thread id 8365, OS thread handle 139798172346112, query id 1201460 localhost root |
| | | TABLE LOCK table `mytest`.`t_rc` trx id 1887640 lock mode IX |
| | | RECORD LOCKS space id 3475 page no 3 n bits 72 index PRIMARY of table `mytest`.`t_rc` trx id 1887640 lock_mode X locks rec but not gap | --记录加x锁但是没有gap锁（记录锁）
| | | 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 0000001ccd58; asc X;; |
| | | 2: len 7; hex f6000006c40110; asc ;; |
| | | 3: len 4; hex 8000000a; asc ;; |
| | | |
| | | 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 0000001ccd58; asc X;; |
| | | 2: len 7; hex f6000006c4011d; asc ;; |
| | | 3: len 4; hex 80000014; asc ;; |
| | | |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 |
| | | 0: len 4; hex 80000005; asc ;; |
| | | 1: len 6; hex 0000001ccd58; asc X;; |
| | | 2: len 7; hex f6000006c4012a; asc *;; |
| | | 3: len 4; hex 80000032; asc 2;; |
| | | |
即在
READ-COMMITTED的隔离级别下，锁住的是 1，2，5记录本身（lock_mode X locks rec but not gap)
在大部分情况下，READ-COMMITTED隔离级别下没有GAP锁，但是一些场景下，线上仍可能出现;
设置了READ-COMMITTED隔离级别后， binlog_format = row ，否则会出现主从不一致的情况.
3.4.3 Next-Key lock优化为Record lock
-
REPEATABLE-READ隔离级别
-
终端1
mysql root@localhost:mytest> rollback;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> set tx_isolation="REPEATABLE-READ";
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set
Time: 0.010s
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from t_rc where a=5 for update;
+---+----+
| a | b |
+---+----+
| 5 | 50 |
+---+----+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>
- 终端2
mysql gcdb@localhost:mytest> rollback;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> set tx_isolation="REPEATABLE-READ";
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set
Time: 0.009s
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+----------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+----------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-25 11:32:52 0x7f2533fbe700 INNODB MONITOR OUTPUT |
| | | ===================================== |
|----------省略其他输出---------
| | | MySQL thread id 8365, OS thread handle 139798172346112, query id 1201660 localhost root |
| | | TABLE LOCK table `mytest`.`t_rc` trx id 1887641 lock mode IX |
| | | RECORD LOCKS space id 3475 page no 3 n bits 72 index PRIMARY of table `mytest`.`t_rc` trx id 1887641 lock_mode X locks rec but not gap | ---记录加x锁但是没有gap锁（记录锁）
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 |
| | | 0: len 4; hex 80000005; asc ;; |
| | | 1: len 6; hex 0000001ccd58; asc X;; |
| | | 2: len 7; hex f6000006c4012a; asc *;; |
| | | 3: len 4; hex 80000032; asc 2;; |
| | | |
降级为
Record Lock是因为返回的记录具有唯一性，不会存在幻读问题;
当唯一索引是复合索引时，且查询条件只包含部分列的话，其实还是有Gap lock
3.4.4 非唯一索引的等值查询
3.4.4.1 REPEATABLE-READ隔离级别
- 终端1
mysql root@localhost:mytest> rollback;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> create table t_unidx (a int not null);
Query OK, 0 rows affected
Time: 0.007s
mysql root@localhost:mytest> insert into t_unidx values(1),(2),(5),(10);
Query OK, 4 rows affected
Time: 0.002s
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from t_unidx where a=5 for update; -- 与a为非唯一索引
+---+
| a |
+---+
| 5 |
+---+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>
- 终端2
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+-----------------------------------------------------------------------------------------------------------------------------+
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-25 11:58:08 0x7f2533fbe700 INNODB MONITOR OUTPUT |
| | | ===================================== |
|----------省略其他输出---------
| | | MySQL thread id 8365, OS thread handle 139798172346112, query id 1202131 localhost root |
| | | TABLE LOCK table `mytest`.`t_unidx` trx id 1887655 lock mode IX |
| | | RECORD LOCKS space id 3477 page no 3 n bits 72 index GEN_CLUST_INDEX of table `mytest`.`t_unidx` trx id 1887655 lock_mode X | --通过索引 GEN_CLUST_INDEX加锁
| | | Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 |
| | | 0: len 8; hex 73757072656d756d; asc supremum;; |
| | | |
| | | Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 |
| | | 0: len 6; hex 000001a61b18; asc ;; |
| | | 1: len 6; hex 0000001ccd9e; asc ;; |
| | | 2: len 7; hex a7000000250110; asc % ;; |
| | | 3: len 4; hex 80000001; asc ;; |
| | | |
| | | Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 |
| | | 0: len 6; hex 000001a61b19; asc ;; |
| | | 1: len 6; hex 0000001ccd9e; asc ;; |
| | | 2: len 7; hex a700000025011f; asc % ;; |
| | | 3: len 4; hex 80000002; asc ;; |
| | | |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 |
| | | 0: len 6; hex 000001a61b1a; asc ;; |
| | | 1: len 6; hex 0000001ccd9e; asc ;; |
| | | 2: len 7; hex a700000025012e; asc % .;; |
| | | 3: len 4; hex 80000005; asc ;; |
| | | |
| | | Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 |
| | | 0: len 6; hex 000001a61b1b; asc ;; |
| | | 1: len 6; hex 0000001ccd9e; asc ;; |
| | | 2: len 7; hex a700000025013d; asc % =;; |
| | | 3: len 4; hex 8000000a; asc ;; |
| | | |
t_unidx根据mysql自己生产GEN_CLUST_INDEX，当查询条件为 a=5 时，游标要访问所有的记录（索引），来判断a列是否等于5；即需要通过GEN_CLUST_INDEX扫描所有主键，则会将所有记录（索引）都锁住。形成了表锁的效果。
总结：在
REPEATABLE-READ的隔离级别下，当查询的列没有索引时，会锁住所有记录
3.4.4.2 READ-COMMITTED隔离级别
- 终端1
mysql root@localhost:mytest> set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set
Time: 0.010s
mysql root@localhost:mytest> select * from d;
+----+
| a |
+----+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
+----+
5 rows in set
Time: 0.010s
mysql root@localhost:mytest> show create table d;
+-------+--------------------------------------+
| Table | Create Table |
+-------+--------------------------------------+
| d | CREATE TABLE `d` ( |
| | `a` int(11) DEFAULT NULL |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------+
1 row in set
Time: 0.010s
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from d where a=30 for update;
+----+
| a |
+----+
| 30 |
+----+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>
- 终端2
mysql gcdb@localhost:mytest> set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set
Time: 0.009s
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-25 16:11:56 0x7f254c776700 INNODB MONITOR OUTPUT |
| | | ===================================== |
----------省略其他输出---------
| | | ---TRANSACTION 1887776, ACTIVE 5 sec |
| | | 2 lock struct(s), heap size 1136, 1 row lock(s) |
| | | MySQL thread id 10838, OS thread handle 139797830510336, query id 1206997 localhost root |
| | | TABLE LOCK table `mytest`.`d` trx id 1887776 lock mode IX |
| | | RECORD LOCKS space id 3476 page no 3 n bits 80 index GEN_CLUST_INDEX of table `mytest`.`d` trx id 1887776 lock_mode X locks rec but not gap | -- 只有一个记录锁，且锁住的是GEN_CLUST_INDEX
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 |
| | | 0: len 6; hex 000001a61b14; asc ;; |
| | | 1: len 6; hex 0000001ccd6e; asc n;; |
| | | 2: len 7; hex a800000047012e; asc G .;; |
| | | 3: len 4; hex 8000001e; asc ;; |
当查询条件是
非索引列的等值查询，REPEATABLE-READ隔离级别下会锁住每个记录（形成表锁的效果），而READ-COMMITTED隔离级别下只锁住查询条件的记录本身
3.4.5 非等值查询 （假如REPEATABLE-READ级别没有Gap LOCK情况）
如果在 REPEATABLE-READ 级别下，且 没有Gap锁 ，则此时 tx1 锁住的是 1，3，5，7 （记录锁），并 标记为删除 ，同时 tx2 可以插入记录 6 。即此时表中的记录为6。
而在 Log 中记录的是 insert 6;del<=7; （先提交的在前面） ，如果此时有slave机器进行同步，该表中的记录为空。
此时， 主从 机器上的 数据不一致 。
当 有Gap Lock 后， tx2 的 insert 6; 需要等待 tx1 的 del<=7; 执行完成后才能执行，此时的Log为 del<=7;insert 6; ，也就不会有数据不一致的问题（符合隔离性要求）
3.4.6 二级索引列的等值查询
3.4.6.1 REPEATABLE-READ隔离级别
- 终端1
mysql root@localhost:mytest> create table e(a int, b int, primary key(a), key(b));
Query OK, 0 rows affected
Time: 0.009s
mysql root@localhost:mytest> insert into e values(1,1),(3,1),(5,3),(7,6),(10,8);
Query OK, 5 rows affected
Time: 0.002s
mysql root@localhost:mytest> select * from e;
+----+---+
| a | b |
+----+---+
| 1 | 1 |
| 3 | 1 |
| 5 | 3 |
| 7 | 6 |
| 10 | 8 |
+----+---+
5 rows in set
Time: 0.010s
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from e where b=3 for update;
+---+---+
| a | b |
+---+---+
| 5 | 3 |
+---+---+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>
- 终端2
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-25 13:50:14 0x7f2533fbe700 INNODB MONITOR OUTPUT |
| | | ===================================== |
|----------省略其他输出---------
| | | MySQL thread id 8365, OS thread handle 139798172346112, query id 1204582 localhost root |
| | | TABLE LOCK table `mytest`.`e` trx id 1887714 lock mode IX |
| | | RECORD LOCKS space id 3478 page no 4 n bits 72 index b of table `mytest`.`e` trx id 1887714 lock_mode X | -- Next-Key lock （index b 二级索引）
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 80000003; asc ;; | --这个行锁锁定a=3
| | | 1: len 4; hex 80000005; asc ;; | --这个行锁锁定b=5
| | | |
| | | RECORD LOCKS space id 3478 page no 3 n bits 72 index PRIMARY of table `mytest`.`e` trx id 1887714 lock_mode X locks rec but not gap | -- Record Lock （index PRIMARY 聚集索引）
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 | --这个行锁锁定b=5
| | | 0: len 4; hex 80000005; asc ;; |
| | | 1: len 6; hex 0000001ccddc; asc ;; |
| | | 2: len 7; hex cc000006c7012a; asc *;; |
| | | 3: len 4; hex 80000003; asc ;; | --这个行锁锁定a=3
| | | |
| | | RECORD LOCKS space id 3478 page no 4 n bits 72 index b of table `mytest`.`e` trx id 1887714 lock_mode X locks gap before rec |
| | | Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 80000006; asc ;; | --这个行锁锁定b=6
| | | 1: len 4; hex 80000007; asc ;; | --这个行锁锁定a=7
| | | |
| | | -------- |
-- 所以二级索引锁住的范围是 (1, 3],（3， 6）
-- 主键索引只锁住了a=5的这条记录 [5, 5]
-- 如果不锁住(3,6)还是可以有别的事物插入b=3的记录，从而产生幻读
3.4.6.1.1 问题1
-- 以下SQL语句在终端2中执行的效果
select * from e where a = 5 lock in share mode; -- 不可执行，因为a=5上有一把记录锁
insert into e values(4, 2); -- 不可以执行，因为b=2在(1, 3]内
insert into e values(6, 5); -- 不可以执行，因为b=5在(3, 6)内
insert into e values(8, 6); -- 可以执行，(8, 6)均不在锁住的范围内
insert into e values(2, 0); -- 可以执行，(2, 0)均不在锁住的范围内
insert into e values(6, 7); -- 可以执行，(6, 7)均不在锁住的范围内
3.4.6.1.2 问题2
insert into e values(6, 6); -- 不可以执行，二级索引除了看key，还要看主键的值，-- (6, 6)在(7, 6)前面，在二级索引锁住的范围(3, 6)中 即(6 6)中的b=6插在(36)中间，而(8 6)中的b=6插在(36)
-- a : 1 3 5 7 10
-- b : 1 1 3 6 8
-- 其实二级索引锁住的不仅仅是二级索引的Key本身，还有对应的value，也就是主键
-- 「1:1」 「1:3」 「3:5」 「6:7」 「8:10」 -- 锁住的是(「3:5」， 「6:7」)
-- ^ ^
-- | |
-- 「6:6」 「8:6」
--
-- b相同的情况下，a也是排序的；a也是二级索引的一部分
- 终端1
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from e where b=3 for update;
+---+---+
| a | b |
+---+---+
| 5 | 3 |
+---+---+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>
- 终端2
mysql gcdb@localhost:mytest> insert into e values(6, 6);
- 终端3
mysql root@localhost:mytest> show engine innodb status;
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-25 14:21:24 0x7f2554069700 INNODB MONITOR OUTPUT |
| | | ===================================== |
----------省略其他输出---------
| | | mysql tables in use 1, locked 1 |
| | | LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 |
| | | MySQL thread id 8335, OS thread handle 139797762664192, query id 1204839 localhost gcdb update |
| | | insert into e values(6, 6) |
| | | ------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED: |
| | | RECORD LOCKS space id 3478 page no 4 n bits 72 index b of table `mytest`.`e` trx id 1887716 lock_mode X locks gap before rec insert intention waiting | -- Next-Key lock （index b 二级索引
| | | Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 | --这个行锁锁定b=6
| | | 0: len 4; hex 80000006; asc ;; |
| | | 1: len 4; hex 80000007; asc ;; |
| | | |
| | | ------------------ |
| | | TABLE LOCK table `mytest`.`e` trx id 1887716 lock mode IX |
| | | RECORD LOCKS space id 3478 page no 4 n bits 72 index b of table `mytest`.`e` trx id 1887716 lock_mode X locks gap before rec insert intention waiting | -- Record Lock （index PRIMARY 聚集索引）
| | | Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 80000006; asc ;; | --这个行锁锁定a=6
| | | 1: len 4; hex 80000007; asc ;; |
| | | |
| | | ---TRANSACTION 1887714, ACTIVE 847 sec |
| | | 4 lock struct(s), heap size 1136, 3 row lock(s) |
| | | MySQL thread id 8365, OS thread handle 139798172346112, query id 1204582 localhost root |
| | | TABLE LOCK table `mytest`.`e` trx id 1887714 lock mode IX |
| | | RECORD LOCKS space id 3478 page no 4 n bits 72 index b of table `mytest`.`e` trx id 1887714 lock_mode X |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 80000003; asc ;; |
| | | 1: len 4; hex 80000005; asc ;; |
| | | |
| | | RECORD LOCKS space id 3478 page no 3 n bits 72 index PRIMARY of table `mytest`.`e` trx id 1887714 lock_mode X locks rec but not gap |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 |
| | | 0: len 4; hex 80000005; asc ;; |
| | | 1: len 6; hex 0000001ccddc; asc ;; |
| | | 2: len 7; hex cc000006c7012a; asc *;; |
| | | 3: len 4; hex 80000003; asc ;; |
| | | |
| | | RECORD LOCKS space id 3478 page no 4 n bits 72 index b of table `mytest`.`e` trx id 1887714 lock_mode X locks gap before rec |
| | | Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 80000006; asc ;; |
| | | 1: len 4; hex 80000007; asc ;; |
| | |
二级索引上加了Record Lock或Next-Key Lock，则对应的主键加Record Lock；二级索引加Gap Lock，则对应的主键不加锁。
3.4.6.2 READ-COMMITTED隔离级别
- 终端1
mysql root@localhost:mytest> set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set
Time: 0.010s
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.000s
mysql root@localhost:mytest> select * from e where b=8 for update;
+----+---+
| a | b |
+----+---+
| 10 | 8 |
+----+---+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>
- 终端2
mysql root@localhost:mytest> set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-25 16:28:12 0x7f254c776700 INNODB MONITOR OUTPUT |
| | | ===================================== |
----------省略其他输出---------
| | | MySQL thread id 10838, OS thread handle 139797830510336, query id 1207298 localhost root |
| | | TABLE LOCK table `mytest`.`e` trx id 1887777 lock mode IX |
| | | RECORD LOCKS space id 3478 page no 4 n bits 72 index b of table `mytest`.`e` trx id 1887777 lock_mode X locks rec but not gap | -- index b 二级索引上的 Record Lock
| | | Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 80000008; asc ;; |
| | | 1: len 4; hex 8000000a; asc ;; |
| | | |
| | | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887777 lock_mode X locks rec but not gap | -- index GEN_CLUST_INDEX 聚集索引上的 Record Lock
| | | Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 |
| | | 0: len 4; hex 8000000a; asc ;; |
| | | 1: len 6; hex 0000001ccddc; asc ;; |
| | | 2: len 7; hex cc000006c70144; asc D;; |
| | | 3: len 4; hex 80000008; asc ;; |
-
当查询条件是二级索引列的等值查询时
-
READ-COMMITTED模式下，二级索引查询的记录上有一个记录锁， 对应的聚集索引上有一个记录锁
-
REPEATABLE-READ模式下，二级索引查询的记录上有一个Next-Key Lock，该记录的下一个记录上有一个Gap-Lock （二级索引）；对应的聚集索引上有一个记录
-
3.5 插入意向锁（insert intention lock）
-
插入意向锁本质上就是个Gap Lock- 普通Gap Lock
不允许在（上一条记录，本记录）范围内插入数据 - 插入意向锁Gap Lock
允许在（上一条记录，本记录）范围内插入数据
- 普通Gap Lock
-
插入意向锁的作用是为了提高并发插入的性能，多个事务同时写入不同数据至同一索引范围（区间）内，并不需要等待其他事务完成，不会发生锁等待
3.5.1. 插入的过程
- 假设现在有记录
10，30，50，70；且为主键，需要插入记录25。
- 找到
小于等于25的记录，这里是10 - 找到
记录10的下一条记录，这里是30 - 判断
下一条记录30上是否有锁（如果有=25的情况，后面再讨论）- 判断
30上面如果没有锁，则 可以插入 - 判断
30上面如果有Record Lock，则 可以插入 - 判断
30上面如果有Gap Lock / Next-Key Lock，则无法插入，(10, 30) / （10, 30]；在30上增加insert intention lock（此时处于waiting状态），当Gap Lock / Next-Key Lock释放时，等待的事物（transaction）将被唤醒，此时记录30上才能获得insert intention lock，然后再插入 记录25
- 判断
- 注意：一个事物
insert 25且没有提交，另一个事物delete 25时，记录25上会有Record Lock
3.5.2 插入意向锁的演示
-
默认隔离级别下演示
-
终端1
mysql root@localhost:mytest> create table f (a int(11) NOT NULL,primary key(a)) ;
Query OK, 0 rows affected
Time: 0.007s
mysql root@localhost:mytest> insert into f select a from e ;
Query OK, 5 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from f;
+----+
| a |
+----+
| 1 |
| 3 |
| 5 |
| 7 |
| 10 |
+----+
5 rows in set
Time: 0.010s
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from f where a<=6 for update;
+---+
| a |
+---+
| 1 |
| 3 |
| 5 |
+---+
3 rows in set
Time: 0.010s
mysql root@localhost:mytest>
- 终端2
mysql gcdb@localhost:mytest> begin;insert into f values (4); -- 插入的SQL语句，之前已阻塞的，等待锁释放中.....
Query OK, 0 rows affected
Time: 0.001s
- 终端3
mysql root@localhost:mytest> show engine innodb status;
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-25 15:05:24 0x7f2554069700 INNODB MONITOR OUTPUT |
| | | ===================================== |
| | | Per second averages calculated from the last 10 seconds |
----------省略其他输出---------
| | | mysql tables in use 1, locked 1 |
| | | LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) |
| | | MySQL thread id 10634, OS thread handle 139798173411072, query id 1205721 localhost gcdb update |
| | | insert into f values (4) | --等待语句
| | | ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED: |
| | | RECORD LOCKS space id 3479 page no 3 n bits 72 index PRIMARY of table `mytest`.`f` trx id 1887728 lock_mode X locks gap before rec insert intention waiting | -- 插入记录4的事物等待中（被终端1中的事物阻塞了），等待 获得 插入意向锁
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 |
| | | 0: len 4; hex 80000005; asc ;; |
| | | 1: len 6; hex 0000001ccde9; asc ;; |
| | | 2: len 7; hex d70000008a012a; asc *;; |
| | | |
| | | ------------------ |
| | | TABLE LOCK table `mytest`.`f` trx id 1887728 lock mode IX |
| | | RECORD LOCKS space id 3479 page no 3 n bits 72 index PRIMARY of table `mytest`.`f` trx id 1887728 lock_mode X locks gap before rec insert intention waiting |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 |
| | | 0: len 4; hex 80000005; asc ;; |
| | | 1: len 6; hex 0000001ccde9; asc ;; |
| | | 2: len 7; hex d70000008a012a; asc *;; |
| | | |
| | | ---TRANSACTION 1887728, ACTIVE 5 sec inserting |
| | | mysql tables in use 1, locked 1 |
| | | LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) |
| | | MySQL thread id 10634, OS thread handle 139798173411072, query id 1205721 localhost gcdb update |
| | | insert into f values (4) |
| | | ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED: |
| | | RECORD LOCKS space id 3479 page no 3 n bits 72 index PRIMARY of table `mytest`.`f` trx id 1887728 lock_mode X locks gap before rec insert intention waiting |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 |
| | | 0: len 4; hex 80000005; asc ;; |
| | | 1: len 6; hex 0000001ccde9; asc ;; |
| | | 2: len 7; hex d70000008a012a; asc *;; |
| | | |
| | | ------------------ |
| | | TABLE LOCK table `mytest`.`f` trx id 1887728 lock mode IX |
| | | RECORD LOCKS space id 3479 page no 3 n bits 72 index PRIMARY of table `mytest`.`f` trx id 1887728 lock_mode X locks gap before rec insert intention waiting |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 |
| | | 0: len 4; hex 80000005; asc ;; |
| | | 1: len 6; hex 0000001ccde9; asc ;; |
| | | 2: len 7; hex d70000008a012a; asc *;; |
| | | |
| | | ---TRANSACTION 1887726, ACTIVE 202 sec |
| | | 2 lock struct(s), heap size 1136, 4 row lock(s) |
| | | MySQL thread id 8365, OS thread handle 139798172346112, query id 1205655 localhost root |
| | | TABLE LOCK table `mytest`.`f` trx id 1887726 lock mode IX |
| | | RECORD LOCKS space id 3479 page no 3 n bits 72 index PRIMARY of table `mytest`.`f` trx id 1887726 lock_mode X |
| | | Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 |
| | | 0: len 4; hex 80000001; asc ;; |
| | | 1: len 6; hex 0000001ccde9; asc ;; |
| | | 2: len 7; hex d70000008a0110; asc ;; |
| | | |
| | | Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 |
| | | 0: len 4; hex 80000003; asc ;; |
| | | 1: len 6; hex 0000001ccde9; asc ;; |
| | | 2: len 7; hex d70000008a011d; asc ;; |
| | | |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 |
| | | 0: len 4; hex 80000005; asc ;; |
| | | 1: len 6; hex 0000001ccde9; asc ;; |
| | | 2: len 7; hex d70000008a012a; asc *;; |
| | | |
| | | Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 |
| | | 0: len 4; hex 80000007; asc ;; |
| | | 1: len 6; hex 0000001ccde9; asc ;; |
| | | 2: len 7; hex d70000008a0137; asc 7;; |
| | | |
3.5.3 插入意向锁提高插入的并发性演示
- 终端1
mysql root@localhost:mytest> create table g (a int(11) NOT NULL,primary key(a));
Query OK, 0 rows affected
Time: 0.053s
mysql root@localhost:mytest> insert into g select * from d;
Query OK, 5 rows affected
Time: 0.002s
mysql root@localhost:mytest> select * from g;
+----+
| a |
+----+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
+----+
5 rows in set
Time: 0.010s
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.000s
mysql root@localhost:mytest> select * from g where a<=40 for update;
+----+
| a |
+----+
| 10 |
| 20 |
| 30 |
| 40 |
+----+
4 rows in set
Time: 0.010s
mysql root@localhost:mytest> commit;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest>
- 终端2
mysql gcdb@localhost:mytest> begin; insert into g values (25);
Query OK, 0 rows affected
Time: 0.001s
Query OK, 1 row affected
Time: 14.466s
mysql gcdb@localhost:mytest>
- 终端3
mysql root@localhost:mytest> show engine innodb status;
+--------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-25 15:34:33 0x7f2554069700 INNODB MONITOR OUTPUT |
| | | ===================================== |
----------省略其他输出---------
| | | 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 |
| | | MySQL thread id 10634, OS thread handle 139798173411072, query id 1206268 localhost gcdb |
| | | TABLE LOCK table `mytest`.`g` trx id 1887740 lock mode IX |
| | | RECORD LOCKS space id 3480 page no 3 n bits 80 index PRIMARY of table `mytest`.`g` trx id 1887740 lock_mode X locks gap before rec insert intention | --插入意向锁已经获得
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 |
| | | 0: len 4; hex 8000001e; asc ;; | --锁定记录30
| | | 1: len 6; hex 0000001ccdf5; asc ;; |
| | | 2: len 7; hex e10000003a012a; asc : *;; |
| | | |
| | | -------- |
- 终端1
mysql root@localhost:mytest> insert into g values (45); -- 插入小于50，大于40的值，插入成功
Query OK, 1 row affected
Time: 0.001s
mysql root@localhost:mytest> insert into g values (35); -- 插入小于40，大于30的值，插入成功
Query OK, 1 row affected
Time: 0.001s
mysql root@localhost:mytest> insert into g values (15); -- 插入小于20，大于10的值，插入成功
Query OK, 1 row affected
Time: 0.001s
mysql root@localhost:mytest> select * from g ;
+----+
| a |
+----+
| 10 |
| 15 |
| 20 |
| 30 |
| 35 |
| 40 |
| 45 |
| 50 |
+----+
8 rows in set
Time: 0.009s
-- 这样就可以并发的插入记录了，而不需要一个事物等待另一事物，所以当所有相关的插入的事物都提交后，30上的插入意向锁便释放
3.6 锁的内部实现
每个事物每个页一个锁对象- 约100个字节
- 通过位图存放锁信息
- 内存占用少
- 没有锁升级
- like Oracle
假设页 (20 100 中有 250 条记录，变量
n_bit=250+64=314，那么实际位图需要40 个字节
用于位图的管理(n_bytes= 1 +3 14 /8=40)。若页中heap_no为2、3、4 的记录都己经上锁，则对应的数据结构lock_rec_t在内存中的关系如下图所示。
由上图可以看到，在页
(20， 100)中，前3条用户记录都有锁，
因此在对应的数据结构的lock_rec_t中对应的heap no位图值都为1。
3.7 显式锁和隐式锁
3.7.1 显式锁和隐式锁
-
显式锁
- 显式锁是使用
全局记录锁哈希表和lock_t结构的锁，通常用show engine innodb status能查看到
- 显式锁是使用
-
隐式锁
- 隐式锁
没有分配关联的lock_t对象所谓的隐式锁，可以理解成一个记录标记，在内存的锁对象在hash中是不存在的。但我们在更新数据块时，会进行如下操作：- 对于
聚集索引，每次更改或插入记录会同时写入对应的事务id和回滚段指针。 - 对于
二级索引，每次更改/插入二级索引记录，会更新二级索引页的最大事务id。
- 对于
- 隐式锁
-
隐式锁的逻辑过程如下:
- A. InnoDB的每条记录中都一个
隐含的trx_id字段，这个字段存在于簇索引的B+Tree中。 - B. 在操作一条记录前，首先根据记录中的
trx_id检查该事务是否是活动的事务(未提交或回滚)。如果是活动的事务，首先将隐式锁转换为显式锁(就是为该事务添加一个锁)。 - C. 检查是否有
锁冲突，如果创建锁，并设置为waiting状态。如果有冲突不加锁,写数据，并将自己的trx_id写入trx_id字段。 - D. 等待加锁成功
被唤醒，或者超时。 - E. 写数据，并将自己的
trx_id写入trx_id字段。Page Lock可以保证操作的正确性。
- A. InnoDB的每条记录中都一个
3.7.2 显式锁（explicit lock）与隐式锁（implicit lock）的区别
- explicit locK
- gap explicit lock
- no gap explicit lock
这
gap通过type_mode中LOCK_GAP来进行设置。
no gap explict lock锁住的是记录以及记录之前范围,否则仅锁住范围。
explicit lock可以是S-lock也可以是X-lock。然而implicit lock总是为X-lock。
explicit lock是非常直接加锁，例如 SELECT * FROM t WHERE rowed = xxx FOR UPDATE ，那
么就需要对这个记录进行加锁，锁的类型为x-lock这个过程通过函数lock_rec_create实现。
implicit lock是指索引记录逻辑上有x-lock，但实际在内存对象中并不含有这个锁信息。这意
味着implicit lock没有任何内存开销， 从减少InnoDB存储引擎的开销。
3.7.3 聚集索引记录的隐式锁
- 聚集索引记录中 implicit lock 的判断较为简单。
- 因为每个
聚集索引记录都有个事务id的隐藏列，只需要通过该事务id判断当前是否为活跃事务就能得知是否有 implicit lock。若通过事务 id查询得到该事务为活跃事务，则此聚集索引记录上有implicit lock。反之，则不含有implicit lock。
- 因为每个
3.7.3 二级索引记录的隐式锁
- 二级索引记录是
不含有事务id的隐藏列，然而 ，每个二级索引页通过page header的PAGE_MAX_TRX_ID保存一个最大事务ID，当二级索引中的任何记录被更新后，都需要更新PAGE_MAX_TRX_ID 的值。因此判断
二级索引记录的implicit lock分为两个步骤进行:- 根据二级索引页的
PAGE_MAX_TRX_ID值进行判断， - 通过
聚集索引记录进行判断。
- 根据二级索引页的
- 因此若当前
PAGE_MAX_TRX_ID 值小于当前活跃事务的最小id 时，则此二级索引记录不含有implicit lock,
即之前已经提交的事务修改了该记录。 - 若
PAGE_MAX_TRX_ID大于等于当前活跃事务的最id 时，则存在下这些可能性:- 存在某活跃事务，
修改了二级索引记录，从而导致 PAGE_MAX_TRX_IDD 的更新 - 存在事务
(可能是活跃也可能已经完成提交操作)，修改了页中其他的二级索引记录，从而导致 PAGE_MAX_TRX_ID 更新。
- 存在某活跃事务，
此时需要通过
二级索引记录对应的聚集索引来判断是否含有implicit lock，这个过程通过函数row_vers_impl_x_locked_off_kernel实现，其判断过程下图所示。
函数 row_vers_impl_x_locked_off_kernel 不仅要判断二级索引记录上是否有锁，并且若有锁，
还要返回当前持有该锁的事务对象。由于二级索引记录不包含隐藏的事务id 列，因此该信息
必然需要通过对应的聚集索引记录来获得。
判断二级索引记录是否持有 implicit lock，只须判断是否存在未提交的活跃事务对记录进行
INSERT DELETE UPDATE 的操作。若有，则必然持有 implicit lock ，并返回该活跃事务对象 trx_t。
- 辅助索引记录的隐式锁参考(姜大佬（MYSQL内核：INNODB存储引擎 卷1）第九章锁)
3.8 一致性的非锁定读
在隔离级别为 READ UNCOMMLTTED、READ COMMITTED、REPEATABLE READ 时，读取（select）操作是不会加锁的，通过行多版本控制（ MVCC ）的方式读取当前执行时间点的记录。
- 终端1
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.000s
mysql root@localhost:mytest> select * from e where b=8; -- 非锁定的读，不会阻塞
+----+---+
| a | b |
+----+---+
| 10 | 8 |
+----+---+
1 row in set
Time: 0.009s
mysql root@localhost:mytest>
- 终端2
mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> select * from e where b=8; -- 非锁定的读，不会阻塞
+----+---+
| a | b |
+----+---+
| 10 | 8 |
+----+---+
1 row in set
Time: 0.010s
mysql gcdb@localhost:mytest>
通过
UNDO 指针的指向，可以读取前一个版本甚至前几个版本的记录（即通过UNDO来构造版本记录），从而实现快照读 （Snapshot Read）;
通过trx_id判断该记录是否被锁住（在线事物列表），从而决定是否要读取之前的版本（UNDO)
四. 死锁
4.1 死锁的介绍
- 两个或两个以上的事务在执行过程中，因
争夺锁资源而造成的一种互相等待的现象- AB-BA, A和B互相等待
- 解决死锁
- 超时，
死锁和锁超时不是同一个东西（锁超时是解决死锁的一种方式） - --innodb_lock_wait_timeout，默认是50s
- 超时，
- waits-for graph
- 自动死锁检测
数据库中的死锁和程序中的死锁不同，数据库中的死锁是不能完全避免的，且数据库中的死锁有检测机制
4.2 AB-BA死锁演示
- 终端1
mysql root@localhost:(none)> show variables like 'innodb%print%deadlocks%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF |
+----------------------------+-------+
1 row in set
Time: 0.012s
mysql root@localhost:(none)> set global innodb_print_all_deadlocks=1; -- 会将死锁信息打印到err_log（搜索 deadlock 关键字）
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:(none)> show variables like 'innodb%print%deadlocks%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | ON |
+----------------------------+-------+
1 row in set
Time: 0.012s
mysql root@localhost:(none)>exit
- 终端1
(root@localhost) 15:39:15 [mytest]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) 15:39:19 [mytest]> delete from e where a=3;
Query OK, 1 row affected (0.00 sec)
- 终端2
(gcdb@localhost) 15:39:52 [mytest]> begin;
Query OK, 0 rows affected (0.00 sec)
(gcdb@localhost) 15:39:56 [mytest]> delete from e where a=5;
Query OK, 1 row affected (0.00 sec)
(gcdb@localhost) 15:40:32 [mytest]> delete from e where a=3;
Query OK, 1 row affected (11.40 sec)
(gcdb@localhost) 15:40:48 [mytest]>
- 终端1
(root@localhost) 15:39:45 [mytest]> delete from e where a=5;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction --出现死锁报错
(root@localhost) 15:40:48 [mytest]>
- 终端3
mysql root@localhost:(none)> show engine innodb status;
+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-29 15:40:54 0x7f254c5f0700 INNODB MONITOR OUTPUT |
----------省略其他输出---------
| | | ------------------------ |
| | | LATEST DETECTED DEADLOCK | --最新检测到的死锁
| | | ------------------------ |
| | | 2018-01-29 15:40:48 0x7f254c776700 |
| | | *** (1) TRANSACTION: |
| | | TRANSACTION 1887794, ACTIVE 16 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 45377, OS thread handle 139798173144832, query id 1310147 localhost gcdb updating |
| | | delete from e where a=3 |
| | | *** (1) WAITING FOR THIS LOCK TO BE GRANTED: |
| | | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887794 lock_mode X locks rec but not gap waiting |
| | | Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 |
| | | 0: len 4; hex 80000003; asc ;; |
| | | 1: len 6; hex 0000001cce31; asc 1;; |
| | | 2: len 7; hex 290000008d1735; asc ) 5;; |
| | | 3: len 4; hex 80000001; asc ;; |
| | | |
| | | *** (2) TRANSACTION: |
| | | TRANSACTION 1887793, ACTIVE 63 sec starting index read, thread declared inside InnoDB 5000 |
| | | 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 45374, OS thread handle 139798173411072, query id 1310151 localhost root updating |
| | | delete from e where a=5 |
| | | *** (2) HOLDS THE LOCK(S): |
| | | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887793 lock_mode X locks rec but not gap |
| | | Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 |
| | | 0: len 4; hex 80000003; asc ;; |
| | | 1: len 6; hex 0000001cce31; asc 1;; |
| | | 2: len 7; hex 290000008d1735; asc ) 5;; |
| | | 3: len 4; hex 80000001; asc ;; |
| | | |
| | | *** (2) WAITING FOR THIS LOCK TO BE GRANTED: |
| | | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887793 lock_mode X locks rec but not gap waiting |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 |
| | | 0: len 4; hex 80000005; asc ;; |
| | | 1: len 6; hex 0000001cce32; asc 2;; |
| | | 2: len 7; hex 2a00000027123d; asc * =;; |
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-29 15:40:54 0x7f254c5f0700 INNODB MONITOR OUTPUT |
| | | ===================================== |
| | | Per second averages calculated from the last 36 seconds |
| | | ----------------- |
| | | BACKGROUND THREAD |
| | | ----------------- |
| | | srv_master_thread loops: 2702 srv_active, 0 srv_shutdown, 1213011 srv_idle |
| | | srv_master_thread log flush and writes: 1215640 |
| | | ---------- |
| | | SEMAPHORES |
| | | ---------- |
| | | OS WAIT ARRAY INFO: reservation count 2793 |
| | | OS WAIT ARRAY INFO: signal count 2732 |
| | | RW-shared spins 0, rounds 4896, OS waits 2134 |
| | | RW-excl spins 0, rounds 3731, OS waits 27 |
| | | RW-sx spins 131, rounds 2447, OS waits 41 |
| | | Spin rounds per wait: 4896.00 RW-shared, 3731.00 RW-excl, 18.68 RW-sx |
| | | ------------------------ |
| | | LATEST DETECTED DEADLOCK |
| | | ------------------------ |
| | | 2018-01-29 15:40:48 0x7f254c776700 |
| | | *** (1) TRANSACTION: |
| | | TRANSACTION 1887794, ACTIVE 16 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 45377, OS thread handle 139798173144832, query id 1310147 localhost gcdb updating |
| | | delete from e where a=3 |
| | | *** (1) WAITING FOR THIS LOCK TO BE GRANTED: |
| | | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887794 lock_mode X locks rec but not gap waiting |
| | | Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 |
| | | 0: len 4; hex 80000003; asc ;; |
| | | 1: len 6; hex 0000001cce31; asc 1;; |
| | | 2: len 7; hex 290000008d1735; asc ) 5;; |
| | | 3: len 4; hex 80000001; asc ;; |
| | | |
| | | *** (2) TRANSACTION: |
| | | TRANSACTION 1887793, ACTIVE 63 sec starting index read, thread declared inside InnoDB 5000 |
| | | 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 45374, OS thread handle 139798173411072, query id 1310151 localhost root updating |
| | | delete from e where a=5 |
| | | *** (2) HOLDS THE LOCK(S): |
| | | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887793 lock_mode X locks rec but not gap |
| | | Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 |
| | | 0: len 4; hex 80000003; asc ;; |
| | | 1: len 6; hex 0000001cce31; asc 1;; |
| | | 2: len 7; hex 290000008d1735; asc ) 5;; |
| | | 3: len 4; hex 80000001; asc ;; |
| | | |
| | | *** (2) WAITING FOR THIS LOCK TO BE GRANTED: |
| | | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887793 lock_mode X locks rec but not gap waiting |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 |
| | | 0: len 4; hex 80000005; asc ;; |
| | | 1: len 6; hex 0000001cce32; asc 2;; |
| | | 2: len 7; hex 2a00000027123d; asc * ' =;; |
| | | 3: len 4; hex 80000003; asc ;; |
| | | |
| | | *** WE ROLL BACK TRANSACTION (2) |
| | | ------------ |
| | | TRANSACTIONS |
| | | ------------ |
| | | Trx id counter 1887800 |
| | | Purge done for trx's n:o < 1887800 undo n:o < 0 state: running but idle |
| | | History list length 61 |
| | | LIST OF TRANSACTIONS FOR EACH SESSION: |
| | | ---TRANSACTION 421284540507984, not started |
| | | 0 lock struct(s), heap size 1136, 0 row lock(s) |
| | | ---TRANSACTION 421284540509808, not started |
| | | 0 lock struct(s), heap size 1136, 0 row lock(s) |
| | | ---TRANSACTION 1887794, ACTIVE 22 sec |
| | | 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2 |
| | | MySQL thread id 45377, OS thread handle 139798173144832, query id 1310147 localhost gcdb |
| | | TABLE LOCK table `mytest`.`e` trx id 1887794 lock mode IX |
| | | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887794 lock_mode X locks rec but not gap |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 |
| | | 0: len 4; hex 80000005; asc ;; |
| | | 1: len 6; hex 0000001cce32; asc 2;; |
| | | 2: len 7; hex 2a00000027123d; asc * ' =;; |
| | | 3: len 4; hex 80000003; asc ;; |
| | | |
| | | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887794 lock_mode X locks rec but not gap |
| | | Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 |
| | | 0: len 4; hex 80000003; asc ;; |
| | | 1: len 6; hex 0000001cce32; asc 2;; |
| | | 2: len 7; hex 2a000000271266; asc * ' f;; |
| | | 3: len 4; hex 80000001; asc ;; |
| | | |
在MySQL出现错误
（ 非死锁 ）的时候，线程中的事物是中止的，需要显示的提交或回滚；而死锁时，MySQL会自动回滚;
- 假如一个事物中有10个步骤，当执行到步骤8时:
- 出现普通错误，在步骤8中停止，只要重新执行步骤8即可，然后
再继续执行后续操作 - 出现死锁时，该事物中，步骤8之前的操作都会
自动回滚
- 出现普通错误，在步骤8中停止，只要重新执行步骤8即可，然后
4.3 官网两种死锁演示
4.3.1 第一种
| 步骤 | 会话1 | 会话2 | 会话3 |
|---|---|---|---|
| 1 | START TRANSACTION; INSERT INTO t1 VALUES(1) |
||
| 2 | START TRANSACTION; INSERT INTO t1 VALUES(1); |
||
| 3 | START TRANSACTION; INSERT INTO t1 VALUES(1); |
||
| 4 | ROLLBACK; | ||
| 5 | INSERT OK ； | ||
| 6 | ERROR1213,Deadlock |
- 终端1
mysql gcdb@localhost:mytest> CREATE TABLE `t1` (
-> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
-> `a` int(11) unsigned DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> UNIQUE KEY `a` (`a`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected
Time: 0.009s
mysql gcdb@localhost:mytest> insert into t1(a) values(10);
Query OK, 1 row affected
Time: 0.001s
mysql gcdb@localhost:mytest> insert into t1(a) values(20);
Query OK, 1 row affected
Time: 0.001s
mysql gcdb@localhost:mytest> insert into t1(a) values(30);
Query OK, 1 row affected
Time: 0.001s
mysql gcdb@localhost:mytest> select * from t1;
+-----+----+
| id | a |
+-----+----+
| 100 | 10 |
| 101 | 20 |
| 102 | 30 |
+-----+----+
3 rows in set
Time: 0.010s
mysql gcdb@localhost:mytest> start transaction;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> insert into t1(a) values(15);
Query OK, 1 row affected
Time: 0.001s
- 终端2
mysql gcdb@localhost:mytest> start transaction;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> insert into t1(a) values(15);
- 终端3
mysql root@localhost:mytest> start transaction;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> insert into t1(a) values(15);
- 终端1
mysql gcdb@localhost:mytest> rollback;
Query OK, 0 rows affected
Time: 0.001s
- 终端2
mysql gcdb@localhost:mytest> insert into t1(a) values(15); --终端2，insert into 语句执行成功。
Query OK, 1 row affected
Time: 8.870s
mysql gcdb@localhost:mytest>
- 终端3
mysql root@localhost:mytest> insert into t1(a) values(15);
(1213, u'Deadlock found when trying to get lock; try restarting transaction') --终端3，报ERROR1213 死锁
mysql root@localhost:mytest>
- 终端1
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-30 14:58:10 0x7f25380b3700 INNODB MONITOR OUTPUT |
| | | ===================================== |
----------省略其他输出---------
| | | ------------------------ |
| | | LATEST DETECTED DEADLOCK |
| | | ------------------------ |
| | | 2018-01-30 14:58:07 0x7f254c672700 |
| | | *** (1) TRANSACTION: |
| | | TRANSACTION 1887869, ACTIVE 9 sec inserting, thread declared inside InnoDB 1 |
| | | mysql tables in use 1, locked 1 |
| | | LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 |
| | | MySQL thread id 53739, OS thread handle 139798173144832, query id 1335448 localhost gcdb update | --终端2线程
| | | insert into t1(a) values(15) | --终端2线程执行insert语句
| | | *** (1) WAITING FOR THIS LOCK TO BE GRANTED: |
| | | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887869 lock_mode X locks gap before rec insert intention waiting | --终端2中的事物在等待a记录20上的插入意向锁（Gap Lock）
| | | Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 00000014; asc ;; | --锁定a记录20
| | | 1: len 4; hex 00000065; asc e;; |
| | | |
| | | *** (2) TRANSACTION: |
| | | TRANSACTION 1887870, ACTIVE 7 sec inserting, thread declared inside InnoDB 1 |
| | | mysql tables in use 1, locked 1 |
| | | 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 |
| | | MySQL thread id 53747, OS thread handle 139798172346112, query id 1335449 localhost root update | --终端3线程
| | | insert into t1(a) values(15) |
| | | *** (2) HOLDS THE LOCK(S): |
| | | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887870 lock mode S locks gap before rec | --终端3中的事物持有a记录20的 S-Gap Lock
| | | Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 00000014; asc ;; |
| | | 1: len 4; hex 00000065; asc e;; |
| | | |
| | | *** (2) WAITING FOR THIS LOCK TO BE GRANTED: |
| | | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887870 lock_mode X locks gap before rec insert intention waiting | --端3中的事物在等待a记录20上的插入意向锁（Gap Lock)
| | | Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 00000014; asc ;; |
| | | 1: len 4; hex 00000065; asc e;; |
| | | |
| | | *** WE ROLL BACK TRANSACTION (2) | --回滚`终端3`中的事物，所以`终端2`中才插入成功
| | | ------------ |
| | | TRANSACTIONS |
| | | ------------ |
| | | Trx id counter 1887871 |
| | | Purge done for trx's n:o < 1887869 undo n:o < 0 state: running but idle |
| | | History list length 14 |
| | | LIST OF TRANSACTIONS FOR EACH SESSION: |
| | | ---TRANSACTION 421284540509808, not started |
| | | 0 lock struct(s), heap size 1136, 0 row lock(s) |
| | | ---TRANSACTION 421284540507984, not started |
| | | 0 lock struct(s), heap size 1136, 0 row lock(s) |
| | | Trx id counter 1887871 |
| | | Purge done for trx's n:o < 1887869 undo n:o < 0 state: running but idle |
| | | History list length 14 |
| | | LIST OF TRANSACTIONS FOR EACH SESSION: |
| | | ---TRANSACTION 421284540509808, not started |
| | | 0 lock struct(s), heap size 1136, 0 row lock(s) |
| | | ---TRANSACTION 421284540507984, not started |
| | | 0 lock struct(s), heap size 1136, 0 row lock(s) |
| | | ---TRANSACTION 1887869, ACTIVE 12 sec |
| | | 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1 |
| | | MySQL thread id 53739, OS thread handle 139798173144832, query id 1335448 localhost gcdb |
| | | TABLE LOCK table `mytest`.`t1` trx id 1887869 lock mode IX |
| | | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887869 lock mode S |
| | | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887869 lock mode S locks gap before rec |
| | | Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 00000014; asc ;; | --锁定记a录20
| | | 1: len 4; hex 00000065; asc e;; |
| | | |
| | | Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 0000000f; asc ;; | --锁定记a录15
| | | 1: len 4; hex 00000068; asc h;; |
| | | |
| | | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887869 lock_mode X locks gap before rec insert intention |
| | | Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 00000014; asc ;; | --锁定a记录20
| | | 1: len 4; hex 00000065; asc e;; |
| | | |
mysql gcdb@localhost:mytest> SHOW processlist > ;
+-------+------+-----------+--------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+-----------+--------+---------+------+----------+------------------+
| 53739 | gcdb | localhost | mytest | Sleep | 1463 | | <null> | --终端2线程号53739
| 53742 | gcdb | localhost | mytest | Query | 0 | starting | SHOW processlist | --终端1线程号53742
| 53747 | root | localhost | mytest | Sleep | 1461 | | <null> | --终端3线程号53747
+-------+------+-----------+--------+---------+------+----------+------------------+
3 rows in set
Time: 0.010s
mysql gcdb@localhost:mytest>
-
死锁具体分析：
-
原来加在
a记录15上的锁，都转移到了a记录20上了( 锁继承 )- 因为
终端1中的事物回滚，则a记录15标记为删除（delete-mark）； - 而
终端2和终端3中的事物都对a记录15添加了S-Lock（后台 Purge 线程会把 a记录15 给删除）； - 此时
下一个记录，即a记录20会继承之前a记录15上的锁，所以上面看到了锁的信息都在a记录20上。
- 因为
-
此时的锁的状态是：
终端2持有a记录20的S Gap-Lock，并且等待X locks gap insert intention lock终端3持有a记录20的 S Gap-Lock ，并且等待X locks gap insert intention lock
-
-
X Lock 和 S Lock 是不兼容的
终端3的insert intention lock等待会话2的S Gap-Lock的释放；终端2的insert intention lock等待会话3的S Gap-Lock的释放；- 都在等待对方释放，所以产生了死锁
至于为什么加
S Gap-Lock，是因为在插入之前还需要增加一步检查：如果记录中有唯一约束，判断存在一条记录等于当前插入的记录时，则需要在这个记录加上S Gap-Lock。
- 完整的插入过程如下：
假设现在有记录 10，30，50，70 ；且为 主键 ，需要插入记录 25 。
1. 找到`小于等于25的记录` ，这里是`记录10`◦
- 如果记录中已经`存在记录25` ，且带有`唯一性约束`，则需要在`记录25`上增加`S Gap-lock`◦
- 不直接报错退出或者提示已存在的原因，是因为有可能之前的 `记录25` 标记为`删除( delete-mark )`，然后等待 `purg`◦
- 如果 `假设 这里 `没有S Gap-Lock` ，此时 `记录30` 上也 `没有锁` ，按照下面的步骤，可以插入 `两个记录25` ，破坏了`唯一性约束`
2. 找到`记录10的下一条记录` ，这里是 `记录30`
3. 判断 `下一条记录30` 上是`否有锁`。
判断`记录30`上面如果 `没有锁` ，则 `可以插入`◦
判断`记录30`上面如果有`Record Lock` ，则 `可以插入`◦
判断`记录30`上面如果有 `Gap Lock / Next-Key Lock` ，则无法插入，因为锁的范围是 `(10, 30) / （10, 30]` ；在 `记录30` 上增加 `insert intention lock` （此时处于 waiting 状态），当 `Gap Lock / Next-Key Lock` 释放时，`等待的事物（transaction）`将被 `唤醒` ，此时 `记录30` 上才能获得 `insert intention lock` ，然后`再插入记录25`。
4.3.1 第二种
| 步骤 | 会话1 | 会话2 | 会话3 |
|---|---|---|---|
| 1 | START TRANSACTION; DELETE FROM t1 WHERE i = 1; |
||
| 2 | START TRANSACTION; INSERT INTO t1 VALUES(1); |
||
| 3 | START TRANSACTION; INSERT INTO t1 VALUES(1); |
||
| 4 | COMMIT; | ||
| 5 | INSERT OK | ||
| 6 | ERROR1213,Deadlock |
这里不累赘上面步骤2~5，展示步骤1和步骤6
- 终端1
mysql gcdb@localhost:mytest> start transaction;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> DELETE FROM t1 WHERE a = 20;
Query OK, 1 row affected
Time: 0.001s
- 终端1
mysql gcdb@localhost:mytest> SHOW processlist;
+-------+------+-----------+--------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+-----------+--------+---------+------+----------+------------------+
| 53739 | gcdb | localhost | mytest | Sleep | 30 | | <null> | --终端2线程号53739
| 53742 | gcdb | localhost | mytest | Query | 0 | starting | SHOW processlist | --终端1线程号53742
| 53747 | root | localhost | mytest | Sleep | 23 | | <null> | --终端3线程号53747
+-------+------+-----------+--------+---------+------+----------+------------------+
3 rows in set
Time: 0.011s
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | | |
| | | ===================================== |
| | | 2018-01-30 15:47:02 0x7f25380b3700 INNODB MONITOR OUTPUT |
| | | ===================================== |
----------省略其他输出---------
| | | ------------------------ |
| | | LATEST DETECTED DEADLOCK |
| | | ------------------------ |
| | | 2018-01-30 15:46:34 0x7f254c672700 |
| | | *** (1) TRANSACTION: |
| | | TRANSACTION 1887876, ACTIVE 13 sec inserting |
| | | mysql tables in use 1, locked 1 |
| | | LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1 |
| | | MySQL thread id 53739, OS thread handle 139798173144832, query id 1336331 localhost gcdb update | --终端2
| | | insert into t1(a) values(20) | --终端2线程执行insert语句
| | | *** (1) WAITING FOR THIS LOCK TO BE GRANTED: |
| | | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887876 lock_mode X locks gap before rec insert intention waiting | --终端2中的事物在等待a记录30上的插入意向锁（Gap Lock）
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 0000001e; asc ;; | --锁定a记录30
| | | 1: len 4; hex 00000066; asc f;; |
| | | |
| | | *** (2) TRANSACTION: |
| | | TRANSACTION 1887877, ACTIVE 6 sec inserting, thread declared inside InnoDB 1 |
| | | mysql tables in use 1, locked 1 |
| | | 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1 |
| | | MySQL thread id 53747, OS thread handle 139798172346112, query id 1336336 localhost root update | --终端3
| | | insert into t1(a) values(20) |
| | | *** (2) HOLDS THE LOCK(S): |
| | | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887877 lock mode S |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 0000001e; asc ;; |
| | | 1: len 4; hex 00000066; asc f;; |
| | | |
| | | *** (2) WAITING FOR THIS LOCK TO BE GRANTED: |
| | | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887877 lock_mode X locks gap before rec insert intention waiting |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 0000001e; asc ;; |
| | | 1: len 4; hex 00000066; asc f;; |
| | | |
| | | *** WE ROLL BACK TRANSACTION (2) | --回滚`终端3`中的事务
| | | ------------ |
| | | TRANSACTIONS |
| | | ------------ |
| | | Trx id counter 1887879 |
| | | Purge done for trx's n:o < 1887879 undo n:o < 0 state: running but idle |
| | | History list length 17 |
| | | LIST OF TRANSACTIONS FOR EACH SESSION: |
| | | ---TRANSACTION 421284540509808, not started |
| | | 0 lock struct(s), heap size 1136, 0 row lock(s) |
| | | ---TRANSACTION 421284540507984, not started |
| | | 0 lock struct(s), heap size 1136, 0 row lock(s) |
| | | ---TRANSACTION 1887876, ACTIVE 41 sec |
| | | 4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1 |
| | | MySQL thread id 53739, OS thread handle 139798173144832, query id 1336331 localhost gcdb |
| | | TABLE LOCK table `mytest`.`t1` trx id 1887876 lock mode IX |
| | | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887876 lock mode S |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 0000001e; asc ;; |
| | | 1: len 4; hex 00000066; asc f;; |
| | | |
| | | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887876 lock_mode X locks gap before rec insert intention |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 0000001e; asc ;; |
| | | 1: len 4; hex 00000066; asc f;; |
| | | |
| | | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887876 lock mode S locks gap before rec |
| | | Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 00000014; asc ;; |
| | | 1: len 4; hex 0000006a; asc j;; |
| | | |
| | | Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 |
| | | 0: len 4; hex 0000001e; asc ;; |
| | | 1: len 4; hex 00000066; asc f;; |
| | | |
| | | -------- |
- 这种死锁的情况，线上是无法完全避免的，解决的办法就是
程序端重试。 - 后续涉及Purge时，可以只使用
两条SQL语句，就出现死锁现象，这里使用了3条SQL语句。 mysqld-debug版本可以操作更多的参数（比如关闭purge），并且可以看到丰富的信息，可以用于故障诊断。
五.AI自增锁
5.1 自增锁
- 一个表一个自增
- AUTO_INCREMENT PK
- 在事务提交前释放
- 其他的锁在事务提交时才释放
- 如果AI锁在提交后才释放，那其他事物就没法插入了，无法实现并发
5.2 演示
5.2.1 演示1
- 终端1
mysql gcdb@localhost:mytest> create table t_ai_1(a int auto_increment, b int , primary key(a));
Query OK, 0 rows affected
Time: 0.007s
mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> insert into t_ai_1 values (NULL, 10); -- 插入一个值，且事物没有提交
Query OK, 1 row affected
Time: 0.001s
mysql gcdb@localhost:mytest>
- 终端2
mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> insert into t_ai_1 values(NULL, 20); --终端1中的事物没有提交，但是终端2中的事物仍能提交，说明AI锁已经释放了
Query OK, 1 row affected
Time: 0.001s
mysql gcdb@localhost:mytest>
-- AI锁 在事物提交前就释放了，类似latch，使用完就释放了
5.2.2 演示2
- 终端1和终端2
mysql gcdb@localhost:mytest> rollback;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest>
- 终端1
mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.000s
mysql gcdb@localhost:mytest> insert into t_ai_1 values (NULL, 20);
Query OK, 1 row affected
Time: 0.001s
mysql gcdb@localhost:mytest> commit;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> select * from t_ai_1; -- 因为之前的两次插入都rollback了，但是AI锁其实是提交的，导致现在插入时，自增列的序号从3开始
+---+----+
| a | b |
+---+----+
| 3 | 20 |
+---+----+
1 row in set
Time: 0.010s
mysql gcdb@localhost:mytest>
事物回滚后，
自增值不会跟着回滚，导致自增值不连续，但是这个值连续也没什么意义。
5.3 AI自增锁的方式
所有的插入都是insert-like
- 如果插入前能
确定行数的，就是simple inserts- insert into table_1 values(NULL, 1), (NULL, 2);
- 如果插入前
不能确定行数的，就是bulk inserts- insert into table_1 select * from table_2;
- 如果
部分自增长，部分指定的，就是mixed-mode inserts
insert ... on duplicate key update 不推荐 使用
- 非ANSI SQL 标准
- 效果并非预期所期望的那样
5.3.1 设置自增并发度
--innodb_autoinc_lock_mode={0|1|2}
-
innodb_autoinc_lock_mode 是
read-only的,需要修改后重启MySQL -
0 传统方式
- 在
SQL语句执行完之后，AI锁才释放 - 例如：当 insert ... select ... 数据量很大时（比如执行10分钟），那在这个
SQL执行完毕前，其他事物是不能插入的（AI锁未释放） - 这样可以保证在这个SQL语句内插入的数据，自增值是
连续的，因为在这个时间内，AI自增锁是被这个SQL持有的，且没有释放
- 在
-
1 默认参数（ 大部分情况设置为1 ）
-
bulk inserts， 同传统方式一样- 对于
bulk inserts的方式，和0 - 传统方式一样，在SQL执行完之后，AI锁才释放
- 对于
-
simple inserts， 并发方式
- 在
SQL运行完之前，确定自增值之后 ，就可以释放自增锁了
- 在
-
因为
bulk inserts不知道要插入多少行，所以只能等insert结束后，才知道N的值，然后一次性(ai + N)
而simple inserts知道插入的行数（M），所以可以先 (ai + M)，然后将锁释放掉，给别的事物用，然后自己慢慢插入数据 -
-
参数2
- 所有自增都可以并发方式（ 不同于Simple inserts的方式 ）
- 同一SQL语句自增可能不连续
- row-based binlog
for (i = ai; until_no_rec; i++) { acquire AI_Lock # 插入前申请锁 insert one record... # 只插入一条记录 ai = ai + 1 # 自增值+1 release AI_Lock # 释放锁 }这样做的好处是，对于批量的、耗时的插入，SQL不会长时间的持有AI自增锁，而是插入
一条（有且仅插入一条，而simple inserts是确定好的M条）语句后就 释放 ，这样可以给别的事物使用，实现并发。
但是这种方式并发度是增加了 ，但是性能不一定变好，尤其是单线程导入数据时，要不断的申请和释放锁
对于批量插入来说，自增就可能变的不连续了（需要和开发沟通，是否可以接受）
5.4. 自增列与索引
mysql gcdb@localhost:mytest> create table t_ai_1(a int auto_increment, b int , key(b,a));
(1075, u'Incorrect table definition; there can be only one auto column and it must be defined as a key') --错误定义，这里有前提条件必须定义为key的第一列
mysql gcdb@localhost:mytest> create table t_ai_1(a int auto_increment, b int , key(a,b));
Query OK, 0 rows affected
Time: 0.008s
mysql gcdb@localhost:mytest>
自增列必须定义为索引列 ，且
必须是第一个列，这样做的好处是，因为索引的第一个列是排序的，这样重启后，数据库可以直接找到最后一个自增值，然后N+1操作后，就可以作为下一个自增列的值 了（否则要全表扫描了；MySQL的自增列的值不是持久化的）。
5.5. 自增的两个参数
- --auto_increment_increment = 1
- --auto_increment_offset = 1
mysql gcdb@localhost:mytest> show variables like 'auto%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 | --步长
| auto_increment_offset | 1 | --初始值
| autocommit | ON |
| automatic_sp_privileges | ON |
+--------------------------+-------+
4 rows in set
Time: 0.013s
-
两个服务器上的User表的主键的值就没有交叉了
-
如果有三台服务器，则
A:[offset = 1, increment=3] ， B:[offset = 2, increment=3] ， C:[offset = 3, increment=3] -
如果一开始不知道后面会有多少台服务器，则可以一开始把increment设置的大一点，比如是10，这样只会浪费一点自增值
-
这样做的目的是保证
每个节点上产生的自增值是全局唯一的，这样做并不能用来做双主(比如一些额外的唯一索引能保证全局唯一么)
