欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

Mysql锁机制和事务隔离级别

程序员文章站 2024-01-07 11:00:28
MyISAM锁机制： MyISAM只有表锁，分为X和S锁两种(或者叫read lock,write lock)。读加S锁，写加X锁。互相阻塞，因此并发DML性能并不好。 InnoDB锁机制： lock table/tables tab_name read/write; --手动加锁语句：（同样适用于 ......
MyISAM锁机制：
MyISAM只有表锁，分为X和S锁两种(或者叫read lock,write lock)。读加S锁，写加X锁。互相阻塞，因此并发DML性能并不好。
InnoDB锁机制：
lock table/tables tab_name read/write; --手动加锁语句：（同样适用于Myisam）
unlock tables;  --解锁语句
set @@global.innodb_status_output_locks=on; --这样show engine innodb status\G可以显示额外的锁信息，标准情况下只显示锁数目。
一、InnoDB锁模式（行锁）
以下提到的锁都是在用到行锁时才会出现的，如果不使用索引，那么就InnoDB会用表锁而非行锁。
1. 两种行锁：
共享锁（S）：
    允许一个事务去读一行，阻止其他事务获得相同的数据集的排他锁。
排他锁（X）：
    允许获得排他锁的事务更新数据，但是阻止其他事务获得相同数据集的共享锁和排他锁。
2. 两种意向表锁：
在加行锁之前，首先要添加表级意向锁：
意向共享锁（IS）：
    表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：
    类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
3.行锁的范围（可以是S也可以是X锁）：
  • Record Lock：行锁，锁定单个行记录。
  • Gap Lock：间隙锁，锁定一个范围，但不包括记录本身，GAP锁的目的，是为了防止幻读。
  • Next-Key Lock：即行锁+间隙锁。
以上谈了InnoDB提供的行锁相关类型，说到加锁模式就必须在事务隔离级别的概念下进行，否则毫无意义。
默认的事物隔离级别下，InnoDB只有在DML语句和select(for update/lock in share mode)语句用到索引时才会使用行锁，否则使用表锁。
在默认的事物隔离级别下，行锁的使用分2种情况：
  1. 对普通索引的访问使用next-key锁，即锁定行和索引键值上下范围内的间隙，不允许间隙插入，防止幻读。
  2. 对于有唯一属性的索引的单行访问，使用行锁，只锁定要访问的行。例外是如果访问的是不存在的值也会产生gap行锁，范围查询也是使用next-key行锁。
而在READ COMMITTED隔离级别下，delete/update或select(for update/lock in share mode)操作不加gap锁，只会加record锁，因此会有幻读产生。
 
二、各事务隔离级别下InnoDB的加锁模式
关于Myql事务隔离级别参见：
https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html 
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
| REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
1. SERIALIZABLE
这种隔离级别类似REPEATABLE READ，如果autocommit设为disable，那么InnoDB隐式的把所有select语句转化为select lock in share mode，但如果autocommit设为enable，那么select自成事务，其加锁模式也同REPEATABLE READ下的一致。delete/update操作也与REPEATABLE READ下的加锁模式一致。
2. REPEATABLE READ（默认）
这种隔离级别下数据库采用事务级MVCC快照机制，select操作读取事务级的快照，不加行锁及表锁，同时也避免了脏读、不可重复读和幻读。对于delete/update或select(for update/lock in share mode)操作如果使用索引，那么如上所说分两种情况添加行锁。
此种隔离级别类似于SQL Server的snapshot隔离级别。ISO标准定义中此隔离级别下是无法避免幻读的，但是各数据库厂商通过MVCC机制直接使select语句不对资源加锁，从而避免了幻读。
3. READ COMMITTED
这种隔离级别下数据库采用语句级的MVCC快照机制，来避免脏读和不可重复读，但是不可避免幻读。select语句对数据表只加元数据锁，不加行锁及表锁。delete/update或select(for update/lock in share mode)操作如果使用索引，那么不加gap锁，只会加record锁，因此会有幻读产生。
Ps:注意在此隔离级别下binlog_format必须使用row模式！
4. READ UNCOMMITTED
这种隔离级别下数据库不采用MVCC快照机制，会脏读，select语句不加锁。对于delete/update或select(for update/lock in share mode)操作，加锁机制与
READ COMMITTED一致。
更详细的各类SQL语句加锁说明参见：
关于mysql事务级一致性读和语句级一致性读的说明参考：
 
三、锁和事务隔离级别总结：
可以看到Innodb通过mvcc机制在REPEATABLE READ和 READ COMMITTED隔离级别下实现了select不阻塞写。但是在REPEATABLE READ隔离级别下由于实现了事务级的一致性读，因此也顺带避免了不可重复读和幻读。这一点其实是与其他数据库的隔离级别与脏读、不可重复读、幻读的关系表有差异的地方。例如SQL Server中隔离级别与异常读的对应关系如下：
Mysql锁机制和事务隔离级别
 Mysql锁机制和事务隔离级别
 
四、Insert语句
以上所有的锁模式和事务隔离级别的讲解都忽略了insert语句，那么insert语句在四大隔离级别下的加锁形式是什么样的呢？官网提供的相关页面如下：

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.
Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.
Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

首先我们要明确一个概念，即InnoDB表必定有主键，无论是显式的还是隐式的系统自定义主键，这意味着insert必定用到主键索引，然后再来看以上内容：
这段的核心意思就是说insert语句只会对加一个index record lock，不会加gap锁，因此insert不同的主键不会互相阻塞。由于insert语句是DML语句，因此可以认为在所有隔离级别下其加锁模式都是一样的，即：先加表级IX锁，再加针对具体插入行的X模式的record lock。

上一篇：

下一篇：

推荐阅读