mysql的各种锁

最新推荐文章于 2024-05-29 14:41:02 发布

Relian哈哈

最新推荐文章于 2024-05-29 14:41:02 发布

阅读量1.9k

点赞数 1

分类专栏： MySQL

本文链接：https://blog.csdn.net/lzxlfly/article/details/85218926

版权

MySQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

一、为什么用锁

提到数据库锁，我们往往会产生这样的疑问：数据库既然有了事务，为什么要用锁。只有一种可能，就是事务并不能解决所数据安全问题。下面先看一下事务所能解决的问题。

1、事物并发遇到的问题

对数据库中数据操作通常遇到以下问题

（1）脏读：事务A已经更改了第N行数据，但是没有提交更改，与此同时另一个事务B也读取了第N行数据。但是由于某种原因事务A失败了并进行了回滚操作，此时事务B读取到的数据就是脏数据

（2）不可重复读：是指在一个事务A需要多次读取同一数据，在事务A还没有结束时，另一事务B也读取该数据并进行了修改，事物A第二次读取该数据可能会读到和第一次不一样的。也就是事务A中的两次读取数据之间，由于事务B的修改，造成事务A两次读到的数据可能不一样。如此一来，就出现了一个事务内两次不能读到相同的数据，即事务A第一次读的数据是不能在第二次重复读到。

（3）幻读：指两个或者多个事务对同一类型数据的读取，如事务A读取的表中一种类型的数据（假设该类型数据有4行），与此同时事物B又往表中插入了一行事务A 需要读取的类型数据。这时候事务A要读取的实际上是5行数据，而它只看到且读取了4行，事物A出现了幻觉，就幻读了。

（4）事务更新丢失：当两个或多个事务操作同一行数据，然后基于最初选定的值更新该行时，由于每个事务都不知道其它事务的存在，就会发生丢失更新问题——最后一个事务的更新的数据覆盖了之前事务所做的更新。也就是之前所有事务对该行数据的更新都丢失了。

2、常见解决方案

要解决以上问题，业界大致提供了两种方案

（1）基于多版本：一种是通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（ＭultiVersion Concurrency Control，简称MVCC或MCC），也常称为多版本数据库。

（2）基于锁：另一种是在事务读取数据前，对其加锁，阻止其他事务对数据进行修改，直到该事务完成。

3、事物所能解决的问题

事务就是基于多版本来解决问题的。以上前三种问题通过设置事务不同的隔离级别可以解决

英文	中文	脏读	不可重复读	幻读
Read uncommitted	读未提交	Y	Y	Y
Read committed	读已经提交	N	Y	Y
Repeatable read	可重复读	N	N	Y
Serializable	可串行化	N	N	N

Read uncommitted：读未提交，事务隔离级别中最低的，花费代价最低，一般情况下不用，该级别不能解决以上三种问题。

Read committed：读提交，一个事物读取数据并进提交了更改操作，该级别解决了脏读问题。事务A已经更改了第N行数据，并且提交了更改，另一个事务B此时再读取第N行数据，事物A已经提交了，不能再回滚，事务B读到的数据不会是脏数据。（也是oracle的默认隔离级别）

Repeatable read：重复读，顾名思义，一个事务内可以对同一数据进行重复读取，该级别解决了不可重复读问题。事物A读取该数据时候就对该数据进行锁定操作，不允许其他事物进行更改操作，直到事物A完成对该数据的读取或更新操作，才允许其它事务对该数据进行操作。（也是mysql的默认隔离级别）

Serializable：可串行化，是事务隔离中最高的，花费代价最高，性能低，一般很少使用，在该级别下，事务顺序执行，可以避免脏读、不可重复读，幻读问题。

前三种本质上都是数据一致性问题，需要事务来解决。而数据更新丢失是我们实际业务需要操作问题，不是数据库的问题，是事务所不能解决的，这时候就需要借助加锁解决，锁机制便应运而生。

二、mysql的锁机制

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

mysql提供了三种锁机制：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

行锁实现方式

InnoDB行锁是通过索引上的索引项来实现的，这一点ＭySQL与Oracle不同，Oracle是通过在数据中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁！在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

三级封锁协议解决事务更新丢失问题

一级封锁协议：事务T中如果对数据R有写操作，必须在这个事务中对R的第一次读操作前对它加X锁，直到事务结束才释放。事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）。
二级封锁协议：一级封锁协议加上事务T在读取数据R之前必须先对其加S锁，读完后方可释放S锁。
三级封锁协议：一级封锁协议加上事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。

　　可见，三级锁操作一个比一个严厉（满足高级锁则一定满足低级锁）。但是效率非常低的，建议一般少用的好。

三、基于InnoDB的锁

InnoDB是mysql中使用最多的引擎，支持事物，它也提供了行锁。

共享锁和排它锁

InnoDB实现标准行级锁定，其中有两种类型的锁，

共享（S）锁：共享（S）锁允许持有锁的事务去读取一行数据，防止其它持有X锁的事务读取同一行。
排它（X）锁：排它（X）锁允许持有锁的事务更新或删除一行数据，防止持有S和X锁的事务获取同一行数据。

如果事务T1在R行上持有共享(S)锁，则事务T2请求对行R上进行加锁进行如下处理：

T2可以即时对R行加S锁成功。最终T1和T2都保持R上的S锁。排它锁反之，T1持有了R行上的X锁时候，T2便不能对R行加X锁，直到T1被释放R行的X锁。

意向锁

InnoDB还支持多粒度锁，即允许行锁和表锁共存。例如，一个语句，比如LOCK TABLES...WRITE在指定表上采用排它锁（X锁）。为了实现多粒度级别的锁，InnoDB使用意向锁。意图锁是表级锁，用于指示事务稍后对于表中的行需要哪种类型的锁（共享或独占）。有两种类型的意向锁，它们协议如下：

意向共享锁（IS）：指示事务打算在表中的单个行上设置共享锁之前，必须首先获取表上的IS锁或更强的锁。
意向排他锁（IX）：指示事务打算在表中的单个行上设置排他锁，它必须首先获取表上的IX锁。

X（排它锁）、IX（意向排它锁）、S（共享锁）、IS（意向共享锁）表示四中锁模式，下表出示了表级锁类型的两两组合下的兼容性。

	`X`	`IX`	`S`	`IS`
`X`	Conflict	Conflict	Conflict	Conflict
`IX`	Conflict	Compatible	Conflict	Compatible
`S`	Conflict	Conflict	Compatible	Compatible
`IS`	Conflict	Compatible	Compatible	Compatible

如果一个锁与现有锁兼容，则向请求事务授予锁，但若与现有锁冲突，则不授予锁，直到现有锁被释放。若一个锁请求与现有锁冲突，并且因为冲突导致的死锁而无法被授予，则会发生错误。

意向锁只对表请求有效（例如，LOCK TABLES...WRITE）。意向锁的主要目的是显示哪些操作正在锁定一行，或者将要锁定表中的一行。意向锁可以说是自动加的，无须人工干预。事务可以通过以下语句显示给操作的数据加共享锁或排它锁

意向共享锁（IS）SELECT ... LOCK IN SHARE MODE

意向排他锁（IX）SELECT ... FOR UPDATE

意向锁定的事务数据在SHOW ENGINE INNODB STATUS and InnoDB monitor输出中类似于以下内容：

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

记录锁

记录锁是索引记录上的锁。例如，select c1 from t WHERE c1=10 FOR UPDATE；防止任何其他事务插入、更新或删除t.c1值为10的行。

记录锁总是锁定索引记录，即使定义表时没有索引。对于这种情况，InnoDB创建一个隐藏的聚合索引，并使用这个索引进行记录锁定。 See Section 14.6.2.1, “Clustered and Secondary Indexes”.

记录锁定的事务数据类似于SHOW ENGINE INNODB STATUS和InnoDB监视器输出中的以下内容：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 10078 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 ;; 1: len 6; hex 00000000274f; asc 'O;; 2: len 7; hex b60000019d0110; asc ;;

间隙锁

间隙锁是一种作用于索引记录之间的间隙的锁，或者是对第一个索引记录之前或最后一个索引记录之后的间隙的锁。

例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;

为了防止其他事务将值15插入到列t.c1的10-20之间，无论列中是否已存在10-20之间的值，10-20之间的值都会被锁定。

间隙可能跨越单个索引值、多个索引值，甚至可能是空的。

间隙锁是性能和并发之间折衷的一部分，用于某些事务隔离级别，而不是其他级别。

对于使用唯一索引锁定行来查询唯一行的语句，不需要间隙锁定。（这不包括搜索条件仅包括多列唯一索引的一些列的情况；在这种情况下，会发生间隙锁定。）例如，如果id列具有唯一索引，则下面的语句仅对具有id值100的行使用索引记录锁定，其他会话是否插入并不重要在前一间隙中的行：SELECT * FROM child WHERE id = 100;如果id没有索引或具有非唯一索引，则语句会锁定前面的间隙。
这里还值得注意的是，不同的事务可以在间隙上持有冲突的锁。例如，事务A可以在间隙上持有共享间隙锁（间隙S-lock），而事务B在同一间隙上持有排它间隙锁（间隙X-lock）。允许冲突间隙锁的原因是，如果从索引中清除记录，则必须合并由不同事务保存在记录上的间隙锁。

InnoDB中的间隙锁是“完全禁止的”，这意味着它唯一的目的是防止其他事务插入到间隙中。间隙锁可以共存。一个事务获取的间隙锁不会阻止另一个事务获取同一间隙上的间隙锁。共享和排它间隙锁之间没有区别。它们不会相互冲突，并且执行相同的功能。

可以显式禁用间隙锁定。如果将事务隔离级别更改为READ COMMITTED或启用innodb_locks_.fe_for_binlog系统变量（现在已弃用）。在这种情况下，间隙锁不用于搜索和索引扫描，仅用于外键约束检查和重复键检查

使用READ COMMITTED隔离级别或启用innodb_locks_.fe_for_binlog还有其他效果。在MySQL评估了WHERE条件之后，不匹配行的记录锁会被释放。对于 FOR UPDATE语句，InnoDB进行“半一致”读取，这样向MySQL返回最新的提交版本，以便MySQL可以确定行是否与UPDATE的WHERE条件匹配。

（Next-Key锁）

Next-Key锁是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合。

InnoDB以这样的方式执行行级锁定，即在搜索或扫描表索引时，它在遇到的索引记录上设置共享或排他锁。因此，行级锁实际上是索引记录锁。索引记录上的Next-Key锁也会影响索引记录之前的“间隙”。也就是说，Next-Key锁是索引记录锁加上索引记录之前的间隙上的间隙锁。如果一个会话在索引中的记录R上具有共享或排他锁，则另一个会话不能按照索引顺序在R之前的间隙中插入新的索引记录。

假设索引包含值10、11、13和20。该索引的可能的Next-Key锁包括下列间隔，其中圆括号表示排除该间隔端点，方括号表示包含该端点：

(negative infinity, 10]

(10, 11]

(11, 13]

(13, 20]

(20, positive infinity)

对于最后一个间隔，Next-Key锁将间隙锁定在索引中最大值之上，而“上限”伪记录的值高于索引中实际存在的任何值。上限不是真正的索引记录，因此，实际上，这个Next-Key锁只锁定最大索引值之后的间隙。

默认情况下，InnoDB在REPEATABLE READ事务隔离级别中操作。在这种情况下，InnoDB使用Next-Key锁进行搜索和索引扫描，这可防止幻读。

Next-Key的事务数据类似于SHOW ENGINE INNODB STATUS和InnoDB监视器输出中的以下内容

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 10080 lock_mode X 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 3; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 6; hex 00000000274f; asc 'O;; 2: len 7; hex b60000019d0110; asc ;;

Insert Intention Locks

插入意向锁是在行插入之前由INSERT操作设置的一种间隙锁。这个锁发出了插入的意向，使得如果插入到同一索引间隙中的多个事务没有插入到该间隙中的相同位置，则它们不需要彼此等待。假设有值为4和7的索引记录。分别尝试插入值5和6的独立事务在获得插入行的排他锁之前使用插入意向锁锁定4和7之间的间隙，但是不会因为多行不冲突而相互阻塞。

下面的示例演示了在获得插入记录的排他锁之前采用插入意图锁的事务。该示例涉及两个客户端，A和B。

客户机A创建一个包含两个索引记录（90和102）的表，然后启动一个事务，该事务对ID大于100的索引记录设置排它锁。排它锁包括在记录102之前的间隙锁：

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

客户机B开始事务以将记录插入间隙。事务在等待获得独占锁时接受插入意向锁。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

插入意向锁的事务数据在SHOW ENGINE INNODB STATUS和InnoDB监视器输出中类似于以下内容：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

AUTO-INC锁

AUTO-INC锁是一种特殊的表级锁，由事务插入到具有AUTO_INCREMENT列的表中。在最简单的情况下，如果一个事务正在向表中插入值，则任何其他事务必须等待向该表执行它们自己的插入，以便由第一个事务插入的行接收连续的主键值。

表加锁与解锁语法如下：

LOCK TABLES 
    tbl_name [[AS] alias] lock_type 
    [, tbl_name [[AS] alias] lock_type] ... 
    lock_type: { 
        READ [LOCAL] 
        | [LOW_PRIORITY] WRITE 
    } 
UNLOCK TABLES

通过设置innodb-table-locks=0和autocommit=0, innodb-table-locks的值默认1，不开启表锁，作用域为Global, Session

例如，如果需要写表t1并从表t2读，可以按如下做：

SET AUTOCOMMIT=0;

LOCAK TABLES t1 WRITE, t2 READ, ...;

[do something with tables t1 and here];

COMMIT;

UNLOCK TABLES;

innodb_autoinc_lock_mode配置选项控制用于自动增量锁定的算法。它允许您选择如何在自动递增值的可预测序列与插入操作的最大并发性之间进行权衡。更多参考https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

四、基于ＭyISAM的锁

基于ＭyISAM表级锁有两种模式：表共享锁（Table Read Lock）和表独占写锁（Table Write Lock）。

对MyISAM的读操作，不会阻塞其他用户对同一表请求，但会阻塞对同一表的写请求；
对MyISAM的写操作，则会阻塞其他用户对同一表的读和写操作；
MyISAM表的读操作和写操作之间，以及写操作之间是串行的。

当一个线程获得对一个表的写锁后，只有持有锁线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。（进一步待完成）

参考：

https://blog.csdn.net/JIESA/article/details/51317164

http://www.cnblogs.com/chenqionghe/p/4845693.html

https://blog.csdn.net/gotobat/article/details/80351224

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

Relian哈哈

关注

1
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
mysql的各种锁

一、为什么用锁提到数据库锁，我们往往会产生这样的疑问：数据库既然有了事务，为什么要用锁。只有一种可能，就是事务并不能解决所数据安全问题。下面先看一下事务所能解决的问题。1、事物并发遇到的问题对数据库中数据操作通常遇到以下问题（1）脏读：事务A已经更改了第N行数据，但是没有提交更改，与此同时另一个事务B也读取了第N行数据。但是由于某种原因事务A失败了并进行了回滚操作，此时事务B读取到...
复制链接

扫一扫