mysql 锁_MySQL进阶之MySQL中的锁

概述

锁是计算机协调多个进程或纯线程并发访问某一资源的机制，这些资源包括CPU、内存、I/O等，而在数据库中，数据也是一种供许多用户(进程/线程)共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，数据库的锁便是解决这个问题的重要工具。数据库的事务具有4个属性，即ACID，其中C即是一致性，所以在数据库中，锁和事务总是息息相关的。

MySQL中的锁

在MySQL中，不同的存储引擎，可能支持的锁会有所不同。下面的内容主要围绕使用比较多的InnoDB和MyISAM进行描述。

MySQL中的锁，从粒度即范围划分为3类：全局锁、表级锁、行级锁。实际上，其它的如BDB存储引擎还有页级锁，这里不做介绍。

• 全局锁

锁住整个数据库实例，使得整个库处于只读状态，会阻塞DML和DDL语句，由MySQL的SQL层实现。可以使用以下命令为数据库加全局锁(简称FTWRL)

FLUSH TABLES WITH READ LOCK;

释放全局锁命令如下

UNLOCK TABLES;

此外，在客户端异常断开后，全局锁会自动释放。全局锁的典型使用场景是，做全库逻辑备份(mysqldump)。

• 表级锁

针对当前操作的整张表加锁，实现简单，消耗资源较少，InnoDB和MyISAM均支持表级锁。表级锁分为表锁和MySQL5.5版本引入的元数据锁(MDL)两种。MDL不需要显式使用，在访问或操作一个表的时候会被自动加上。当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。 MDL可以避免读写数据时突然变更了表结构等元数据而导致出现问题。

相关表级锁的一些命令

-- 手动增加表锁(READ为共享读锁，WRITE为独占写锁)LOCK TABLE 表名 READ | WRITE;-- 查看表锁情况SHOW OPEN TABLES;-- 删除表锁UNLOCK TABLES;

表级锁从资源管理角度又分为表共享读锁(共享锁)和表独占写锁(排他锁)两种形式。如果对数据表加了共享读锁，当前会话只能读取加锁的表数据，同时不能增删改(报错)，也不能读取其它表数据；其它的会话则可以读取加锁的表数据，增删改将被阻塞，但可以操作其它表数据。

表共享读锁的演示

打开一个客户端会话1，对tdep表加共享读锁，会话1可以读取tdep表的数据，但不能进行增删改，同时也不能操作其它表。

会话1对tdep表加共享读锁的表现

此时打开另外一个客户端会话2，可以读取tdep表数据，对tdep表进行增删改将被阻塞，但可以操作其它数据表。

其它会话表现

表独占写锁演示

打开一个客户端会话1，对tdep表加独占写锁，会话1可以对tdep表进行增删改查，但依旧不可以操作其它表。

会话1对tdep表加独占写锁的表现

打开另外一个客户端会话2，会话可以操作其它数据表，但对tdep任何的操作包括select查询都会被阻塞。

其它会话的表现

查看表级锁定的争用状态

mysql> SHOW STATUS LIKE  'table_locks%';+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Table_locks_immediate | 99    || Table_locks_waited    | 0     |+-----------------------+-------+

其中table_locks_immediate表示产生表级锁定的次数；table_locks_waited表示出现表级锁定争用而发生等待的次数。

• 行级锁

针对某行数据加锁(也可能锁定行之间的间隙)，是一种排他锁，防止其它事务修改此行。部分存储引擎支持，比如InnoDB，下面主要讲的就是InnoDB的行级锁。

InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过
索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁(自动使用表锁，不需要人员干预)！

InnoDB的行级锁，按照锁定范围来说，分为三种：

记录锁(Record Locks)，锁定索引中一条记录；间隙锁(Gap Locks)，要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值，其用于防止插入或更新间隙内的数据；Next-Key锁，是索引记录上的记录锁和在索引记录之前的间隙锁的组合。

按照功能来说，分为两种：

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

对于INSERT、UPDATE、DELETE语句，InnoDB会自动给涉及数据集加排他锁(X)；对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。

-- 使用LOCK IN SHARE MODE手动添加共享锁(S)SELECT * FROM table_name WHERE condition LOCK IN SHARE MODE;-- 使用FOR UPDATE手动添加排他锁(x)SELECT * FROM table_name WHERE condition FOR UPDATE;

使用COMMIT或ROLLBACK语句释放锁。

查看行级锁争用状态

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

其中第2、3、5项是优化或分析问题的比较常用指标

Innodb_row_lock_current_waits：当前正在等待锁定的数量；

Innodb_row_lock_time：从系统启动到现在锁定总时间长度；

Innodb_row_lock_time_avg：每次等待所花平均时间；

Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；

Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

行锁基本演示

使用2个会话分别连接MySQL服务器，首先使用会话1更新tdep表中id为3的记录，其中id为主键，有索引，所以会自动为该记录加上排他锁(X)，示例中关闭了自动提交，以验证行锁的效果。

会话1的表现

此时会话2可以更新其它记录，也可以读取id为3的记录，但更新id为3的记录将被阻塞

其它会话的表现

行读锁(共享锁)演示

使用会话1为tdep表中id为3的记录加上共享锁，基本上可以做任何的操作

会话1加行读锁的表现

会话2可以访问其它表的数据以及未锁定的其它行，id为3的行可以读取但不能更新

其它会话的表现

行写锁(排他锁)演示

会话1对tdep表中id为3的记录加了排他锁，基本上可以做任何的操作

会话1加行写锁的表现

会话2可以访问tdep表，但不能该记录进行更新或删除，同时也不能对该记录进行加共享锁或排他锁。

其它会话的表现

表级锁和行级锁的比较

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

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

总结

关于InnoDB的行级锁，这里主要介绍了记录锁。InnoDB行级锁是通过给索引实现的，只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将自动使用表锁。

对于INSERT、UPDATE、DELETE语句，InnoDB会自动给涉及数据记录加排他锁(X)，这意味着其它会话不能再对这些记录进行INSERT、UPDATE、DELETE操作以及使用LOCK IN SHARE MODE或FOR UPDATE对涉及的数据记录进行加读锁或写锁，非要操作将被阻塞；

对于普通SELECT语句，InnoDB不会加任何锁，但可以通过LOCK IN SHARE MODE或FOR UPDATE语句显示给记录集加共享锁或排他锁。

一条记录被加了排他锁之后，其它会话不能再对该记录加锁，包括共享锁和排他锁。但如果一条记录加的是共享锁，则其它会话可以对该记录加共享锁，但不能加排他锁。即一条记录可以同时被多个会话加共享锁，但只能被一个会话加排他锁。

不同于表的独占写锁，行的排他锁可以允许其它会话读取行记录，而表的独占写锁则不允许其它会话读取表中的记录。即行级锁作用是限制了其它会话对加锁的记录进行增删改，但不限制读取。