MySQL的锁

MyISAM和MEMORY存储引擎采用的是表级锁( table- level locking）；
BDB存储引擎 采用的是页面锁（page- level locking）， 但也支持表级锁；
InnoDB存储引擎既支持行级锁（row- level locking）， 也支持表级锁， 但默认情况下是采用行 级 锁。

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

锁名称	开销	加锁时间	死锁	粒度	锁冲突的概率	并发度
表级锁	小	快	不会	大	最高	最低
页面锁	中	中	会	中	中	中
行级锁	大	慢	会	最小	最低	最高

1、MyISAM 总是 一次 获得 SQL 语句 所需 要的 全部 锁。 这也 正是 MyISAM 表 不会 出现 死锁（ Deadlock Free） 的 原因。

从对数据操作的类型（读\写）分

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

查看表的锁情况

mysql> show open tables;
+--------------------------------+-------------------------------+--------+-------------+
| Database                       | Table                         | In_use | Name_locked 
+--------------------------------+-------------------------------+--------+-----------+
| sdly_saas-synthetic-system     | irri_channel_mouth_relation   |      0 |           0 |
| performance_schema             | session_status                |      0 |           0 |

分析表锁情况：

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 1051  |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 7     |
| Table_open_cache_misses    | 0     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.00 sec)

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1
Table_locks_waited：出现表锁锁定竞争而等待的次数（不能立即获取锁的次数，没等待一次加1），该值越高说明存在严重的表级锁竞争情况.

关闭自动提交事务：

mysql> set autocommit =0;
Query OK, 0 rows affected (0.00 sec)

修改表的存储引擎：

alter table table_name ENGINE = MyISAM;

1、表锁

1.1、如何加表锁

  MySQL 在引擎之上支持表级别的锁，在数据备份、数据同步等场景下，可以通过表级锁确保获得完整一致的数据。
表读锁情况：加锁的session只能对锁定的表进行查询操作，其他session对锁定表也只能进行查询操作

sessionA	sessionB
LOCK TABLE course READ;
insert Into course (cname,tid) VALUES (“java”,3) （修改/删除/插入该表数据失败） 1099 - Table 'course' was locked with a READ lock and can't be updated·
select * from t1 (查询别的表失败) 1100 - Table 't1' was not locked with LOCK TABLES
-	SELECT * FROM course;(正常返回数据)
-	insert Into course (cname,tid) VALUES (“java”,3);(添加/修改/删除数据阻塞)
UNLOCK TABLES	（阻塞的行为执行）

表写锁情况：加写锁的session对数据库的数据可以进行CRUD，

sessionA	sessionB
LOCK TABLE course WRITE;
-	SELECT * FROM course;(查询/修改/新增/删除阻塞)
UNLOCK TABLES	（阻塞的行为执行）

注意事项：

1、MyISAM在执行查询语句（ SELECT）前，会自动给涉及的所有表加读锁， 在执行更新操作（ UPDATE、DELETE INSERT等）前，会自动给涉及的表加写锁，
2、当使用 LOCK TABLES时，不仅需要一次锁定用到的所有表，而且，同一个表在SQL语句中出现多少次，就要通过与SQL语句中相同的别名锁定多少次，否则也会出错！

mysql> lock table actor read; 
Query OK, 0 rows affected (0. 00 sec) 
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

正确操作：lock table actor as a read, actor as b read;

1.2、MyISAM的并发插入（Concurrent Inserts）

  MyISAM存储引擎有一个系统变量 concurrent_insert可以控制其并发插入的行为：

当 concurrent_ insert =0(never)时，不允许并发插入;
当 concurrent_ insert =1(auto)时， 如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许 一个进程读表的同时，另一个进程从表尾插入记录。 默认设置。
当 concurrent_ insert =2(always)时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

查看以及修改concurrent_insert的值

mysql> SHOW GLOBAL VARIABLES LIKE '%concurrent_insert%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| concurrent_insert | AUTO  |
+-------------------+-------+
1 row in set (0.01 sec)
// 这里写数字和对应的字符value是等价的
mysql> SET GLOBAL concurrent_insert = AUTO;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE '%concurrent_insert%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| concurrent_insert | AUTO  |
+-------------------+-------+
1 row in set (0.00 sec)

sessionA	sessionB
lock table t1 read local;
-	insert into t1 (id,content) values(4,‘11111’); 操作成功

1.3、MyISAM的锁调度

读锁大于写锁，两个session同时获取锁，一个获取读锁，一个获取写锁，一定是写锁获取成功；
在等待获取锁的队列中，即使获取读锁的session先达到队列，写锁后到达队列，写锁也会插入到读锁的前面去。
这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！

2、行锁

2.1、获取InnoDB行锁的争用情况

mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 422383 |
| Innodb_row_lock_time_avg      | 38398  |
| Innodb_row_lock_time_max      | 51006  |
| Innodb_row_lock_waits         | 11     |
+-------------------------------+--------+
5 rows in set (0.00 sec)

锁争用比较严重表现在InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高。

2.2、加锁

InnoDB 为了尽可能支持高并发的写入，支持细粒度的行级锁。行级锁分为如下几种：

共享（S）锁（Shared Lock）：行锁，表示锁定一行数据并读取。
排它（X）锁（Exclusive Lock）：行锁，表示锁定一行数据更新或删除。
意向共享（IS）锁（Intention Shared Lock）：内部使用的表锁，事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他（IX）锁（Intention Exclusive Lock）：内部使用的表锁，事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

锁类别	S	X	IS	IX
S	compatible	conflict	compatible	conflict
X	conflict	conflict	conflict	conflict
IS	compatible	conflict	compatible	compatible
IX	conflict	conflict	compatible	compatible

1、X与所有的锁之间都冲突；
2、S与X、IX之间都冲突；
3、IS只与X之间冲突；
4、IX与意向锁之间都兼容。
如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

加共享锁：

select <column1,column2,column3.....> from table_name where condition lock in share mode;

加排他锁：

select <column1,column2,column3.....> from table_name where condition for update;

sessionA	sessionB
set autocommit = 0; 加共享锁，
-	set autocommit = 0; 获取数据成功，
对锁定行数据进行修改（获取排它锁） 阻塞
-	获取排它锁，报错
-	提交事务
修改成功

sessionA	sessionB
set autocommit = 0; ，	set autocommit = 0;
获取排它锁成功
1.	可以获取该行数据的共享锁，查询成功
2.	尝试获取排它锁，超时等待
修改数据成功，提交事务
3.	查询到sessionA提交的数据

3、Next-key lock

3.1、锁的实现

Record lock：对索引项加锁。
Gap lock：对索引项之间的“间隙”、第一条记录前的“间隙”或最后一条记录后的“间隙”加锁。
Next-key lock：前两种的组合，对记录及其前面的间隙加锁。

注意事项：

1、如果不通过索引条件检索数据，那么InnoDB将对表中所有记录加锁，即表锁！
2、锁是针对索引加的，不是针对记录，即使访问不同的记录，如果是相同的索引键，一样会冲突。
3、当有多个索引时，不同的事务可以使用不同的索引对数据行进行加锁，如果锁定的行相同一样的会出现冲突。
4、在执行计划中，如果MySQL认为全表扫描的效率更高，InnoDB一样的会对所有记录加锁，分析锁冲突时，切记检查SQL的执行计划，检查是否使用了索引。
间歇（GAP）：对于键值在条件范围内但不存在的记录，叫做间隙
Next-key lock：用范围条件而不是相等条件时，请求共享或者排他锁时，InnoDB会对符合条件的已有数据记录索引加锁，包括间隙在内，这种锁机制就是Nest-key Lock
使用Next-key Lock的主要目的是为了防止幻读，以满足隔离要求。同时也是为了满足恢复和复制的需要。

特别注意：InnoDB除了通过范围条件加锁时使用Next-Key锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用Next-Key锁！

sessionA	sessionB
set autocommit = 0	set autocommit = 0
select * from t1 where cid = 5 for update 该条记录不存在
-	insert into t1 (cid ,content) values (1111.‘cname’) 添加一条新的记录，由于next-key lock的原因，阻塞
rollback
-	插入成功

4、使用表锁的情况

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

但是加表锁注意以下事项：

1、仅当autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQLServer也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁;
2、事务结束前不要使用 UNLOCK TABLES解锁，因为该命令会隐含的提交事务，COMMIT、ROLLBACK也不是释放用LOCK TABLES加的表级锁，正确的操作流程：

set autocommit = 0;
lock tables t1 write,t2 read,........;
[do something with tables t1 and t2 here];
commit/rollback;
unlock tables;

5、死锁

5.1、死锁概念

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

sessionA	sessionB
set autocommit = 0	set autocommit = 0
select * from t1 where id = 1 for update	select * from t2 where id = 1 for update
select * from t2 where id = 1 for update 因为此时sessionB已经获取呃该锁，阻塞等待
-	select * from t1 where id = 1 for update 因为此时sessionA已经获取呃该锁，阻塞等待

  发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。

该参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖垮数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

5.2、避免死锁

1. 尽量保证事务访问表的顺序保持一致，即sessionA先访问A表，再访问B表，那么sessionB也必须以相同的顺序进行访问，可大幅度降低死锁；
2. 在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。即要么大家都按升序处理数据，要么大家都按降序进行数据处理；
3. 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁；
4. 遇到主键重复的情况，直接rollback释放获得的排他锁；
5. 尽量使用较低的隔离级别。

遇到死锁可通过命令SHOW INNODB STATUS来确定最后一个死锁产生的原因信息，并进行分析。