MySql 锁

MySql

1.锁机制

共享锁与排他锁

  • 共享锁（读锁）：其他事务可以读，但不能写。
  • 排他锁（写锁） ：其他事务不能读取，也不能写。

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞

1.1 粒度锁

MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现：

MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）
BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁。
InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令。

但是在有的情况下， 用户需要明确地进行锁表或者进行事务的控制， 以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

不同粒度锁的比较：

  • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
    这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
    表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用。
  • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
    最大程度的支持并发，同时也带来了最大的锁开销。
    在 InnoDB 中，除单个 SQL 组成的事务外，
    锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。
    行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
  • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

2.MyISAM 表锁

2.1 MyISAM表级锁模式：

  • 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
  • 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作；
    MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止。

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。

这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。同时，一些需要长时间运行的查询操作，也会使写线程“饿死” ，应用中应尽量避免出现长时间运行的查询操作（在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解” ，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行）。

2.2 MyISAM加表锁方法：

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

1
加锁：lock table table_nmae read|write;
1
解锁锁：unlock tables;

2.3 查询表级锁争用情况：

可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁的争夺，如果 Table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况：

1
2
3
4
5
6
7
mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 2       |
| Table_locks_waited    | 1       |
+-----------------------+---------+

3.InnoDB行级锁和表级锁

InnoDB锁模式：

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

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

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

  • 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
  • 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

3.1 InnoDB加锁方法：

  • 意向锁:是 InnoDB 自动加的， 不需用户干预。
  • 对于 ‘UPDATE、 DELETE 和 INSERT ‘语句， InnoDB会自动给涉及数据集加排他锁（X)；
    对于普通 SELECT 语句，InnoDB 不会加任何锁；
    事务可以通过以下语句显式给记录集加共享锁或排他锁：
  • 共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
  • 排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁
隐式锁定：

InnoDB在事务执行过程中，使用两阶段锁协议：

随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；

锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

显式锁定 ：
1
2
select ... lock in share mode //共享锁 
select ... for update //排他锁
for update 和 lock in share mode 的区别：

  • 共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

  • 排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

for update 是排他锁（X 锁），一旦一个事务获取了这个锁，其他的事务是没法在这些数据上执行 for update ；lock in share mode是共享锁，多个事务可以同时的对相同数据执行 lock in share mode。

3.2 InnoDB的间隙锁：

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

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

InnoDB使用间隙锁的目的：
  • 防止幻读，以满足相关隔离级别的要求；
  • 满足恢复和复制的需要：

3.3 获取 InnoDB 行锁争用情况：

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

1
2
3
4
5
6
7
8
9
10
11
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     | 
+-------------------------------+-------+ 
5 rows in set (0.01 sec)