分类： 数据库

MySQL的InnoDB中主要有8种锁（来自MySQL 8.0）

• Shared and Exclusive Locks 读锁和写锁
• Intention Locks 意向锁
• Record Locks 行锁
• Gap Locks 间隙锁
• Next-Key Locks 行锁+间隙锁
• Insert Intention Locks
• AUTO-INC Locks
• Predicate Locks for Spatial Indexes

未完待续

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

出现问题

这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

在MySQL中，实现了这四种隔离级别，分别有可能产生问题如下所示：

隔离级别	脏读	不可重复读	幻读
Read Uncommitted	✔️	✔️	✔️
Read Committed	❎	✔️	✔️
Repeatable Read	❎	❎	✔️
Serializable	❎	❎	❎

---

MySQL8 之前使用如下命令查看 MySQL 隔离级别：

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

MySQL8 开始，通过如下命令查看 MySQL 默认隔离级别：

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;

 

下面是官方文档的说法：

The following list describes how MySQL supports the different transaction levels. The list goes from the most commonly used level to the least used.

•  REPEATABLE READ This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, theseSELECT statements are consistent also with respect to each other. See Section 15.7.2.3, “Consistent Nonlocking Reads”. For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and DELETEstatements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition.
  • For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.
  • For other search conditions, InnoDB locks the index range scanned, using gap locksor next-key locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks, see Section 15.7.1, “InnoDB Locking”.

•  READ COMMITTED Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. For information about consistent reads, see Section 15.7.2.3, “Consistent Nonlocking Reads”.For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. Gap locking is only used for foreign-key constraint checking and duplicate-key checking.Because gap locking is disabled, phantom row problems may occur, as other sessions can insert new rows into the gaps. For information about phantom rows, seeSection 15.7.4, “Phantom Rows”.Only row-based binary logging is supported with the READ COMMITTED isolation level. If you use READ COMMITTED with binlog_format=MIXED, the server automatically uses row-based logging.Using READ COMMITTED has additional effects:
  • For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. This greatly reduces the probability of deadlocks, but they can still happen.
  • For UPDATE statements, if a row is already locked, InnoDB performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again and this time InnoDBeither locks it or waits for a lock on it.

The READ COMMITTED isolation level can be set at startup or changed at runtime. At runtime, it can be set globally for all sessions, or individually per session.

•  READ UNCOMMITTED SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a dirty read. Otherwise, this isolation level works like READ COMMITTED.

•  SERIALIZABLE This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

1. 原子性（Atomicity）

事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。

回滚可以用回滚日志（Undo Log）来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

2. 一致性（Consistency）

数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对同一个数据的读取结果都是相同的。

3. 隔离性（Isolation）

一个事务所做的修改在最终提交以前，对其它事务是不可见的。

4. 持久性（Durability）

一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

系统发生崩溃可以用重做日志（Redo Log）进行恢复，从而实现持久性。与回滚日志记录数据的逻辑修改不同，重做日志记录的是数据页的物理修改。

Atomicity

The atomicity aspect of the ACID model mainly involves InnoDBtransactions. Related MySQL features include:

• The autocommit setting.
• The COMMIT statement.
• The ROLLBACK statement.

Consistency

The consistency aspect of the ACID model mainly involves internal InnoDBprocessing to protect data from crashes. Related MySQL features include:

• The InnoDB doublewrite buffer. See Section 15.6.4, “Doublewrite Buffer”.
• InnoDB crash recovery. See InnoDB Crash Recovery.

Isolation

The isolation aspect of the ACID model mainly involves InnoDBtransactions, in particular the isolation level that applies to each transaction. Related MySQL features include:

• The autocommit setting.
• Transaction isolation levels and the SET TRANSACTION statement. See Section 15.7.2.1, “Transaction Isolation Levels”.
• The low-level details of InnoDB locking. Details can be viewed in the INFORMATION_SCHEMA tables (see Section 15.15.2, “InnoDB INFORMATION_SCHEMA Transaction and Locking Information”) and Performance Schema data_locks and data_lock_waits tables.

Durability

The durability aspect of the ACID model involves MySQL software features interacting with your particular hardware configuration. Because of the many possibilities depending on the capabilities of your CPU, network, and storage devices, this aspect is the most complicated to provide concrete guidelines for. (And those guidelines might take the form of “buy new hardware”.) Related MySQL features include:

• The InnoDB doublewrite buffer. See Section 15.6.4, “Doublewrite Buffer”.
• The innodb_flush_log_at_trx_commit variable.
• The sync_binlog variable.
• The innodb_file_per_table variable.
• The write buffer in a storage device, such as a disk drive, SSD, or RAID array.
• A battery-backed cache in a storage device.
• The operating system used to run MySQL, in particular its support for the fsync() system call.
• An uninterruptible power supply (UPS) protecting the electrical power to all computer servers and storage devices that run MySQL servers and store MySQL data.
• Your backup strategy, such as frequency and types of backups, and backup retention periods.
• For distributed or hosted data applications, the particular characteristics of the data centers where the hardware for the MySQL servers is located, and network connections between the data centers.