MySQL的锁机制

1. MySQL锁的基本介绍

锁是计算机系统协调多个进程或线程并发访问某一资源的机制。在数据库中，除了传统的计算机资源，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，所对数据库而言显得尤其重要，也更加复杂。
相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和Memory存储引擎支持的是表级锁；InnoDB存储引擎支持行级锁，也支持表级锁，默认情况下使用行级锁。
表级锁：开销小，加锁快；不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度也最低。
行级锁：开销小，加锁慢；会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
从上述特点可见，很难笼统地说哪种锁更好，只能就具体的应用特点来说哪种锁更合适。仅从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用，而行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理(OLTP)系统。

2. MyISAM表锁

MySQLDE表级锁有两种模式：表共享读锁（table read lock）和表独占写锁（table write lock）。
对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM的读操作与写操作之间，以及写操作之间是串行的。

CREATE TABLE testlock (
	id int(11) NOT NULL AUTO_INCREMENT,
	NAME varchar(20) DEFAULT NULL,
	PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf-8
lock table testlockwrite
INSERT INTO testlock(id, name) VALUES (1, 'a');
INSERT INTO testlock (id, name) VALUES (2, 'b');
INSERT INTO testlock(id, name) VALUES (3, 'c');
INSERT INTO testlock (id, name) VALUES (4, 'd');
SELECT * FROM testlock;
unlock tables;
lock table testlock read;
select * from testlock;
update testlock set name = 'abc' where id = 2;
> ERROR 1099 (HY000): Table `testlock` was locked with a READ lock and can't be updated
select * from othertable;
> ERROR 1100 (HY000): Table `othertable` was not locked with LOCK TABLES
unlock tables;

MyISAM写锁阻塞：
当一个线程获得一个表的写锁之后，只有持有锁的线程可以对表进行更新操作。其他的线程的读写操作都会被阻塞，知道锁释放为止。
MyISAM的读阻塞写：
当一个session使用lock table给表加读锁，这个session可以锁定表中的记录，但更新和访问其他表都会提示错误，同时，另一个session可以查询表中的记录，但更新会阻塞。
注意：MyISAM在执行查询语句之前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动 给涉及的表加写锁，这个过程并不需要用户干预，因此一般不需要用户使用命令来显式加锁。

MyISAM的并发插入问题
MyISAM表的读和写是串行的，这是就总体而言的，在一定条件下，MyISAM也支持查询和插入操作的并发执行。MyISAM存储引擎有一个系统变量 concurrent_insert 专门用来控制并发插入的行为，值分别为 0、1、2。
  1. 当值为0时，不允许发生插入
  2. 当值为1时，如果表中间没有被删除的行，MyISAM允许一个进程读表的同时，另一个进程从表尾插入记录
  3. 当值为2时，无论表中有没有空洞，都允许在表尾并发插入记录

InnoDB锁

1. 事务及其ACID属性

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

  • 原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改要么全部执行，要么全部不执行
  • 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致
  • 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”运行环境
  • 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使系统出现故障也能够保持

2. 并发事务带来的问题

相对于船型处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多用户的并发操作，但与此同时会带来一个问题
脏读：一个事务正在对一条记录做修改，在这个事务提交之前，这条记录的数据就处于不一致状态；这时另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些脏数据，并据此做了进一步处理，就会产生未提交的数据依赖关系。这种现象成为脏读。
不可重复读：一个事务在读取某些数据已经发生了改变，或某些记录已经被删除，这种现象叫做不可重复读
幻读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象称为幻读
上述出现的问题都是数据库读一致性的问题，可以通过事务的隔离机制来进行保证。
数据库的事务隔离越严格，并发副作用就越小，但付出的代价也越大，因为事务隔离本质上就是使事务在一定程度上串行化，需要根据具体的业务需求来决定使用哪种隔离级别。

脏读

不可重复读

幻读

read uncommitted

√

√

√

read commited

√

√

repeatable read

√

serializable

可以通过检查 InnoDB_row_lock 状态来分析系统上的锁的争夺情况：

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 18702 |
| Innodb_row_lock_time_avg      | 18702 |
| Innodb_row_time_max           | 18702 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+

如果发现锁争用比较严重，如 InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg 的值比较高

3. InnoDB的行锁模式及加锁方法

  共享锁(s): 又称读锁，允许一个事务去读一行，阻止其他事务获取相同数据集的排他锁，若事务T对数据对象A加了S锁，则事务T可以读A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这个保证了其他事务可以读A，但在T释放A上的S锁之前，不能对A做任何修改
  排他锁(x): 又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁
  mysql InnoDB引擎默认的修改数据语句：update，delete，insert 都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁，如果加排他锁可以使用 select … for update，加共享锁可以使用 select … lock in share mode 的语句。所以加过排他锁的数据行，在其他事务中是不能修改数据的，也不能通过 for update 和 lock in share mode 锁的方式查询数据，但可以通过 select from 来查询数据，因为普通的查询没有任何锁机制。

InnoDB行锁实现方式

  InnoDB行锁是通过给索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着，只有通过索引条件检索数据，InnoDB 才使用行级锁，否则使用表级锁。

  1. 在不通过索引条件查询的时候，InnoDB使用的是表锁而不是行锁
CREATE table tab(id int, name varchar(10)) ENGINE=INNODB
INSERT INTO tab VALUES(1, '1'), (2, '2'), (3, '3'), (4, '4')

-- session1
SELECT * FROM tab WHERE id = 1 FOR UPDATE;
-- session2
SELECT * FROM tab WHERE id = 2 FOR UPDATE;
ALTER TABLE tab ADD INDEX id(id);

总结

对于MyISAM的表锁，主要讨论了以下几点：
（1）共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的。
（2）在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。
（3）MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
（4）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

对于InnoDB表，主要讨论了以下几项内容：
（1）InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
（2）在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

  • 尽量使用较低的隔离级别； 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
  • 选择合理的事务大小，小事务发生锁冲突的几率也更小；
  • 给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
  • 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
  • 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响； 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
  • 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。