mysql 锁表 innodb行锁实现方式

获取当前数据库隔离级别select @@tx_isolation;

MySQL锁概述

  • 表级锁（table-level locking）

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

  • 页面锁（page-level locking）

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

  • 行级锁（row-level locking）

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

查询表级锁争用情况

mysql> show status like 'table%'; 如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

MySQL表级锁的锁模式

  • 表共享读锁（Table Read Lock）
  • 表独占写锁（Table Write Lock）

** MySQL中的表锁兼容性 **

当前锁模式 是否兼容 请求锁模式

None

读锁

写锁

读锁

是

是

否

写锁

是

否

否

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

MyISAM存储引擎的写阻塞读例子

session_1

session_2

获得表film_text的WRITE锁定mysql> lock table film_text write;

当前session对锁定表的查询、更新、插入操作都可以执行：mysql> select film_id,title from film_text where film_id = 1001;mysql> insert into film_text (film_id,title) values(1003,'Test');mysql> update film_text set title = 'Test' where film_id = 1001;

其他session对锁定表的查询被阻塞，需要等待锁被释放：mysql> select film_id,title from film_text where film_id = 1001;等待

释放锁：mysql> unlock tables;

等待

||Session2获得锁，查询返回：`mysql> select film_id,title from film_text where film_id = 1001;`|

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

MyISAM存储引擎的读阻塞写例子

session_1

session_2

获得表film_text的READ锁定mysql> lock table film_text read;

当前session可以查询该表记录mysql> select film_id,title from film_text where film_id = 1001;

其他session也可以查询该表的记录mysql> select film_id,title from film_text where film_id = 1001;

当前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;mysql> update film set title = 'Test' where film_id = 1001;

当前session中插入或者更新锁定的表都会提示错误：mysql> insert into film_text (film_id,title) values(1002,'Test');mysql> update film_text set title = 'Test' where film_id = 1001;

其他session更新锁定表会等待获得锁：mysql> update film_text set title = 'Test' where film_id = 1001;等待

释放锁mysql> unlock tables;

等待

Session获得锁，更新操作完成：mysql> update film_text set title = 'Test' where film_id = 1001;

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

  • （1）对actor表获得读锁：
mysql> lock table actor read;
  • （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;
  • （3）需要对别名分别锁定：

mysql> lock table actor as a read,actor as b read;

  • （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;

并发插入（Concurrent Inserts）

MyISAM表也支持查询和插入操作的并发进行

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

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

MyISAM存储引擎的读写（INSERT）并发例子

session_1

session_2

获得表film_text的READ LOCAL锁定mysql> lock table film_text read local;

当前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 updated;mysql> 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');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;

释放锁：mysql> unlock tables;

等待

当前session解锁后可以获得其他session插入的记录：mysql> select film_id,title from film_text where film_id = 1002;

Session2获得锁，更新操作完成：mysql> update film_text set title = 'Update Test' where film_id = 1001;

通过定期在系统空闲时段执行 OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞。

MyISAM的锁调度

写进程先获得锁

即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM 的调度行为。

  • 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
  • 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
  • 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级

MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。
上面已经讨论了写优先调度机制带来的问题和解决办法。这里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”！因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语句来解决问题，因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。

背景知识： 以下为innodb详解

  1. 事务（Transaction）及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性

  1. 原子性（Atomicity）

事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

  1. 一致性（Consistent）

在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。

  1. 隔离性（Isolation）

数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

  1. 持久性（Durable）

事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

  1. 并发事务处理带来的问题
  1. 更新丢失（Lost Update）

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。

2.脏读（Dirty Reads）

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。

  1. 不可重复读（Non-Repeatable Reads）

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。

  1. 幻读（Phantom Reads）

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

3．事务隔离级别

“更新丢失”通常是应该完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
两种方法解决：1 数据表中加versIon字段更新时拿之前取出的version版本对比 2 用时间戳做比较跟version类似

“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

  • 一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
  • 另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)（把最先的数据存起来事务过来查都是最早的数据），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。

4种隔离级别比较

读数据一致性及允许的并发副作用隔离级别

读数据一致性

脏读

不可重复读

幻读

未提交读（Read uncommitted）

最低级别，只能保证不读取物理上损坏的数据

是

是

是

已提交度（Read committed）

语句级

否

是

是

可重复读（Repeatable read）

事务级

否

否

是

可序列化（Serializable）

最高级别，事务级

否

否

否

Oracle 只提供Read committed和Serializable两个标准隔离级别，另外还提供自己定义的Read only隔离级别

SQL Server　　除支持上述ISO/ANSI SQL92定义的4个隔离级别外，还支持一个叫做“快照”的隔离级别，但严格来说它是一个用MVCC实现的Serializable隔离级别

获取InnoDB行锁争用情况

  • 检查InnoDB_row_lock状态变量来分析系统上的行锁
mysql> show status like 'innodb_row_lock%';

如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

  • 方法如下：
mysql> CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
mysql> show engine innodb status\G; //查看最近死锁状态 命令行执行
mysql> DROP TABLE innodb_monitor;

InnoDB的行锁模式及加锁方法

  1. 共享锁（S）

允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

  1. 排他锁（X)

允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

  1. 意向共享锁（IS）

事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

  1. 意向排他锁（IX）

事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

InnoDB行锁模式兼容性列表

↓当前锁模式 / 是否兼容 / 请求锁模式→

X

IX

S

IS

X

冲突

冲突

冲突

冲突

IX

冲突

兼容

冲突

兼容

S

冲突

冲突

兼容

兼容

IS

冲突

兼容

兼容

兼容

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

意向锁是InnoDB自动加的，不需用户干预。

UPDATE、DELETE、INSERT InnoDB会自动给涉及数据集加排他锁（X)；

SELECT InnoDB不会加任何锁；

  • 共享锁（S）SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT... FOR UPDATE方式获得排他锁。

  • 排他锁（X）SELECT * FROM table_name WHERE ... FOR UPDATE

InnoDB存储引擎的共享锁例子

session_1

session_2

mysql> set autocommit = 0;

mysql> set autocommit = 0;

select actor_id,first_name,last_name from actor where actor_id = 178;

select actor_id,first_name,last_name from actor where actor_id = 178;

mysql> select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;

mysql> select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;

当前session对锁定的记录进行更新操作，等待锁：mysql> update actor set last_name = 'MONROE T' where actor_id = 178; 等待

其他session也对该记录进行更新操作，则会导致死锁退出：mysql> update actor set last_name = 'MONROE T' where actor_id = 178; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

获得锁后，可以成功更新：mysql> update actor set last_name = 'MONROE T' where actor_id = 178;

InnoDB存储引擎的排他锁例子

session_1

session_2

mysql> set autocommit = 0;

mysql> set autocommit = 0;

当前session对actor_id=178的记录加for update的排它锁：mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update;

其他session可以查询该记录，但是不能对该记录加共享锁，会等待获得锁：mysql> select actor_id,first_name,last_name from actor where actor_id = 178; mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update; 等待

当前session可以对锁定的记录进行更新操作，更新后释放锁：mysql> update actor set last_name = 'MONROE T' where actor_id = 178;

其他session获得锁，得到其他session提交的记录：mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update;

InnoDB行锁实现方式

msyql InnoDB行锁是通过给索引上的索引项加锁来实现的

InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

Oracle 通过在数据块中对相应数据行加锁来实现的

InnoDB存储引擎的表在不使用索引时使用表锁例子

  • 创建数据库 mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb;

session_1

session_2

mysql> set autocommit = 0;

mysql> set autocommit = 0;

mysql> select * from tab_no_index where id = 1 for update;

mysql> select * from tab_no_index where id = 2 for update;等待

当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

InnoDB存储引擎的表在使用索引时使用行锁例子

- 添加索引 `mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb;`

session_1

session_2

mysql> set autocommit = 0;

mysql> set autocommit = 0;

mysql> select * from tab_with_index where id = 1 for update;

mysql> select * from tab_with_index where id = 2 for update;

由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的

InnoDB存储引擎使用相同索引键的阻塞例子

  • tab_with_index的id字段有索引，name字段没有索引
    mysql> alter table tab_with_index drop index name;

session_1

session_2

mysql> set autocommit = 0;

mysql> set autocommit = 0;

mysql> select * from tab_with_index where id = 1 and name = '1' for update;

虽然session_2访问的是和session_1不同的记录，但是因为使用了相同的索引，所以需要等待锁：mysql> select * from tab_with_index where id = 1 and name = '4' for update;等待

原来InnoDB不是只在最终要更新的行上加锁,而是在被扫描过的所有行上加锁.也就是说,

他们官方文档说明如下
A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.

InnoDB存储引擎的表使用不同索引的阻塞例子

session_1

session_2

mysql> set autocommit = 0;

mysql> set autocommit = 0;

mysql> select * from tab_with_index where id = 1 for update;

Session_2使用name的索引访问记录，因为记录没有被索引，所以可以获得锁：mysql> select * from tab_with_index where name = '2' for update;

由于访问的记录已经被session_1锁定，所以等待获得锁。：mysql> select * from tab_with_index where name = '4' for update;

因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引

name字段是varchar类型的，如果where条件中不是和varchar类型进行比较，则会对name进行类型转换，而执行的全表扫描。
mysql> alter table tab_no_index add index name(name);mysql> explain select * from tab_with_index where name = '1' \G

间隙锁（Next-Key锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

InnoDB存储引擎的间隙锁阻塞例子

session_1

session_2

mysql> select @@tx_isolation; mysql> set autocommit = 0;

mysql> select @@tx_isolation; mysql> set autocommit = 0;

当前session对不存在的记录加for update的锁：mysql> select * from emp where empid = 102 for update;

这时，如果其他session插入empid为102的记录（注意：这条记录并不存在），也会出现锁等待：mysql>insert into emp(empid,...) values(102,...);阻塞等待

Session_1 执行rollback：mysql> rollback;

由于其他session_1回退后释放了Next-Key锁，当前session可以获得锁并成功插入记录：mysql>insert into emp(empid,...) values(102,...);

恢复和复制的需要，对InnoDB锁机制的影响

MySQL的恢复机制

  • 一是MySQL的恢复是SQL语句级的，也就是重新执行BINLOG中的SQL语句。这与Oracle数据库不同，Oracle是基于数据库文件块的。
  • 二是MySQL的Binlog是按照事务提交的先后顺序记录的，恢复也是按这个顺序进行的。这点也与Oralce不同，Oracle是按照系统更新号

CTAS操作给原表加锁例子

session_1

session_2

mysql> set autocommit = 0; mysql> select * from target_tab;mysql> select * from source_tab where name = '1';

mysql> set autocommit = 0;mysql> select * from target_tab;mysql> select * from source_tab where name = '1';

mysql> insert into target_tab select d1,name from source_tab where name = '1';

mysql> update source_tab set name = '1' where name = '8';等待

commit;

返回结果commit;

CTAS操作不给原表加锁带来的安全问题例子

session_1

session_2

mysql> set autocommit = 0;mysql>set innodb_locks_unsafe_for_binlog='on'; mysql> select * from target_tab;mysql> select * from source_tab where name = '1';

mysql> set autocommit = 0;mysql> select * from target_tab;mysql> select * from source_tab where name = '1';

mysql> insert into target_tab select d1,name from source_tab where name = '1';

session_1未提交，可以对session_1的select的记录进行更新操作。mysql> update source_tab set name = '8' where name = '1';mysql> select * from source_tab where name = '8';

更新操作先提交mysql> commit;

插入操作后提交mysql> commit;

此时查看数据，target_tab中可以插入source_tab更新前的结果，这符合应用逻辑：mysql> select * from source_tab where name = '8';mysql> select * from target_tab;

mysql> select * from tt1 where name = '1';mysql> select * from source_tab where name = '8';mysql> select * from target_tab;

从上可见，设置系统变量innodb_locks_unsafe_for_binlog的值为“on”后，InnoDB不再对source_tab加锁，结果也符合应用逻辑，但是如果分析BINLOG的内容：

可以发现，在BINLOG中，更新操作的位置在INSERT...SELECT之前，如果使用这个BINLOG进行数据库恢复，恢复的结果与实际的应用逻辑不符；如果进行复制，就会导致主从数据库不一致！

通过上面的例子，我们就不难理解为什么MySQL在处理“Insert into target_tab select * from source_tab where ...”和“create table new_tab ...select ... From source_tab where ...”时要给source_tab加锁，而不是使用对并发影响最小的多版本数据来实现一致性读。还要特别说明的是，如果上述语句的SELECT是范围条件，InnoDB还会给源表加间隙锁（Next-Lock）

因此，INSERT...SELECT...和 CREATE TABLE...SELECT...语句，可能会阻止对源表的并发更新，造成对源表锁的等待。如果查询比较复杂的话，会造成严重的性能问题，我们在应用中应尽量避免使用。实际上，MySQL将这种SQL叫作不确定（non-deterministic）的SQL，不推荐使用。

  • 如果应用中一定要用这种SQL来实现业务逻辑，又不希望对源表的并发更新产生影响，可以采取以下两种措施：
  • 一是采取上面示例中的做法，将innodb_locks_unsafe_for_binlog的值设置为“on”，强制MySQL使用多版本数据一致性读。但付出的代价是可能无法用binlog正确地恢复或复制数据，因此，不推荐使用这种方式。
  • 二是通过使用“select * from source_tab ... Into outfile”和“load data infile ...”语句组合来间接实现，采用这种方式MySQL不会给source_tab加锁。

InnoDB在不同隔离级别下的一致性读及锁的差异

InnoDB存储引擎中不同SQL在不同隔离级别下锁比较

隔离级别 一致性读和锁 SQL

Read Uncommited

Read Commited

Repeatable Read

Serializable

SQL

条件

select

相等

None locks

Consisten read/None lock

Consisten read/None lock

Share locks

select

范围

None locks

Consisten read/None lock

Consisten read/None lock

Share Next-Key

update

相等

exclusive locks

exclusive locks

exclusive locks

Exclusive locks

update

范围

exclusive next-key

exclusive next-key

exclusive next-key

exclusive next-key

Insert

N/A

exclusive locks

exclusive locks

exclusive locks

exclusive locks

replace

无键冲突

exclusive locks

exclusive locks

exclusive locks

exclusive locks

replace

键冲突

exclusive next-key

exclusive next-key

exclusive next-key

exclusive next-key

delete

相等

exclusive locks

exclusive locks

exclusive locks

exclusive locks

delete

范围

exclusive next-key

exclusive next-key

exclusive next-key

exclusive next-key

Select ... from ... Lock in share mode

相等

Share locks

Share locks

Share locks

Share locks

Select ... from ... Lock in share mode

范围

Share locks

Share locks

Share Next-Key

Share Next-Key

Select * from ... For update

相等

exclusive locks

exclusive locks

exclusive locks

exclusive locks

Select * from ... For update

范围

exclusive locks

Share locks

exclusive next-key

exclusive next-key

Insert into ... Select ...（指源表锁）

innodb_locks_unsafe_for_binlog=off

Share Next-Key

Share Next-Key

Share Next-Key

Share Next-Key

Insert into ... Select ...（指源表锁）

innodb_locks_unsafe_for_binlog=on

None locks

Consisten read/None lock

Consisten read/None lock

Share Next-Key

create table ... Select ...（指源表锁）

innodb_locks_unsafe_for_binlog=off

Share Next-Key

Share Next-Key

Share Next-Key

Share Next-Key

create table ... Select ...（指源表锁）

innodb_locks_unsafe_for_binlog=on

None locks

Consisten read/None lock

Consisten read/None lock

Share Next-Key

对于许多SQL，隔离级别越高，InnoDB给记录集加的锁就越严格（尤其是使用范围条件的时候），产生锁冲突的可能性也就越高，从而对并发性事务处理性能的影响也就越大。因此，我们在应用中，应该尽量使用较低的隔离级别，以减少锁争用的机率。实际上，通过优化事务逻辑，大部分应用使用Read Commited隔离级别就足够了。对于一些确实需要更高隔离级别的事务，可以通过在程序中执行SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ或SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE动态改变隔离级别的方式满足需求。

什么时候使用表锁

  • 第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
  • 第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

在InnoDB下，使用表锁要注意以下两点。

  • 使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的，仅当autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。有关死锁，下一小节还会继续讨论。
  • 在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。正确的方式见如下语句：
    例如，如果需要写表t1并从表t读，可以按如下做：
SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

关于死锁

MyISAM表锁是deadlock free的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了在InnoDB中发生死锁是可能的

InnoDB存储引擎中的死锁例子

session_1

session_2

mysql> set autocommit = 0;mysql> select * from table_1 where where id=1 for update; ... 做一些其他处理...

mysql> set autocommit = 0;mysql> select * from table_2 where id=1 for update; ...

select * from table_2 where id =1 for update;

因session_2已取得排他锁，等待

mysql> select * from table_1 where where id=1 for update;死锁

发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

避免死锁的常用方法

  1. 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。在下面的例子中，由于两个session访问两个表的顺序不同，发生死锁的机会就非常高！但如果以相同的顺序来访问，死锁就可以避免。

InnoDB存储引擎中表顺序造成的死锁例子

session_1

session_2

mysql> set autocommit=0;

mysql> set autocommit=0;

mysql> select first_name,last_name from actor where actor_id = 1 for update;

mysql> insert into country (country_id,country) values(110,'Test');

mysql> insert into country (country_id,country) values(110,'Test');等待

mysql> select first_name,last_name from actor where actor_id = 1 for update;

mysql> insert into country (country_id,country) values(110,'Test');ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

  1. 在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。

InnoDB存储引擎中表数据操作顺序不一致造成的死锁例子

session_1

session_2

mysql> set autocommit=0;

mysql> set autocommit=0;

mysql> select first_name,last_name from actor where actor_id = 1 for update;

mysql> select first_name,last_name from actor where actor_id = 3 for update;

mysql> select first_name,last_name from actor where actor_id = 3 for update;等待

mysql> select first_name,last_name from actor where actor_id = 1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

mysql> select first_name,last_name from actor where actor_id = 3 for update;

  1. 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
  2. 前面讲过，在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题

InnoDB存储引擎中隔离级别引起的死锁例子1

session_1

session_2

mysql> select @@tx_isolation;'REPEATABLE-READ' mysql> set autocommit=0;

mysql> select @@tx_isolation;'REPEATABLE-READ' mysql> set autocommit=0;

当前session对不存在的记录加for update的锁：mysql> select actor_id,first_name,last_name from actor where actor_id = 201 for update;

其他session也可以对不存在的记录加for update的锁：mysql> select actor_id,first_name,last_name from actor where actor_id = 201 for update;

因为其他session也对该记录加了锁，所以当前的插入会等待：mysql> insert into actor (actor_id , first_name , last_name) values(201,'Lisa','Tom'); 等待

因为其他session已经对记录进行了更新，这时候再插入记录就会提示死锁并退出：mysql> insert into actor (actor_id, first_name , last_name) values(201,'Lisa','Tom'); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

由于其他session已经退出，当前session可以获得锁并成功插入记录：mysql> insert into actor (actor_id , first_name , last_name) values(201,'Lisa','Tom');

  1. 当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。
    对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁

InnoDB存储引擎中隔离级别引起的死锁例子2

session_1

session_2

session_3

mysql> select @@tx_isolation; ‘READ-COMMITTED’mysql> set autocommit=0;

mysql> select @@tx_isolation; ‘READ-COMMITTED’mysql> set autocommit=0;

mysql> select @@tx_isolation; ‘READ-COMMITTED’mysql> set autocommit=0;

Session_1获得for update的共享锁：mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update;

由于记录不存在，session_2也可以获得for update的共享锁：mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update;

Session_1可以成功插入记录：mysql> insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom');

Session_2插入申请等待获得锁：mysql> insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom');等待

Session_1成功提交：mysql> commit;

Session_2获得锁，发现插入记录主键重，这个时候抛出了异常，但是并没有释放共享锁：mysql> insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom');ERROR 1062 (23000): Duplicate entry '201' for key 'PRIMARY'

Session_3申请获得共享锁，因为session_2已经锁定该记录，所以session_3需要等待：mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update;等待

这个时候，如果session_2直接对记录进行更新操作，则会抛出死锁的异常：mysql> update actor set last_name='Lan' where actor_id = 201;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Session_2释放锁后，session_3获得锁：mysql> select first_name, last_name from actor where actor_id = 201 for update;