MySQL学习笔记（一）——事务和锁

前言：在广义的计算机世界中，锁和版本控制是并发环境下的保证高效和运行正确的主要手段。面对多线程争抢临界资源的情况，必须采取措施进行干预，否则会出现数据不一致。通常使用锁和版本控制在高性能和数据一致性之间找到一个平衡。

总体的提高并发的思路如下：

普通锁 本质是串行执行
读写锁 读写分离，实现读读并发。
版本控制 实现读写并发。

事务

当数据库操作越来越复杂，数据操作并发量越来越大，仅仅使用原有的基本操作语句已无法满足业务需求。此时就需要引入事务的概念。事务将一组原子SQL进行组合，形成一个独立的工作单元，要么所有的SQL都执行，要么都不执行。事物之间的数据一致性冲突又引入了锁的概念。一个典型的事务应用场景就是转账。

START TRENSACTION
update user_account set balance = balance - 1000 where userID = 3;
update user_account set balance = balance + 1000 where userID = 1;
COMMIT;

事务四大特性

原子性

事务必须被视为一个不可分割的最小工作单元，整个事务的所有操作要么全部提交成功，要么全部失败回滚。对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。

一致性

数据库总是从一个一致性状态转换到另一个一致性状态。在上面的例子中，一致性确保了即使在执行最后两条语句时系统崩溃了。账户也不会损失1000元，因为事务没有提交，所以事务所做出的修改不会保存到数据库中。

隔离性

通常来说，一个事务在所做出的修改提交之前，对其他事务是不可见的。后面讨论事务隔离级别的时候再看。

持续性

一旦事务提交，其所做的修改就会永久的保存在数据库中，即使系统崩溃也不会丢失。

事务隔离级别

未提交读(READ UNCOMMITTED)

隔离性最差的级别，事务中的修改，即使没有提交，其他事务也会看到。一个事务读取另一个事务未提交的数据。会造成脏读（Dirty Read）。并且从性能上讲，这个级别并不会比其他级别性能好多少，所以非常不建议使用。

提交读（Read COMMITTED）

大多数的数据库默认隔离级别是提交读（但MySQL不是）。提交读满足隔离性的简单定义，但假设一个场景，事务A读取一行数据，事务B将这行数据改了之后提交，事务A再读取这一行数据时发现数据不一致了。这就是提交读隔离级别带来的问题，不可重复读(nonrepeatable read)。

可重复读（REPETABLE READ）

可重复读表示同一个事务多次读取同样的数据记录的结果是一致的。但还是解决不了幻读的问题，幻读就是当某个事务在读取某个范围的数据时，另一个事务在该范围内插入了新纪录。该事务再次读取时就产生了幻行。InnoDB引入多版本并发控制MVCC解决这个问题，使事务的读操作变成了快照读。MVCC后续专门讨论。该隔离级别是MySQL默认的隔离级别。

可串行化（SERIALIZABLE）

这是等级最高的隔离级别。通过强制事务串行执行，避免了幻读产生，该隔离级别会在读取的每一行上都加上排他锁。可能导致大量的超时和锁争用的问题。实际上很少使用，除非接受没有并发和数据强一致性的情况下，才会考虑使用。

隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读
READ UNCOMMITTED	Yes	Yes	Yes	No
READ COMMITED	No	Yes	Yes	No
REPEATABLE READ	No	No	Yes	No
SERIALIZABLE	No	No	No	Yes

死锁

当多个事务在同一资源上相互占用，并请求锁定对方占用的资源时，就产生了死锁。多个事务已不同的顺序锁定资源，多个事务同时锁定一个资源等都会产生死锁。

事务1

START TRANSACTION
UPDATE StockPrice SET close = 45.5 WHERE stock_id = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19.8 WHERE stock_id = 3 and date = '2002-05-02';
COMMIT;

事务2

START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = '2002-05-01';
COMMIT;

如果凑巧两个事务都执行了第一条语句，锁住并更新了一条数据，然后互相尝试执行第二条语句发现被锁了，然后就在等待对方释放锁，同时持有对方需要的锁。陷入了死循环。

InnoDB采用的策略是回滚持有最少行级排他锁的事务。死锁产生了原因主要是数据冲突和存储引擎的实现方式两个。InnoDB作为事务型的存储殷勤，在防止死锁产生做了很多工作。

快照读和当前读

当前读

当前读就是读取数据库最新的版本，事务会对读取的记录进行加锁，阻止其他事务对数据进行修改。
以下情况会使用当前读
select…lock in share mode (共享读锁)
select…for update
update , delete , insert

在可重复读隔离级别中，当前读通过行锁+间隙锁来防止幻读的发生。例如，假设要update一条记录，但是另一个事务已经delete这条数据并且commit了，如果不加锁就会产生冲突。所以update的时候肯定要是当前读，得到最新的信息并且锁定相应的记录。

快照读

单纯的select操作，不包括上述 select … lock in share mode, select … for update。　　　　
提交读隔离级别：每次select都生成一个快照读。
可重复读隔离级别：开启事务后第一个select语句才是快照读的地方，而不是一开启事务就快照读。

快照读通过undolog和mvcc实现。

undo log

undo log有两个作用：提供回滚和多个行版本控制(MVCC)。

在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

undo log是采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment。

另外，undo log也会产生redo log，因为undo log也要实现持久性保护。

如果要删除一行数据，当事务提交的时候，innodb不会立即删除undo log，因为后续还可能会用到undo log，如隔离级别为repeatable read时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即undo log不能删除。

但是在事务提交的时候，会将该事务对应的undo log放入到删除列表中，未来通过purge来删除。并且提交事务时，还会判断undo log分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的undo log页而浪费存储空间和性能。

通过undo log记录delete和update操作的结果发现：(insert操作无需分析，就是插入行而已)

delete操作实际上不会直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的。
update分为两种情况：update的列是否是主键列。
如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。
如果是主键列，update分两部执行：先删除该行，再插入一行目标行。

MVCC

大多数事务型的数据库都实现了多版本并发控制(MVCC) ，但各自实现的方式不尽相同。主要思想是通过保存数据库在某个时间点的快照实现的。不管执行多长时间，事务内部看到的数据总是一个快照，是一致的。根据事务开始的时间不同，同一时刻，同一张表看到的数据可能都是不一样的。

InnoDB的MVCC由乐观锁实现。通过在每行记录后面保存两个隐藏的列来实现的。一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。存储不是时间，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号就是事务的版本号。每条语句都会把当前事务版本号和记录的版本号进行比较。

SELECT

InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保该事务读取的行，要么是在事务开始之前就存在的，要么就是事务自身修改的。
行的删除版本要么未定义，要么大于当前事务版本号。确保了事务读取到的行，在事务开始之前未被删除。

INSERT

InnoDB给每个新插入的行保存当前系统版本号作为行版本号。

DELETE

InnoDB给删除的每一行保存当前系统版本号作为行的删除标识

UPDATE

InnoDB插入一行新数据，保存当前系统版本号作为行版本号，同时保存当前版本号到原来的行作为行的删除标识。

使用版本号作为事务数据判断依据，使得很多操作可以不用加锁。可使数据库性能更好，但这是个空间换时间的操作，每行记录都需要额外的存储空间，同时需要做更多的检查工作和额外工作。MVCC在MySQL中只在提交读和可重复读隔离级别下工作。

锁

数据库正确的进行并发，主要由事务和锁两个机制来保证。通常分为读锁和写锁。也称为共享锁和排他锁。

多个客户端在同一时刻可以同时对同一资源进行读取，互补干扰，所以叫共享锁。但是同一时间只允许一个客户端修改数据。写锁是排他的。只有这样才能保证数据不会错乱。在MySQL中，写锁比读锁的优先级高，会出现写锁插队排第一位的情况。

锁粒度

锁住的记录越少，并发的效率越高。MySQL按照加锁范围分为行级锁，表级锁以及全局锁。

行级锁

只对某些行数据进行加锁，当事务操作某一行的时候，只对该行数据加排他锁，其他事务操作其他行时不影响。并发性好，但由于加锁解锁频繁，比较耗时。根据两段锁协议，一个事务只有获取到所有的锁直到事务提交才会释放锁。

MySQL的行级锁是加在索引上的，所以如果查询条件没有用到索引，也就无法使用到行级锁。

表级锁

表锁是MySQL最基本的锁策略，是开销最小的锁，一个事务在对表进行增删改查时，会使用相应的读写锁锁住整张表。

表锁不只是存储引擎进行管理，服务器层也会通过Alter操作使用表锁，比存储引擎的级别更高。

全局锁

通常在给数据库做备份和快照的时候使用。

InnoDB常用的锁种类

记录锁（Record Locks）
间隙锁（Gap Locks）
临键锁（Next-Key Locks）
共享锁/排他锁（Shared and Exclusive Locks）
意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）
插入意向锁（Insert Intention Locks）
自增锁（Auto—Inc Locks）

记录锁

锁定单行，一定是加在索引上的，如果查询的SQL没有走索引，加锁时会锁全表。如果where条件是主键，那就会直接锁主键，如果不是主键索引，会先锁住二级索引，然后再锁住对应的主键索引。

间隙锁

间隙锁只存在于可重复读及以上隔离级别中，锁住的是一个范围区间，防止其他事务在一定的间隙内进行插入，防止幻读的产生。假设where条件带id>3，那么所有id>3都会被加上间隙锁，及id不允许从3到正无穷。

临键锁

行锁同时再加上间隙锁就是临键锁。锁住间隙及记录本身的记录行。

共享锁/排他锁

共享锁和排他锁是按照锁的性质区分的，一个事务在请求共享锁和排他锁时，可能是行锁，间隙锁，也可能是临键锁。但都是行级锁

意向共享锁/意向排他锁

意向共享锁和意向排他锁属于表锁，意思是该事务有意向对表中的某些行加锁。所以取得意向锁是共享锁/排他锁的前置条件。事务要获得某些行的S锁，必须先获得表的IS锁，事务要获得某些行的X锁，必须先获得表的IX锁。
意向锁仅仅只表明意向，是一个比较弱的锁，所以意向锁之间并不互斥。但是意向锁会和共享锁/排他锁互斥。兼容互斥表如下：

	X（排他锁）	IX（意向排他锁）	S（共享锁）	IS（意向共享锁）
X（排他锁）	互斥	互斥	互斥	互斥
IX（意向排他锁）	互斥	兼容	互斥	兼容
S（共享锁）	互斥	互斥	兼容	兼容
IS（意向共享锁）	互斥	兼容	兼容	兼容

插入意向锁

插入意向锁是间隙锁的一种，所以也是加在索引上的。专门针对insert操作。如果多个事务插入相同的索引间隙时，只要不是插入到相同的位置，就不用进行锁等待。

自增锁

自增锁是一种特殊的表级锁，如果一个表的某个行具有AUTO_INCREMENT的列，则一个事务在插入记录到这个表的时候，会先获取自增锁，如果一个事务持有自增锁，则会堵塞其他事务对该表的插入操作，保证自增的连续性。