一|一 MYSQL简介一MYSQL简介

锁粒度

表级锁：MYSQL中最基本的锁策略，拥有最小的开销，锁定整张表。当客户端对表进行写操作时，将获取写锁，同时阻塞其他的读与写操作；当客户端进行读操作时，将获取读锁，不影响其他的读获取锁。
行级锁：提供最优秀的并发能力，同时花费最大的开销。InnoDB 和 XtraDB 存储引擎实现的是行级锁。

事务 ACID

隔离等级

隔离等级优缺点

Isolation level	Dirty reads possible	Nonrepeatable reads possible	Phantom reads possible	Locking reads
READ UNCOMMITTED	YES	YES	YES	NO
READ COMMITTED	NO	YES	YES	NO
REPEATABLE READ	NO	NO	YES	NO
SERIALIZABLE	NO	NO	NO	YES

MVCC(multiversion concurrency control) 工作原理：保存数据在某个时间点的快照。
InnoDB的MVCC实现在每一行存了两个额外的隐藏值：行的创建事务版本号和行的删除事务版本号。
【一|一 MYSQL简介】例：

SELECT：InnoDB must examine each row to ensure that it meets two criteria:
- InnoDB must find a version of the row that is at least as old as the transaction
  (i.e., its version must be less than or equal to the transaction’s version). This
  ensures that either the row existed before the transaction began, or the transaction
  created or altered the row.
- The row’s deletion version must be undefined or greater than the transaction’s
  version. This ensures that the row wasn’t deleted before the transaction began.
  Rows that pass both tests may be returned as the query’s result.
INSERT：
InnoDB records the current system version number with the new row.
DELETE：
InnoDB records the current system version number as the row’s deletion ID.
UPDATE：
InnoDB writes a new copy of the row, using the system version number for the new
row’s version. It also writes the system version number as the old row’s deletion
version.