Mysql的锁问题

2018-05-22

MySQL的锁机制比较简单，不同的存储引擎支持不同的锁机制。MyISAM和MEMORY存储引擎采用的是表级锁，BDB存储引擎采用的是页面锁，也支持表级锁。InnoDB存储引擎既支持行级锁，也支持表级锁。
MySQL的3种锁的特性归纳如下:

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

一、MyISAM表锁。

MyISAM存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。随着应用对事物完整性和并发性要求的不断提高，MySQL才开始开发基于事务的存储引擎，后开慢慢出现支持页锁的BDB存储引擎和支持行锁的InnoDB存储引擎。

1、查询表级别锁争用情况。

show status like 'table%'可以查询状态。

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 145   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 40    |
| Table_open_cache_misses    | 18    |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+

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

2、MySQL表级锁的锁模式。

MySQL的表级锁有两种模式: 表共享读锁和表独占写锁，兼容模式如下:

锁兼容模式

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

session_1	session_2
获得表actor的WRITE锁定 `mysql > lock table actor write;` `Query OK, 0 rows affected (0.00 sec)`
当前session对锁定表的查询、更新、插入操作都可以执行 `select * from actor where actor_id = 100`	其他session对锁定表的查询被阻塞，需要等待锁被释放
释放锁: `mysql> unlock tables;` `Query OK, 0 rows affected (0.00 sec)`	等待
	session2获得锁，查询返回 `select * from actor where actor_id = 100;`

MyISAM在执行查询语句(SELECT)前，会自动给涉及的所有表加读锁，在执行更新操作(UPDATE, DELETE, INSERT)前，会自动给涉及的表加写锁，这个过程不需要用户干预，因此一般不会使用LOCK TABLE命令给MyISAM表显示加锁。

3、MyISAM读锁的问题

使用如下命令可以给表加读锁:

1	mysql > lock table actor read;

当前session给表加读锁之后会出现如下情况:

当前session可以对加锁的表进行查询操作
当前session不能查询没有锁定的表,如果查询没有锁定的表，会报错 ERROR 1100 (HY000): Table 'staff' was not locked with LOCK TABLES
当前session的插入和更新操作都会提示错误，只有释放锁之后才能更新
当前session不能通过别名访问表，通过别名访问表会报错，如果需要通过别名访问表，需要对别名分别锁定。

其他session操作已经加了读锁的表会出现如下情况:

其他session可以正常查询已经加读锁的表的数据
其他session可以查询会更新未锁定的表
其他session更新已经加读锁的表会等待获得锁

4、并发插入

MyISAM表的读和写是串行的，但是也支持插入和查询操作的并发进行，MyISAM引擎有一个系统变量concurrent_inset，专门用于控制并发插入的行为，其值可以是0、1和2，设置变量方法如下:

1	set global concurrent_inset=1

这三个值对应的意义如下:

当为0时不允许并发插入
当为1时如果MyISAM表中没有空洞，则允许并发插入记录，这也是MySQL的默认设置
当为2时，无论如何都可以并发插入数据

允许并发插入的加锁如下:

1	lock table user read local;

在lock语句的最后加上local关键字，表示允许并发插入。一个session加读锁，另外的session可以并发插入数据。一个session加local的写锁，不会影响其他线程对数据的读操作。

二、InnoDB锁问题。

InnoDB与MyISAM最大不同有两点: 一是支持事务，二是采用了行级锁。

1、事务

事务的ACID属性

原子性：事务是一个院子操作单元，其对数据的修改，要么全部执行，要么全部不执行
一致性：在事务开始和完成时，数据都必须保持一致性状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性，当事务结束时，所有的内部数据（如B树索引和双向链表）也都必须是正确的
隔离性：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作的影响的“独立”环境执行。这意味这事务在处理过程中的中间状态对于外部是不可见的，反之亦然。
持久性：事务完成之后，他对于数据的修改是永久性的，即时出现系统故障也能够保持。

2、并发事务处理带来的问题

并发事务处理能够大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而支持更多的用户，但是并发事务也带来一些问题，主要包括如下问题:

更新丢失: 当两个或者多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生更新丢失的问题–最后的更新覆盖了由其他事务所做的更新。
脏读: 一个事务正在对一条记录做修改，在这个事务完成提交前，这条记录的数据就处于不一致转台，这时，另外一个事务也来读取同一条记录，如果不加控制，第二个事务就会读取到这些未提交的脏数据，并据此做进一步处理，就会产生未提交的数据依赖关系。
不可重复读: 一个事务在读取某些数据之后的某个时间再次读取以前读过的数据，却发现其独处的数据已经改变或者已经被删除了，这种现象就叫做不可重复读
幻读: 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就叫做幻读

3、事务的隔离级别

数据库实现事务隔离级别的方式，基本上可分为以下两种:

一种是在读取数据之前，对其加锁，阻止其他事务对数据进行修改
通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定级别的一致性读取，从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术也叫做数据多版本并发控制，简称MVCC，也常称作多版本数据库

事务的四个隔离级别如下:

	读数据一致性	脏读	不可重复读	幻读
未提交读(Read uncommitted)	最低级别，只能保证不会读取到物理上损坏的数据	是	是	是
已提交读(Read committed)	语句级	否	是	是
可重复读(Repeatable read)	事务级	否	否	是
可序列化(Serializable)	最高级别，事务级	否	否	否

4、InnoDB行锁及其加锁方法

InnoDB实现了一下两种类型的行锁

共享锁(S): 允许一个事务去读一行，阻止其他事务活的相同数据集的排他锁
排他锁(X): 允许获得排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁

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

意向共享锁(IS): 事务打算给数据行加共享锁之前必须先取得该表的意向共享锁
意向排他锁(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

共享锁例子:

1	SELECT actor_id, first_name, last_name FROM actor WHERE actor_id = 178 LOCK IN SHARE MODE

当一个session获得记录集的共享锁之后，具有如下特性:

其他session仍然可以查询记录，并也可以对该记录加share mode的共享锁。
其他session对共享锁锁定的记录进行更新操作，需要等待锁
只有获得锁之后才能更新数据

5、InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。InnoDB行锁分为3中情形:

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