mysql表级锁权限

转载

墨韵流香 2024-11-22 18:29:10

文章标签 mysql表级锁权限数据库 python mysql GAP 文章分类 MySQL 数据库

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 b 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列上加上非唯一索引。

alter table record add index idx_author_id (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

这个更新一定是加了X锁的（排它锁）和GAP锁（间隙锁）；

Session B

执行以下插入语句，其中authorId ＝ 4；

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间隙锁。

再比如，其中authorId＝ 3；

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

这两个还是稍微有些差别的，就是在lock_mode: X 这个只有一个锁——X锁，没有间隙锁。

我们再来执行插入，其中authorId ＝ 5 ；

mysql> insert into record (title,shortName,authorId,createTime,state,totalView)  values ('hello world 666','hello-world-0',5,'2015-10-11 08:08:08',6,10);
Query OK, 1 row affected (0.00 sec)

看到了没，居然插入成功了，说明4，5 这个间隙（简单的说）是没有上锁的。这个间隙没有间隙锁。所以执行插入成功了。

行锁（Record Lock）：锁直接加在索引记录上面。
间隙锁（Gap Lock）：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。
Next-Key Lock：行锁与间隙锁组合起来用就叫做Next-Key Lock。默认情况下，InnoDB工作在可重复读隔离级别下，并且以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁与间隙锁的组合，这样，当InnoDB扫描索引记录的时候，会首先对选中的索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。如果一个间隙被事务T1加了锁，其它事务是不能在这个间隙插入记录的。

总结：间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。另外，在上面的例子中，我们选择的是一个普通（非唯一）索引字段来测试的，这不是随便选的，因为如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁，而不会使用Next-Key Lock的方式，也就是说不会对索引之间的间隙加锁。

=========END=========

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。