数据库事务及锁总结

什么是事务？

事务是指是程序中一系列严密的逻辑操作，而且所有操作必须全部成功完成，否则在每个操作中所作的所有更改都会被撤消。是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行；事务是一组不可再分割的操作集合（工作逻辑单元）

事务的四种特性（ACID）

1 、原子性（atomicity）
事务是数据库的操作的最小单元，不可分割。事务中包含的各操作要么都做，要么都不做 。
2 、一致性（consistency）
事务的执行使数据从一个状态转换为另一个状态，但是对于整个数据的完整性保持稳定。 
3 、隔离性（isolation）
一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。 
4 、持续性（durability）
也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。

事务的并发问题

1、脏读（Dirty Read）：一个事务处理过程里读取了另一个未提交的事务中的数据。例如：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读（NonRepeatable Read）：一个事务两次读取同一行的数据，结果得到不同状态的结果，中间正好另一个事务更新了该数据，两次结果相异，不可被信任。

3、幻读（Phantom Read）：幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

　　幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

事务的隔离级别

隔离级别	脏读 (Dirty Read)	不可重复读 (NonRepeatable Read)	幻读 (Phantom Read)
读未提交(Read uncommitted)	可能	可能	可能
读已提交(Read committed)	不可能	可能	可能
可重复读(Repeatable read)	不可能	不可能	可能
可串行化(Serializable)	不可能	不可能	不可能

引用看到的一个例子，非常有意思

1. 读未提交(Read uncommitted)：一个事务可以读取另一个未提交事务的数据。

老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

2. 读已提交(Read committed)：一个事务要等另一个事务提交后才能读取数据。

程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

3. 可重复读(Repeatable read)：开始读取数据（事务开启）时，不再允许修改操作。

程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

4. 可串行化(Serializable)：最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

ORACLE的隔离级别

ORACLE提供了SQL92标准中的read committed（缺省的事务隔离级别）和serializable，同时提供了非SQL92标准的read-only。

read-only：

        遵从事务级的读一致性，仅仅能看见在本事务开始前由其它事务提交的更改。

        不允许在本事务中进行DML操作。

        read only是serializable的子集。它们都避免了非重复读和幻像。区别是在read only中是只读；而在serializable中可以进行DML操作。

        Export with CONSISTENT = Y sets the transaction to read-only.

        read committed和serializable的区别和联系：

设置隔离级别

    设置一个事务的隔离级别

        SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

        SET TRANSACTION READ ONLY;

    设置多个会话的隔离级别

        ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;

        ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;

ORACLE锁使用

行级锁
select * from person for update;--该表的所有行都加上锁
select * from person  where id = '1'  for update nowait;--该行记录已经被锁定，就不用等待，系统会直接抛错 ora-00054 
select * from person for update wait 5;--该行记录已经被锁定，更新的时候等待5秒，如果这5秒内，该行记录被解锁，那么返回查询结果，如果5秒内仍未解锁，那么系统会直接抛错 ora-00054 

select * from person  where id = '1'  for update;--当该行记录已经被锁定时，那么系统将一直等待该行记录被释放后，再加锁。

表级锁
lock table person in row share mode;--行共享：允许用户进行任何操作，禁止排他锁 
lock table person in row exclusive mode;--行排他：允许用户进行任何操作，禁止共享锁 
lock table person in share mode;--共享锁：其他用户只能看，不能修改 
lock table person in share row exclusive mode;--共享行排他：比共享锁有更多限制 
lock table person in exclusive mode;--排他锁：其他用户只能看，不能修改，不能加其他锁 

对于通过lock table命令主动添加的锁定来说，如果要释放它们，只需要发出rollback命令即可。

MYSQL的隔离级别

mysql默认的事务隔离级别为repeatable-read

设置隔离级别

        SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

        SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

        SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

        SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

MYSQL常见七种锁

这里以mysql默认使用的InnoDB引擎来说，参考觉得比较详细的一篇文章

实际上，MySQL官网中还提到了一种预测锁，这种锁主要用于存储了空间数据的空间索引

行锁

行锁的劣势：开销大；加锁慢；会出现死锁
行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强
加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁。

共享锁：select * from tableName where ... + lock in share more
排他锁：select * from tableName where ... + for update

行锁（Record Locks）

A record lock is a lock on an index record. Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.

行锁一定是作用在索引上的。

间隙锁（Gap Locks）

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

间隙锁一定是开区间。间隙锁在本质上是不区分共享间隙锁或互斥间隙锁的，而且间隙锁是不互斥的，即两个事务可以同时持有包含共同间隙的间隙锁。这里的共同间隙包括两种场景：其一是两个间隙锁的间隙区间完全一样；其二是一个间隙锁包含的间隙区间是另一个间隙锁包含间隙区间的子集。间隙锁本质上是用于阻止其他事务在该间隙内插入新记录，而自身事务是允许在该间隙内插入数据的。也就是说间隙锁的应用场景包括并发读取、并发更新、并发删除和并发插入。

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

在RU和RC两种隔离级别下，即使你使用select ... in share mode或select ... for update，也无法防止幻读（读后写的场景）。因为这两种隔离级别下只会有行锁，而不会有间隙锁。这也是为什么示例中要规定隔离级别为RR的原因。

临键锁（Next-key Locks）

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows.

临键锁是行锁+间隙锁，即临键锁是是一个左开右闭的区间。InnoDB的默认事务隔离级别是RR，在这种级别下，如果你使用select ... in share mode或者select ... for update语句，那么InnoDB会使用临键锁，因而可以防止幻读；但即使你的隔离级别是RR，如果你这是使用普通的select语句，那么InnoDB将是快照读，不会使用任何锁，因而还是无法防止幻读。

共享锁/排他锁（Shared and Exclusive Locks）

InnoDB implements standard row-level locking where there are two types of locks, shared (S) locks and exclusive (X) locks。

• A shared (S) lock permits the transaction that holds the lock to read a row.

• An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.

共享锁/排他锁都只是行锁，与间隙锁无关。其中共享锁是一个事务并发读取某一行记录所需要持有的锁，比如select ... in share mode；排他锁是一个事务并发更新或删除某一行记录所需要持有的锁，比如select ... for update。

不过这里需要重点说明的是，尽管共享锁/排他锁是行锁，与间隙锁无关，但一个事务在请求共享锁/排他锁时，获取到的结果却可能是行锁，也可能是间隙锁，也可能是临键锁，这取决于数据库的隔离级别以及查询的数据是否存在。

插入意向锁（Insert Intention Locks）

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. 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, respectively, 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.

意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。如果说间隙锁锁住的是一个区间，那么插入意向锁锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。与间隙锁的另一个非常重要的差别是：尽管插入意向锁也属于间隙锁，但两个事务却不能在同一时间内一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。这里我们再回顾一下共享锁和排他锁：共享锁用于读取操作，而排他锁是用于更新或删除操作。也就是说插入意向锁、共享锁和排他锁涵盖了常用的增删改查四个动作。

行锁优化

• 尽可能让所有数据检索都通过索引来完成，避免无索引行或索引失效导致行锁升级为表锁。
• 尽可能避免间隙锁带来的性能下降，减少或使用合理的检索范围。
• 尽可能减少事务的粒度，比如控制事务大小，而从减少锁定资源量和时间长度，从而减少锁的竞争等，提供性能。
• 尽可能低级别事务隔离，隔离级别越高，并发的处理能力越低。

表锁

表锁的优势：开销小；加锁快；无死锁
表锁的劣势：锁粒度大，发生锁冲突的概率高，并发处理能力低
加锁的方式：自动加锁。查询操作（SELECT），会自动给涉及的所有表加读锁，更新操作（UPDATE、DELETE、INSERT），会自动给涉及的表加写锁。也可以显示加锁：
共享读锁：lock table tableName read;
独占写锁：lock table tableName write;
批量解锁：unlock tables;

即便你在条件中使用了索引字段，MySQL会根据自身的执行计划，考虑是否使用索引(所以explain命令中会有possible_key 和 key)。如果MySQL认为全表扫描效率更高，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了explain检查SQL的执行计划，以确认是否真正使用了索引。

意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）

Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table。

The intention locking protocol is as follows:

• Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.

• Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

共享锁/意向排他锁属于表锁，且取得意向共享锁/意向排他锁是取得共享锁/排他锁的前置条件。

自增锁（Auto-inc Locks）

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns.The innodb_autoinc_lock_mode configuration option controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.

这段话表明自增锁是一种特殊的表级锁，主要用于事务中插入自增字段，也就是我们最常用的自增主键id。通过innodb_autoinc_lock_mode参数可以设置自增主键的生成策略。

悲观锁与乐观锁