MySQL锁

数据库锁，是用来在多线程和高并发场景下，保证数据一致性以及稳定性的一种机制。

MySQL中，不同的存储引擎，其锁粒度与实现原理也不同。InnoDB支持行锁、表锁（InnoDB的两大优势：事务、行锁），MyISAM仅支持表锁，BerkeleyDB支持页级锁。

MySQL各种存储引擎按粒度划分可以分为三种级别的锁定机制：表级锁、行级锁、页级锁；

按锁机制划分可以分为共享锁和排它锁（独占锁）。

一、按粒度划分

 

 1. 表级锁

         表级锁是MySQL最大粒度的锁定机制。表级锁的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取和释放锁的速度很快。由于表级锁会一次性锁定整个表，所以可以很好得避免死锁。

        表级锁的负面影响：锁定资源的争用概率高。

        使用表级锁的存储引擎主要是MyISAM（读/写操作自动加读/写锁），MEMORY等非事务性存储引擎。

    （1）读锁（共享锁）

    读取操作创建的锁。其他用户可以并发读取数据，但是任何事务都不能对数据进行修改（获取数据的排它锁）。直到释放所有的共享锁。（数据被一个用户加了读锁时，其他用户可以继续加读锁）

    ★ 锁表方式

 

SET AUTOCOMMIT=0;        --InnoDB引擎如果不关闭自动提交，MySQL不会加表锁

 

LOCK TABLE table_name READ;        

    （2）写锁（排它锁）

     当一个会话对一个表加了写锁以后，只有持有锁的会话可以对表进行读、写操作，加锁操作，其他会话的读写操作都会等待，直接锁释放或超时。

    当一个会话对一个表加了写锁以后，如果再给该表加读锁，会先释放写锁，再加读锁。

    ★ 锁表：

LOCK TABLE table_name WRITE;

★ 解锁：

方法一：

UNLOCK TABLES;        --会隐式提交事务

方法二：

找锁进程，杀进程

show processlist;
kill id;

 

2. 行级锁（记录锁）

 

        行级锁最大的特点就是锁定对象的颗粒度最小，所以资源争用率最小，能够予以应用程序最大的并发处理能力。

        负面影响：获取锁和释放锁开销较大；容易发生死锁。

        使用行级锁的存储引擎主要是InnoDB。

 

★  加锁方式：

    （1）读锁（共享锁）

SELECT ... LOCK IN SHARE MODE;

    （2）写锁（排它锁）

    如果事务T对数据A加上排它锁，则其他事务不能再对A加任何类型的锁。

SELECT ... FOR UPDATE;

        InnoDB的行锁是通过给索引上的索引项加锁实现（Oracle是在数据块中给相应的数据行加锁实现），所以：

    1）InnoDB只有通过索引条件检索数据，才会使用行锁，否则InnoDB会对聚簇索引加锁，即对整个表加表锁。

加索引以后：

  2）MySQL的行锁是针对索引加锁，不是记录，所以如果使用相同的索引键，即使访问不同行也会出现锁冲突。

 

    （3） 间隙锁

    当我们用等值条件（where .. = ..）或者存在数据的范围条件（between .. and..）检索数据时，InnoDB会给已有的记录的索引项加行锁；对于键值在条件范围内但不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个间隙加锁，这种锁机制就间隙锁。

举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：
Select * from emp where empid > 100 for update;
是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。

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

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！
下面例子中，假如emp表中只有101条记录，其empid的值分别是1,2,……,100,101。
InnoDB存储引擎的间隙锁阻塞例子

（4）Next-Key锁，是一种行锁与间隙锁的组合锁。既能锁住行，也能锁住间隙。采用左开右闭原则。

CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_uid` (`uid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `a`(uid) VALUES (1);
INSERT INTO `a`(uid) VALUES (2);
INSERT INTO `a`(uid) VALUES (3);
INSERT INTO `a`(uid) VALUES (6);
INSERT INTO `a`(uid) VALUES (10);

# T1
START TRANSACTION WITH CONSISTENT SNAPSHOT; //1

SELECT * FROM a WHERE uid = 6 for UPDATE; //2

COMMIT;  //5


# T2
START TRANSACTION WITH CONSISTENT SNAPSHOT;  //3

INSERT INto a(uid) VALUES(11);
INSERT INto a(uid) VALUES(5);  //4
INSERT INto a(uid) VALUES(7);
INSERT INto a(uid) VALUES(8);
INSERT INto a(uid) VALUES(9);

SELECT * FROM a WHERE uid = 6 for UPDATE;

COMMIT;

ROLLBACK;

上面的SQL按照1、2、3、4的顺序执行，在4时会发生阻塞，在5之后才会插入成功。这里所的是uid=6这一行，为什么不能插入5呢，因为这里NK锁锁住了（3,10）这个区间的所有数据。

3. 页级锁

 

        页级锁一次锁定相邻的一组记录，是MySQL比较独特的锁，颗粒度介于行级锁和表级锁，锁开销和并发处理能力也介于行锁和表锁之间。

        使用页级锁的存储引擎是BerkeleyDB。

 

 

二、发生死锁的几种情况及解决方法

    1. 一个用户A访问A表（锁了A表），然后访问B表；同时另一个用户B访问B表（锁住了B表），然后企图访问A表；此时用户A由于用户B已经锁住了B表，必须等B表释放才能继续，同样B在等A释放。此时就产生了死锁。

    解决方案：除了调整代码逻辑无其他方法。不同程序并发存取多个表，尽量约定以相同的顺序访问，可以大大降低死锁几率。

    2.用户A查询一条记录，然后修改该条记录；同时用户B修改该条记录。此时用户A的事务里锁的性质有查询的读锁（共享锁）上升到写锁（排它锁），而用户B里的排他锁由于A有共享锁存在所以必须等A释放共享锁，但是A由于B的排它锁无法上升获得排它锁就不会释放共享锁，于是出现了死锁。（如某用户连续点击修改按钮，导致对一数据连续操作）

    解决方案：

    （1） 使按钮点击后失效；

    （2）使用乐观锁。为表加一个“版本号”字段，读取数据时连同版本号一起读出，写入时+1，对于过期的版本号数据不予处理。

    （3） 使用悲观锁。悲观锁大多依赖数据库锁实现，如oracle的select .. for update，以保证最大程度独占性。随之而来的是数据库的大量开销，特别是长事务，用户在select到update中间，数据始终处于加锁状态。

    （4） 查询不加锁。

    3. 没有条件的update修改了全表数据，导致行级锁上升为表级锁；或者表数据量过大，索引建的不合适，查询结果过多，使得经常发生全表扫描，最终发生阻塞或者死锁。

    解决方案：SQL中不要使用太复杂的关联查询；使用执行计划分析复杂SQL，避免全表扫描。