Mysql锁机制

概述

---

1.Mysql锁介绍

2.MyISAM锁

3.InnoDB锁

Mysql锁介绍

Mysql不同的存储引擎支持不同的锁机制，根据不同的引擎划分，mysql锁机制分类如下：

	行锁	表锁
MyISAM	不支持	支持
InnoDB	支持	支持

很明显可以看到，MyISAM不支持行锁，而InnoDB支持行锁，可能还有人说BDB引擎的页锁呢？ 这里不做介绍，BDB已经被InnoDB取代了。

• 表锁：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
• 行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

所以MyISAM不会出现死锁的情况。而InnoDB则会出现，后面会介绍到。 

MyISAM锁

基本介绍

我们可以在使用数据库时，可以通过命令来查看数据库表锁的竞争情况

MariaDB [test]> show status like 'table%'; 

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

| Variable_name         | Value     |

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

| Table_locks_immediate | 128366245 |

| Table_locks_waited    | 1670689   |

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

2 rows in set (0.00 sec)

 

Table_locks_waited 的值越高说明存在较严重的表级锁竞争情况；

 

MyISAM的表级锁有两种模式，表共享读锁和表独占写锁。锁模式的兼容性如下所示

 

请求锁模式        是否兼容 当前所模式	共享读锁	独占写锁
共享读锁	Y	N
独占写锁	N	N

 

 

 

可见MyISAM的特点：

1.对于表的读操作，不会阻塞其他用户对同一表的读操作；但是会阻塞其他用户对同一表的写操作；

2.对于表的写操作，会阻塞其他用户对同一表的读和写操作，只有当写操作释放锁后，其他用户才能继续执行。

下面来看一下实际例子

实际演示

这里来模拟MyISAM引擎加锁的操作，事实上，在sql执行update，insert，delete操作时，会自动给表加上独占写锁，在select前会自动给加上表读锁。

假设有两个session分布操作mysql，如下（假设MyISAM引擎类型的表已经创建好）

session1	session2
1.获取locktest表的写锁 MariaDB [test]> lock table locktest write;
2.对当前表进行select，或者update都是可以成功的 MariaDB [test]> select * from locktest; +----+------+------+-----------------+ | id | name | age  | address         | +----+------+------+-----------------+ |  1 | aaa  |    1 | aaa@aaa.com | |  2 | bbb  |    2 | bbb@bbb.com |   MariaDB [test]> update locktest set address='aa@aa.com' where id=1; Query OK, 1 row affected (0.04 sec) Rows matched: 1  Changed: 1  Warnings: 0	在session2中对locktest表进行select，发现一直卡住了，没有执行 MariaDB [test]> select * from locktest; 等待
3.释放写锁 MariaDB [test]> unlock tables ; Query OK, 0 rows affected (0.00 sec)	等待的语句开始执行 MariaDB [test]> select * from locktest; +----+------+------+-----------+ | id | name | age  | address   | +----+------+------+-----------+ |  1 | aaa  |    1 | aa@aa.com | |  2 | bbb  |    2 | bb@bb.com | +----+------+------+-----------+ 2 rows in set (3 min 53.21 sec)
再次测试下读锁，获取locktest的读锁 MariaDB [test]> lock table locktest read; Query OK, 0 rows affected (0.01 sec)
执行select语句 MariaDB [test]> select * from locktest; +----+------+------+-----------+ | id | name | age  | address   | +----+------+------+-----------+ |  1 | aaa  |    1 | aa@aa.com | |  2 | bbb  |    2 | bb@bb.com |     这时候如果在session1中执行update操作，会怎么样？ MariaDB [test]> update locktest set age=10 where id=1; ERROR 1099 (HY000): Table 'locktest' was locked with a READ lock and can't be updated  提示目前获取的read锁，无法update操作     如果这时候select其他表呢？ MariaDB [test]> select * from locktestInno; ERROR 1100 (HY000): Table 'locktestInno' was not locked with LOCK TABLES 提示其他表没有获取锁。 MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁（Deadlock Free）的原因     如果select 当前表的别名呢？ MariaDB [test]> select * from locktest as a; ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES 也会提示错误，怎么解决呢？ 这时候，我们需要在锁表的时候，也加上别名 MariaDB [test]> lock table locktest as a read; Query OK, 0 rows affected (0.00 sec)	执行update，这时候update会获取write锁，但是session1的read锁还未释放，所以进入等待 MariaDB [test]> update locktest set age=10 where id=1; 等待

 

那是不是MyISAM在写锁的情况下，其他session就一定无法进行插入操作呢？

答案是否定的，接下来介绍一下mysql的并发插入；

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

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

Mysql里面先查一下变量concurrent_insert的value

MariaDB [test]> show variables like '%concurrent_insert%';

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

| Variable_name     | Value |

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

| concurrent_insert | AUTO  |

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

5.5.3以后的value改版了，AUTO代表为1的情况，下图为对应关系：

 

在如下表所示的例子中，session1获得了一个表的READ LOCAL锁，可以对表进行查询操作，但不能对表进行更新操作；其他的线程（session2），虽然不能对表进行删除和更新操作，但却可以对该表进行并发插入操作，这里假设该表中间不存在空洞(没有delete或update操作)。

 

session1	session2
1.获取locktest的read锁,必须加上local关键字，否则不生效，local关键字就是满足并发插入的条件下，允许其他进程进行表尾插入记录。 MariaDB [test]> lock table freeblock read local; Query OK, 0 rows affected (0.00 sec)
2.当前session进入插入或者update均失败，原因是只获取到了读锁   MariaDB [test]> insert into freeblock(count)values(2); ERROR 1099 (HY000): Table 'freeblock' was locked with a READ lock and can't be updated MariaDB [test]> update freeblock set count=4 where id=1; ERROR 1099 (HY000): Table 'freeblock' was locked with a READ lock and can't be updated	这是如果在session2执行insert操作，则可以成功 insert into freeblock(count)values(3);
3.当前session查看session2插入的记录,发现查不到 MariaDB [test]> select * from freeblock where count=3; Empty set (0.00 sec)	session2进行update操作，进入等待 MariaDB [test]> update freeblock set count=10 where id=1; 等待
4.释放表读锁 MariaDB [test]> unlock tables ; Query OK, 0 rows affected (0.00 sec)	update操作执行成功 MariaDB [test]> update freeblock set count=10 where id=1; Query OK, 1 row affected (23.73 sec) Rows matched: 1  Changed: 1  Warnings: 0
5.再次查看session2插入的记录 MariaDB [test]> select * from freeblock where count=3; +----+-------+ | id | count | +----+-------+ |  3 |     3 | +----+-------+ 1 row in set (0.00 sec)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

MyISAM的锁调度情况：

MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个 MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM 的调度行为。

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

虽然上面3种方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。

 

InnoDB锁

InnoDB比MyISAM的最大的不同点是，1.支持事务；2.使用行锁。

事务的一些特点及面临的一些问题这里不做介绍，类似银行转账的例子，要么成功，要么失败。

InnoDB行锁介绍

我们可以通过show status like 'innodb_row_lock%'命令来查看行锁竞争情况

MariaDB [test]> show status like 'innodb_row_lock%';

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

| Variable_name                 | Value  |

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

| Innodb_row_lock_current_waits | 0      |

| Innodb_row_lock_time          | 257703 |

| Innodb_row_lock_time_avg      | 100    |

| Innodb_row_lock_time_max      | 51834  |

| Innodb_row_lock_waits         | 2556   |

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

Innodb_row_lock_waits和InnoDB_row_lock_time_avg值越大，代表行锁争夺严重；可以通过InnoDB Monitors来观察。

 

InnoDB实现了以下两种行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

• 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
• 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

锁的兼容情况如下：

请求锁模式       是否兼容 当前锁模式	S	X	IS	IX
S	Y	N	Y	N
X	N	N	N	N
IS	Y	N	Y	Y
IX	N	N	Y	Y

 

 

 

 

 

InnoDB示例

之前有讲到InnoDB会出现死锁的情况，这里我们会设计一个这样的场景出来，有个事务使用select share mode获取共享锁，这时事务还想update数据，就直接update了，这样有可能会造成死锁的产生，具体来看一下步骤：

session1	session2
1.设置autocommit=0，即不立即提交； MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
2.通过select share mode来获取共享锁 MariaDB [test]> select * from locktestInno lock in share mode; +----+------+------+------------+ | id | name | age  | address    | +----+------+------+------------+ |  1 | aaa  |   20 | aa@aa.com  | |  2 | bbb  |    2 | abb@aa.com | +----+------+------+------------+	执行select操作 MariaDB [test]> select * from locktestInno; +----+------+------+------------+ | id | name | age  | address    | +----+------+------+------------+ |  1 | aaa  |   20 | aa@aa.com  | |  2 | bbb  |    2 | abb@aa.com |   通过share mode获取锁，也能够获取成功 MariaDB [test]> select * from locktestInno lock in share mode; +----+------+------+------------+ | id | name | age  | address    | +----+------+------+------------+ |  1 | aaa  |   20 | aa@aa.com  | |  2 | bbb  |    2 | abb@aa.com | +----+------+------+------------+
3.执行update操作 MariaDB [test]> update locktestInno set age=10 where id=1; 等待	session2页执行update操作，这时就会出现死锁 MariaDB [test]> update locktestInno set age=10 where id=1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
4.session2出现死锁后，会释放锁，这边的等待的更新操作就会执行成功了 MariaDB [test]> update locktestInno set age=10 where id=1; Query OK, 1 row affected (14.18 sec) Rows matched: 1  Changed: 1  Warnings: 0
通过commit提交，锁会自动释放 MariaDB [test]> commit; Query OK, 0 rows affected (0.17 sec)	commit提交

可以上到上述出现死锁的原因，获取共享锁后进行更新操作，所以更新操作要通过for update来获取排它锁，那现在来看下排它锁

session1	session2
1.设置autocommit=0 MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec)	设置autocommit=0
2.获取排它锁 MariaDB [test]> select * from locktestInno for update; +----+------+------+------------+ | id | name | age  | address    | +----+------+------+------------+ |  1 | aaa  |   10 | aa@aa.com  | |  2 | bbb  |    2 | abb@aa.com | +----+------+------+------------+	session2可以进行select操作，但是无法获取共享锁（InnoDb select操作是不加锁的） MariaDB [test]> select * from locktestInno; +----+------+------+------------+ | id | name | age  | address    | +----+------+------+------------+ |  1 | aaa  |   10 | aa@aa.com  | |  2 | bbb  |    2 | abb@aa.com | +----+------+------+------------+ 2 rows in set (0.00 sec)   MariaDB [test]> select * from locktestInno lock in share mode; 等待
3.进行update操作,并释放锁 MariaDB [test]> update locktestInno set age=30 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0
4.释放锁 MariaDB [test]> commit; Query OK, 0 rows affected (0.00 sec)	commit后，其他session能够获取到锁 MariaDB [test]> select * from locktestInno lock in share mode; +----+------+------+------------+ | id | name | age  | address    | +----+------+------+------------+ |  1 | aaa  |   30 | aa@aa.com  | |  2 | bbb  |    2 | abb@aa.com | +----+------+------+------------+

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

重要说明，InnoDB的行锁是针对索引的，而不是记录。

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

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。下面通过一些实际例子来加以说明。

下面通过例子来说明：

1.在where语句中不使用索引字段，session1获取其中一行的排它锁后，session2再获取另一行的排它锁，按照理论来讲，session2会进入等待；

session1	session2
1.同样设置autocommit=0，获取排它锁，根据条件age来获取 MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> select * from locktestInno where age=2 for update; +----+------+------+------------+ | id | name | age  | address    | +----+------+------+------------+ |  2 | bbb  |    2 | abb@aa.com | +----+------+------+------------+	session2获取排它锁时，遇到等待，奇怪，InnoDB不是行锁吗？为什么取不同的行还是要阻塞呢？ 可见是使用了表锁 MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> select * from locktestInno where age=10 for update;  等待
2.将age字段添加索引 MariaDB [test]> alter table locktestInno add index(age); Query OK, 0 rows affected (0.25 sec) Records: 0  Duplicates: 0  Warnings: 0
3.再次操作步骤1	能够执行了,所以这里使用了行锁，可以验证之前的说法，行锁是基于索引的， MariaDB [test]> select * from locktestInno where age=30 for update; +----+------+------+-----------+ | id | name | age  | address   | +----+------+------+-----------+ |  1 | aaa  |   30 | aa@aa.com | +----+------+------+-----------+ 1 row in set (0.00 sec)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

无论是主键索引还是普通索引InnoDB均使用的行锁进行加锁，可以看下table的索引，自己感兴趣可以试下，是否会使用表锁。

MariaDB [test]> show index in locktestInno;

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

| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| locktestInno |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |

| locktestInno |          1 | name     |            1 | name        | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |

| locktestInno |          1 | age      |            1 | age         | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |

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

3 rows in set (0.00 sec)

 

即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

比如对于下面的例子就没有使用到索引，table中的name字段为varchar类型，且建了索引。但是在搜索的时候，如果使用的不是varchar类型进行比较，则会进行类型转换，而使用全表扫描。

MariaDB [test]> select * from courseInno;

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

| id | name | score |

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

|  1 | 1    |    10 |

|  2 | 2    |    20 |

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

2 rows in set (0.00 sec)

 

MariaDB [test]> alter table courseInno add index(name);

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

MariaDB [test]> explain select * from courseInno where name=1;

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

| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |

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

|    1 | SIMPLE      | courseInno | ALL  | name          | NULL | NULL    | NULL |    2 | Using where |

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

1 row in set (0.00 sec)

 

MariaDB [test]> explain select * from courseInno where name='1';

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

| id   | select_type | table      | type | possible_keys | key  | key_len | ref   | rows | Extra                 |

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

|    1 | SIMPLE      | courseInno | ref  | name          | name | 138     | const |    1 | Using index condition |

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

1 row in set (0.00 sec)

 

由于行锁是针对索引的，所以对于两个session，如果使用了相同的索引，其中一个也会进行阻塞，如下

 

session1	session2
1.设置autocommit=0，获取排它锁 MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> select * from locktestInno where age=2 and address like 'aa%' for update; +----+------+------+-----------+ | id | name | age  | address   | +----+------+------+-----------+ |  1 | aaa  |    2 | aa@aa.com | +----+------+------+-----------+ 1 row in set (0.35 sec)	session2也获取排它锁，这时进入等待，原因是使用了相同的索引age=2； MariaDB [test]> select * from locktestInno where age=2 and address like 'abb%' for update;  等待

 

 

 

 

 

 

 

 

间隙锁（Next-Key锁）

什么是间隙锁？当我们用范围条件而不是用相等条件去检索数据时，并请求共享或排它锁时，InnoDB会给符合条件的已有数据记录的索引项加上锁，对于键值在条件范围内但不存在的记录，叫做间隙；InnoDB也会对这种间隙加锁，这种锁机制就是间隙锁。

举例来说，假如一个表locktestInno里面的id字段的值分别1，2，3，。。。101，那sql语句

select * from locktestInno where id >100;

是一个范围检索，这时InnoDB不仅会对id为101的记录加锁，也会对id大于101的记录（这些记录不存在）加锁

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其它事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！

下面来看下间隙锁的例子：

session1	session2
1.首先看下事务隔离性 MariaDB [test]>  select @@tx_isolation; +-----------------+ | @@tx_isolation  | +-----------------+ | REPEATABLE-READ | +-----------------+ 事务隔离级别描述：       READ UNCOMMITTED：幻读，不可重复读和脏读均允许；       READ COMMITTED：允许幻读和不可重复读，但不允许脏读；       REPEATABLE READ：允许幻读，但不允许不可重复读和脏读；       SERIALIZABLE:幻读，不可重复读和脏读都不允许；        ORACLE默认的是 READ COMMITTED。       MYSQL默认的是 REPEATABLE READ。
2.对不存在的记录加for update的锁 MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> select * from locktestInno where id=3 for update; Empty set (0.00 sec)	对id为3执行insert操作 MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> insert into locktestInno(name,age,address) values('ccc',3,'cc@cc.com'); 等待
3.回滚 MariaDB [test]> rollback; Query OK, 0 rows affected (0.00 sec)	插入成功,可以看到mysql事务默认是运行幻读的，但是通过间隙锁是会阻塞的 MariaDB [test]> insert into locktestInno(name,age,address) values('ccc',3,'cc@cc.com'); Query OK, 1 row affected (39.60 sec)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

什么时候使用表锁，什么时候使用行锁？

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。

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

当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。

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

（1）使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的，仅当autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。有关死锁，下一小节还会继续讨论。

（2）在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。正确的方式见如下语句：

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

 

关于死锁

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

session1	session2
1.获取locktestInno的排它锁 MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> select * from locktestInno where id=1 for update; +----+------+------+-----------+ | id | name | age  | address   | +----+------+------+-----------+ |  1 | aaa  |    2 | aa@aa.com | +----+------+------+-----------+ 1 row in set (0.12 sec)	获取courseInno的排它锁 MariaDB [test]> select * from courseInno where id=1 for update; +----+------+-------+ | id | name | score | +----+------+-------+ |  1 | 1    |    10 | +----+------+-------+
2.获取courseInno的排它锁 MariaDB [test]> select * from courseInno where id=1 for update; 等待   在session2发生死锁后，继续执行     MariaDB [test]> select * from courseInno where id=1 for update;   +----+------+-------+   | id | name | score |   +----+------+-------+   |  1 | 1    |    10 |   +----+------+-------+	获取locktestInno 的排它锁，出现死锁 MariaDB [test]> select * from locktestInno where id=1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

 

 

 

 

 

 

 

 

 

 

 

这个死锁很明显，在步骤2中，session1获取courseInno的排它锁，但是排它锁还在session2中没有释放，进入等待；这是session2又来获取locktestInno的排它锁，而session1之前已经进入阻塞状态了，所以session2出现死锁。这里死锁就是因为资源的相互等待。

发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。

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

通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句，绝大部分死锁都可以避免。下面就通过实例来介绍几种避免死锁的常用方法。

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

 

2）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。比如下面的例子，处理数据时，没有对条件的字段id进行排序，一会获取1，一会获取3，这样很容易造成死锁；

session1	session2
1.获取ID为1的数据的for update锁 MariaDB [test]> select * from locktestInno where id=1 for update; +----+------+------+-----------+ | id | name | age  | address   | +----+------+------+-----------+ |  1 | aaa  |    2 | aa@aa.com | +----+------+------+-----------+ 1 row in set (0.00 sec)	获取id为3的for update 锁 MariaDB [test]> select * from locktestInno where id=3 for update; +----+------+------+-----------+ | id | name | age  | address   | +----+------+------+-----------+ |  3 | ccc  |    3 | cc@cc.com | +----+------+------+-----------+
2.获取id为3的 for update锁 MariaDB [test]> select * from locktestInno where id=3 for update;	获取id为1的for update锁，出现死锁的情况 MariaDB [test]> select * from locktestInno where id=1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

 

 

 

 

 

 

 

 

 

（3）在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。

（4）在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题，如下所示。

session1	session2
1.对不存在的记录获取for update锁 MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> select @@tx_isolation; +-----------------+ | @@tx_isolation  | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec)   MariaDB [test]> select * from locktestInno where id=4 for update; Empty set (0.43 sec)	也获取id为4的for update锁，能够获取成功 MariaDB [test]> select * from locktestInno where id=4 for update; Empty set (0.00 sec)
2.执行insert操作 MariaDB [test]> insert into locktestInno(name,age,address)values('dd',4,'dd@dd.com'); 等待	执行insert操作，出现死锁 MariaDB [test]> insert into locktestInno(name,age,address)values('dd',4,'dd@dd.com'); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
session2死锁退出后，insert成功 MariaDB [test]> insert into locktestInno(name,age,address)values('dd',4,'dd@dd.com'); Query OK, 1 row affected (6.68 sec)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5）当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。

例子不举了，事务这块不太熟。。。