MySQL事务隔离级别和锁
表结构
create table record(
id int auto_increment primary key,
title varchar(255) not null,
shortName varchar(255) not null,
authorId int not null,
createTime datetime not null,
state int not null,
totalView int default null
);
insert into record (title,shortName,authorId,createTime,state,totalView)
values (‘hello world 000‘,‘hello-world-0‘,1,‘2015-10-11 08:08:08‘,1,10),
(‘hello world 111‘,‘hello-world-1‘,1,‘2015-10-11 08:08:08‘,2,10),
(‘hello world 222‘,‘hello-world-2‘,2,‘2015-10-11 08:08:08‘,3,10),
(‘hello world 333‘,‘hello-world-3‘,3,‘2015-10-11 08:08:08‘,4,10),
(‘hello world 444‘,‘hello-world-4‘,3,‘2015-10-11 08:08:08‘,5,10);
首先关于事务的隔离级别
还有锁的分类,粒度和策略
MySQL的多版本控制MVCC
RC隔离级别下的锁
在READ-COMMITTED隔离级别下,行锁的表现如下,
SessionA
开启事务
mysql>
mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED |
+-----------------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql>
SessionB
开启事务
mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED |
+-----------------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql>
SessionA
在Session A中更新id = 1 的纪录,如下,
mysql> update record set title = ‘session a update‘ where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
更新成功了,接下来在Session B中更新同一个id = 1的纪录,
Session B
mysql> update record set title = ‘session b update‘ where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
可以看到由于Session A还没有提交,SessionA持有id = 1 的纪录的行锁,所以当Session B更新时没有相应的行锁,所以锁等待超时更新失败。同时也可以看到在当前的事务下可以更新其他的纪录。
mysql> update record set title = ‘session b update‘ where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update record set title = ‘session b update‘ where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
更新id = 2 的纪录成功了。
但要注意的是,我们通过id字段进行更新的,通过id字段选择要更新的数据行,同时id字段是一个主键列,如果在没有索引的字段上查找更新会有怎么样的效果呢?我们来看一下。
Session AA
开启事务,
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
Session BB
开启事务,
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
Sessoin AA
mysql> update record set title = ‘session a update‘ where authorId = 1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
更新成功。接下来在Session BB中更新authorId = 1 的数据行。按照上面说的情况,authorId列上没有索引,这样会导致锁表,但实际的效果是怎么样的呢?
Session BB
mysql> update record set title = ‘session c update‘ where authorId = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update record set title = ‘session c update‘ where authorId = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
可以看到更新authorId = 2 的纪录没有等待锁,也就是其他数据行没有被锁住,而更新authorId = 1 的数据行时却发现锁等待超时(Session AA事务还没有提交)。
我们前面也说了,当更新非索引列时会把整个表锁住,这是怎么回事?
这时因为当通过authorId更新时,mysql存储引擎不知道要锁定哪些数据行,因为authorId上没有索引,所以返回整个表的数据行,同时锁住。然后mysql服务器层进行过滤,同时解锁不符合条件的数据行(调用存储引擎的unlock操作)。
最后提交 Session AA的事务,
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
然后在Session BB中执行更新,提交,
mysql> update record set title = ‘session c update‘ where authorId = 1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
RR隔离级别下的锁
我们来看一下RR隔离级别下的锁,首先我们把authorId列上加上索引。
Session A
开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
Session B
开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
Session A
执行以下更新语句
mysql> select * from record;
+----+------------------+---------------+----------+---------------------+-------+-----------+
| id | title | shortName | authorId | createTime | state | totalView |
+----+------------------+---------------+----------+---------------------+-------+-----------+
| 1 | hello world 000 | hello-world-0 | 1 | 2015-10-11 08:08:08 | 1 | 10 |
| 2 | hello world 111 | hello-world-1 | 1 | 2015-10-11 08:08:08 | 2 | 10 |
| 3 | hello world 222 | hello-world-2 | 2 | 2015-10-11 08:08:08 | 3 | 10 |
| 4 | hello world 333 | hello-world-3 | 3 | 2015-10-11 08:08:08 | 4 | 10 |
| 5 | hello world 444 | hello-world-4 | 3 | 2015-10-11 08:08:08 | 5 | 10 |
| 6 | session a update | hello-world-0 | 4 | 2015-10-11 08:08:08 | 1 | 10 |
| 7 | hello world 666 | hello-world-0 | 5 | 2015-10-11 08:08:08 | 1 | 10 |
| 8 | hello world 666 | hello-world-0 | 6 | 2015-10-11 08:08:08 | 1 | 10 |
+----+------------------+---------------+----------+---------------------+-------+-----------+
8 rows in set (0.00 sec)
mysql> update record set title = ‘session a update‘ where authorId = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
Session B
执行以下插入语句,
mysql> insert into record (title,shortName,authorId,createTime,state,totalView) values (‘hello world 666‘,‘hello-world-0‘,4,‘2015-10-11 08:08:08‘,6,10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
可以看到锁等待超时,看一下是等待什么锁,
> select * from INNODB_LOCKS
******************** 1. row *********************
lock_id: 11604:64:4:11
lock_trx_id: 11604
lock_mode: X,GAP
lock_type: RECORD
lock_table: `test`.`record`
lock_index: idx_author_id
lock_space: 64
lock_page: 4
lock_rec: 11
lock_data: 5, 7
******************** 2. row *********************
lock_id: 11603:64:4:11
lock_trx_id: 11603
lock_mode: X,GAP
lock_type: RECORD
lock_table: `test`.`record`
lock_index: idx_author_id
lock_space: 64
lock_page: 4
lock_rec: 11
lock_data: 5, 7
2 rows in set
可以看到lock_mode项是 X,GAP。X表示排他锁,GAP间隙锁。
再比如,
mysql> insert into record (title,shortName,authorId,createTime,state,totalView) values (‘hello world 666‘,‘hello-world-0‘,3,‘2015-10-11 08:08:08‘,6,10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
等待的锁,
> select * from INNODB_LOCKS
******************** 1. row *********************
lock_id: 11604:64:4:10
lock_trx_id: 11604
lock_mode: X,GAP
lock_type: RECORD
lock_table: `test`.`record`
lock_index: idx_author_id
lock_space: 64
lock_page: 4
lock_rec: 10
lock_data: 4, 6
******************** 2. row *********************
lock_id: 11603:64:4:10
lock_trx_id: 11603
lock_mode: X
lock_type: RECORD
lock_table: `test`.`record`
lock_index: idx_author_id
lock_space: 64
lock_page: 4
lock_rec: 10
lock_data: 4, 6
2 rows in set
这两个还是稍微有些差别的。
注意:
记录锁:是加在索引记录上的。
间隙锁:对索引记录间的范围加锁,加在最后一个索引记录的前面或者后面
Next-key锁:记录锁和间隙锁的组合,间隙锁锁定记录锁之前的范围
间隙锁主要是防止幻象读,用在Repeated-Read(简称RR)隔离级别下。在Read-Commited(简称RC)下,一般没有间隙锁(有外键情况下例外,此处不考虑)。间隙锁还用于statement based replication
间隙锁有些副作用,如果要关闭,一是将会话隔离级别改到RC下,或者开启 innodb_locks_unsafe_for_binlog(默认是OFF)。
间隙锁只会出现在辅助索引上,唯一索引和主键索引是没有间隙锁。间隙锁(无论是S还是X)只会阻塞insert操作。
=========END=========
原文:http://my.oschina.net/xinxingegeya/blog/499717