mysql更新时怎么加锁 mysql更新是否加锁

mysql读数据时怎么加写锁

加锁情况与死锁原因分析

成都创新互联坚持“要么做到，要么别承诺”的工作理念，服务领域包括：成都网站设计、成都做网站、外贸网站建设、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的册亨网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！

为方便大家复现，完整表结构和数据如下：

CREATE TABLE `t3` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` int(11) DEFAULT NULL,

PRIMARY KEY (`c1`),

UNIQUE KEY `c2` (`c2`)

) ENGINE=InnoDB

insert into t3 values(1,1),(15,15),(20,20);

在 session1 执行 commit 的瞬间，我们会看到 session2、session3 的其中一个报死锁。这个死锁是这样产生的：

1. session1 执行 delete  会在唯一索引 c2 的 c2 = 15 这一记录上加 X lock（也就是在MySQL 内部观测到的：X Lock but not gap）；

2. session2 和 session3 在执行 insert 的时候，由于唯一约束检测发生唯一冲突，会加 S Next-Key Lock，即对 (1,15] 这个区间加锁包括间隙，并且被 seesion1 的 X Lock 阻塞，进入等待；

3. session1 在执行 commit 后，会释放 X Lock，session2 和 session3 都获得 S Next-Key Lock；

4. session2 和 session3 继续执行插入操作，这个时候 INSERT INTENTION LOCK（插入意向锁）出现了，并且由于插入意向锁会被 gap 锁阻塞，所以 session2 和 session3 互相等待，造成死锁。

死锁日志如下：

请点击输入图片描述

INSERT INTENTION LOCK

在之前的死锁分析第四点，如果不分析插入意向锁，也是会造成死锁的，因为插入最终还是要对记录加 X Lock 的，session2 和 session3 还是会互相阻塞互相等待。

但是插入意向锁是客观存在的，我们可以在官方手册中查到，不可忽略：

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

插入意向锁其实是一种特殊的 gap lock，但是它不会阻塞其他锁。假设存在值为 4 和 7 的索引记录，尝试插入值 5 和 6 的两个事务在获取插入行上的排它锁之前使用插入意向锁锁定间隙，即在（4，7）上加 gap lock，但是这两个事务不会互相冲突等待。

当插入一条记录时，会去检查当前插入位置的下一条记录上是否存在锁对象，如果下一条记录上存在锁对象，就需要判断该锁对象是否锁住了 gap。如果 gap 被锁住了，则插入意向锁与之冲突，进入等待状态（插入意向锁之间并不互斥）。总结一下这把锁的属性：

1. 它不会阻塞其他任何锁；

2. 它本身仅会被 gap lock 阻塞。

在学习 MySQL 过程中，一般只有在它被阻塞的时候才能观察到，所以这也是它常常被忽略的原因吧...

GAP LOCK

在此例中，另外一个重要的点就是 gap lock，通常情况下我们说到 gap lock 都只会联想到 REPEATABLE-READ 隔离级别利用其解决幻读。但实际上在 READ-COMMITTED 隔离级别，也会存在 gap lock ，只发生在：唯一约束检查到有唯一冲突的时候，会加 S Next-key Lock，即对记录以及与和上一条记录之间的间隙加共享锁。

通过下面这个例子就能验证：

请点击输入图片描述

这里 session1 插入数据遇到唯一冲突，虽然报错，但是对 (15,20] 加的 S Next-Key Lock 并不会马上释放，所以 session2 被阻塞。另外一种情况就是本文开始的例子，当 session2 插入遇到唯一冲突但是因为被 X Lock 阻塞，并不会立刻报错 “Duplicate key”，但是依然要等待获取 S Next-Key Lock 。

有个困惑很久的疑问：出现唯一冲突需要加 S Next-Key Lock 是事实，但是加锁的意义是什么？还是说是通过 S Next-Key Lock 来实现的唯一约束检查，但是这样意味着在插入没有遇到唯一冲突的时候，这个锁会立刻释放，这不符合二阶段锁原则。这点希望能与大家一起讨论得到好的解释。

如果是在 REPEATABLE-READ，除以上所说的唯一约束冲突外，gap lock 的存在是这样的：

普通索引（非唯一索引）的S/X Lock，都带 gap 属性，会锁住记录以及前1条记录到后1条记录的左闭右开区间，比如有[4,6,8]记录，delete 6，则会锁住[4,8）整个区间。

对于 gap lock，相信 DBA 们的心情是一样一样的，所以我的建议是：

1. 在绝大部分的业务场景下，都可以把 MySQL 的隔离界别设置为 READ-COMMITTED；

2. 在业务方便控制字段值唯一的情况下，尽量减少表中唯一索引的数量。

锁冲突矩阵

前面我们说的 GAP LOCK 其实是锁的属性，另外我们知道 InnoDB 常规锁模式有：S 和 X，即共享锁和排他锁。锁模式和锁属性是可以随意组合的，组合之后的冲突矩阵如下，这对我们分析死锁很有帮助：

请点击输入图片描述

mysql 的锁以及间隙锁

mysql 为并发事务同时对一条记录进行读写时，提出了两种解决方案：

1）使用 mvcc 的方法，实现多事务的并发读写，但是这种读只是“快照读”，一般读的是历史版本数据，还有一种是“当前读”，一般加锁实现“当前读”，或者 insert、update、delete 也是当前读。

2）使用加锁的方法，锁分为共享锁（读锁），排他锁（写锁）

快照读：就是select

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。

mysql 在 RR 级别怎么处理幻读的呢？一般来说，RR 级别通过 mvcc 机制，保证读到低于后面事务的数据。但是 select for update 不会触发 mvcc，它是当前读。如果后面事务插入数据并提交，那么在 RR 级别就会读到插入的数据。所以，mysql 使用 行锁 + gap 锁（简称 next-key 锁）来防止当前读的时候插入。

Gap Lock在InnoDB的唯一作用就是防止其他事务的插入操作，以此防止幻读的发生。

Innodb自动使用间隙锁的条件：

mysql update是加什么锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。本章我们着重讨论MySQL锁机制的特点，常见的锁问题，以及解决MySQL锁问题的一些方法或建议。

MySQL锁概述

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MySQL这3种锁的特性可大致归纳如下。

开销、加锁速度、死锁、粒度、并发性能

l 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

l 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

l 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。这一点在本书的“开发篇”介绍表类型的选择时，也曾提到过。下面几节我们重点介绍MySQL表锁和 InnoDB行锁的问题，由于BDB已经被InnoDB取代，即将成为历史，在此就不做进一步的讨论了。

MyISAM表锁

MyISAM存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。随着应用对事务完整性和并发性要求的不断提高，MySQL才开始开发基于事务的存储引擎，后来慢慢出现了支持页锁的BDB存储引擎和支持行锁的InnoDB存储引擎（实际 InnoDB是单独的一个公司，现在已经被Oracle公司收购）。但是MyISAM的表锁依然是使用最为广泛的锁类型。本节将详细介绍MyISAM表锁的使用。

查询表级锁争用情况

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

mysql show status like 'table%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Table_locks_immediate | 2979 |

| Table_locks_waited | 0 |

+-----------------------+-------+

2 rows in set (0.00 sec))

如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

MySQL表级锁的锁模式

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。锁模式的兼容性如表20-1所示。

表20-1 MySQL中的表锁兼容性请求锁模式

是否兼容

当前锁模式

None

读锁

写锁

读锁

是

是

否

写锁

是

否

否

可见，对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！根据如表20-2所示的例子可以知道，当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

表20-2 MyISAM存储引擎的写阻塞读例子session_1

session_2

获得表film_text的WRITE锁定

mysql lock table film_text write;

Query OK, 0 rows affected (0.00 sec)

当前session对锁定表的查询、更新、插入操作都可以执行：

mysql select film_id,title from film_text where film_id = 1001;+---------+-------------+

| film_id | title |

+---------+-------------+

| 1001 | Update Test |

+---------+-------------+

1 row in set (0.00 sec)

mysql insert into film_text (film_id,title) values(1003,'Test');Query OK, 1 row affected (0.00 sec)

mysql update film_text set title = 'Test' where film_id = 1001;Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

其他session对锁定表的查询被阻塞，需要等待锁被释放：

mysql select film_id,title from film_text where film_id = 1001;等待

释放锁：

mysql unlock tables;

Query OK, 0 rows affected (0.00 sec)

等待

Session2获得锁，查询返回：

mysql select film_id,title from film_text where film_id = 1001;+---------+-------+

| film_id | title |

+---------+-------+

| 1001 | Test |

+---------+-------+

1 row in set (57.59 sec)

如何加表锁

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。在本书的示例中，显式加锁基本上都是为了方便而已，并非必须如此。

给MyISAM表显示加锁，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。例如，有一个订单表orders，其中记录有各订单的总金额total，同时还有一个订单明细表order_detail，其中记录有各订单每一产品的金额小计 subtotal，假设我们需要检查这两个表的金额合计是否相符，可能就需要执行如下两条SQL：

Select sum(total) from orders;

Select sum(subtotal) from order_detail;

这时，如果不先给两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中，order_detail表可能已经发生了改变。因此，正确的方法应该是：

Lock tables orders read local, order_detail read local;Select sum(total) from orders;

Select sum(subtotal) from order_detail;

Unlock tables;

要特别说明以下两点内容。

? 上面的例子在LOCK TABLES时加了“local”选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录，有关MyISAM表的并发插入问题，在后面的章节中还会进一步介绍。

? 在用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及到表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此，MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁（Deadlock Free）的原因。

在如表20-3所示的例子中，一个session使用LOCK TABLE命令给表film_text加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误；同时，另外一个session可以查询表中的记录，但更新就会出现锁等待。

表20-3 MyISAM存储引擎的读阻塞写例子session_1

session_2

获得表film_text的READ锁定

mysql lock table film_text read;

Query OK, 0 rows affected (0.00 sec)

当前session可以查询该表记录

mysql select film_id,title from film_text where film_id = 1001;+---------+------------------+

| film_id | title |

+---------+------------------+

| 1001 | ACADEMY DINOSAUR |

+---------+------------------+

1 row in set (0.00 sec)

其他session也可以查询该表的记录

mysql select film_id,title from film_text where film_id = 1001;+---------+------------------+

| film_id | title |

+---------+------------------+

| 1001 | ACADEMY DINOSAUR |

+---------+------------------+

1 row in set (0.00 sec)

当前session不能查询没有锁定的表

mysql select film_id,title from film where film_id = 1001;ERROR 1100 (HY000): Table 'film' was not locked with LOCK TABLES其他session可以查询或者更新未锁定的表

mysql select film_id,title from film where film_id = 1001;+---------+---------------+

| film_id | title |

+---------+---------------+

| 1001 | update record |

+---------+---------------+

1 row in set (0.00 sec)

mysql update film set title = 'Test' where film_id = 1001;Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

当前session中插入或者更新锁定的表都会提示错误：

mysql insert into film_text (film_id,title) values(1002,'Test');ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updatedmysql update film_text set title = 'Test' where film_id = 1001;ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated其他session更新锁定表会等待获得锁：

mysql update film_text set title = 'Test' where film_id = 1001;等待

释放锁

mysql unlock tables;

Query OK, 0 rows affected (0.00 sec)

等待

Session获得锁，更新操作完成：

mysql update film_text set title = 'Test' where film_id = 1001;Query OK, 1 row affected (1 min 0.71 sec)Rows matched: 1 Changed: 1 Warnings: 0

当使用LOCK TABLES时，不仅需要一次锁定用到的所有表，而且，同一个表在SQL语句中出现多少次，就要通过与SQL语句中相同的别名锁定多少次，否则也会出错！举例说明如下。

（1）对actor表获得读锁：

mysql lock table actor read;

Query OK, 0 rows affected (0.00 sec)

（2）但是通过别名访问会提示错误：

mysql select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name b.last_name;ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES（3）需要对别名分别锁定：

mysql lock table actor as a read,actor as b read;Query OK, 0 rows affected (0.00 sec)

（4）按照别名的查询可以正确执行：

mysql select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name b.last_name;+------------+-----------+------------+-----------+| first_name | last_name | first_name | last_name |+------------+-----------+------------+-----------+| Lisa | Tom | LISA | MONROE |+------------+-----------+------------+-----------+1 row in set (0.00 sec)

并发插入（Concurrent Inserts）

上文提到过MyISAM表的读和写是串行的，但这是就总体而言的。在一定条件下，MyISAM表也支持查询和插入操作的并发进行。

MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

l 当concurrent_insert设置为0时，不允许并发插入。

l 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。

l 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

在如表20-4所示的例子中，session_1获得了一个表的READ LOCAL锁，该线程可以对表进行查询操作，但不能对表进行更新操作；其他的线程（session_2），虽然不能对表进行删除和更新操作，但却可以对该表进行并发插入操作，这里假设该表中间不存在空洞。

表20-4 MyISAM存储引擎的读写（INSERT）并发例子session_1

session_2

获得表film_text的READ LOCAL锁定

mysql lock table film_text read local;

Query OK, 0 rows affected (0.00 sec)

当前session不能对锁定表进行更新或者插入操作：

mysql insert into film_text (film_id,title) values(1002,'Test');ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updatedmysql update film_text set title = 'Test' where film_id = 1001;ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated其他session可以进行插入操作，但是更新会等待：

mysql insert into film_text (film_id,title) values(1002,'Test');Query OK, 1 row affected (0.00 sec)

mysql update film_text set title = 'Update Test' where film_id = 1001;等待

当前session不能访问其他session插入的记录：

mysql select film_id,title from film_text where film_id = 1002;Empty set (0.00 sec)

释放锁：

mysql unlock tables;

Query OK, 0 rows affected (0.00 sec)

等待

当前session解锁后可以获得其他session插入的记录：

mysql select film_id,title from film_text where film_id = 1002;+---------+-------+

| film_id | title |

+---------+-------+

| 1002 | Test |

+---------+-------+

1 row in set (0.00 sec)

Session2获得锁，更新操作完成：

mysql update film_text set title = 'Update Test' where film_id = 1001;Query OK, 1 row affected (1 min 17.75 sec)Rows matched: 1 Changed: 1 Warnings: 0

可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表查询和插入的锁争用。例如，将concurrent_insert系统变量设为2，总是允许并发插入；同时，通过定期在系统空闲时段执行 OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞。有关OPTIMIZE TABLE语句的详细介绍，可以参见第18章中“两个简单实用的优化方法”一节的内容。