Innodb事务和锁

事务概念

数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作，经典的事务场景是转账，A(id为3)给B(id为1)转账：

update user_account set balance = balance - 1000 where user_id = 3;
update user_account set balance = balance + 1000 where user_id = 1;

这两个sql要保证必须同时成功或同时失败，否则数据将出现不一致的情况。

mysql中的事务

查看mysql事务开启状态：

show variables like 'autocommit'

默认是ON。

mysql中开启事务

会话级别

set session autocommit = on/off;  

这个是对当前会话设置自动提交，对其他会话不起作用，如果设置为off，这时候执行完sql后，当前会话都要手动加上commit才能提交事务。

手动开启

手动执行sql：

开启事务：begin / start transaction；
提交或回滚事务：commit / rollback

JDBC编程中：

connection.setAutoCommit（boolean）;
connection.commit();

Spring事务AOP编程：

expression=execution（com.faith.dao.*.*(..))

mysql中默认是自动提交事务的。也就是在你执行sql语句的时候，它会自动在你sql前边加上begin或start transaction；在后边自动加上commit；从而事务就会自动提交。

当手动使用begin或start transaction时，mysql就会取消自动加事务，例如：

begin;
update user set name="faith" where id=1；

执行后，数据库id为1的记录并不会改变，因为这时候mysql不会自动提交，当手动执行commit之后才会进行提交。

而因为mysql自动提交事务，所以如下两个sql实际上是在两个事务中的：

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

那么为了保证原子性，我们需要做如下操作：

begin；
update user_account set balance = balance - 1000 where userID = 3;
update user_account set balance = balance +1000 where userID = 1;
commit；

这样就把这两个sql放到一个事务中去了。

在jdbc中将两个sql放到一个事务中,如下：

connection.setAutoCommit（boolean）;
update user_account set balance = balance - 1000 where userID = 3;
update user_account set balance = balance +1000 where userID = 1;
connection.commit();

Spring事务AOP编程，实际上也是做了手动开启操作:

expression=execution（com.faith.dao.*.*(..))

这里设置了一个切面，基于这个切面的所有方法都会被拦截，这些方法配置事务的传播性质，拦截之后，spring会在方法之前加一个切面，设置会话手动提交，例如：

connection.setAutoCommit（boolean）;

然后在方法之后加一个切面，设置会话提交，例如：

connection.commit();

当catch到异常的时候，就执行

connection.rollback();

事务的特性

原子性（Atomicity）

事务是最小的工作单元，事务中的sql要么一起提交成功，要么全部失败回滚。

一致性（Consistency）

事务中操作的数据及状态改变是一致的，更新的数据必须完全符合预设的规则，不会因为事务或系统等原因导致状态的不一致。

隔离性（Isolation）

一个事务所操作的数据在提交之前，对其他事务的可见性设定。如果事务并发且相互不隔离，会导致脏读、不可重复读、幻读等系列问题。

持久性（Durability）

事务所做的修改会永久保存，不会因为系统意外导致数据的丢失。

原子性和一致性是两个不同的概念。对于原子性来说，如下的两条语句：

update user_account set balance = balance - 1000 where userID = 3;
update user_account set balance = balance +10000 where userID = 1;

只要同时执行成功或失败就是符合原子性的。

而对于一致性来说是不成立的，因为实际给1转账1000，但是1的账户上多了10000，不符合转账的规则，导致了数据的不一致性。

事务的隔离级别

SQL92 ANSI/ISO标准

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
Read Uncommitted（读未提交）
    --未解决任何并发问题，可以读到其他事务未提交的数据，会导致脏读（dirty read）。

Read Committed（读已提交）
    --解决脏读问题，一个事务开始之后，只能看到己提交的事务所做的修改，但是未解决不可重复读（nonrepeatable read）。

Repeatable Read (可重复读)
    --解决不可重复读问题，在同一个事务中多次读取同样的数据结果是一样的，这种隔离级别未定义解决幻读的问题。

Serializable（串行化）
    --解决所有问题，最高的隔离级别，通过强制事务的串行执行。

innodb对隔离级别的支持

在92标准中，可重复读级别可以不解决幻读问题，但是innodb存储引擎的可重复读解决了幻读问题。

锁

锁用来管理不同事务对共享资源的并发访问。

表锁与行锁：

锁定粒度：表锁 > 行锁

表锁直接锁定表，行锁只锁定一行。

加锁效率：表锁 > 行锁

直接对表加锁块，而行锁需要在表中找到指定的行记录。

冲突概率：表锁 > 行锁，表锁锁定的记录更多，更容易产生冲突。

并发性能：表锁 < 行锁

InnoDB存储引擎只有行锁，没有表锁，但它也能实现表锁的效果，因为它的表锁是把表中所有的行都锁一遍，就成了表锁。这个只是实现了表锁的效果，但是和真正的表锁效率相比要低下很多。

innodb的锁类型

InnoDB默认select语句不加任何锁类型，但是delete、update、insert 默认会加上X锁。

innodb共有八种锁：

共享锁（行锁）：Shared Locks

排它锁（行锁）：Exclusive Locks

意向锁共享锁（表锁）：Intention Shared Locks

意向锁排它锁（表锁）：Intention Exclusive Locks

自增锁：AUTO-INC Locks

关于行锁的锁：

记录锁 Record Locks

间隙锁 Gap Locks

临键锁 Next-key Locks

共享锁

又称为读锁，简称S锁，多个事务对于同一数据可以共享一把共享锁，持有共享锁的事务都能访问到数据，但是只能读不能修改。

共享锁示例：

begin；
select * from user WHERE id=1 LOCK IN SHARE MODE;

不执行commit操作，这时候在另一个窗口执行：

select * from user WHERE id=1;
update user set name='2' where id=1;

select操作可以查到数据，但是update会被阻塞，直到最开始申请到共享锁的事务执行commit或rollback来释放享锁，之后update才会继续执行。

排他锁

又称为写锁，简称X锁，排他锁不能与其他锁并存，如一个事务获取了某条记录的排他锁，其他事务就不能再获取该行的锁（共享锁、排他锁），即不能读不能写，只有持有排他锁的事务才可以对记录进行读取和修改(其他事务要读取数据可来自于快照)。

排它锁示例：

begin；
update user set name='2' where id=1；

不执行commit操作，这时候在另一个窗口执行：

select * from user WHERE id=1 LOCK IN SHARE MODE;
update user set name='3' where id=1;

这两条操作都会被阻塞，直至持有排它锁的事务commit或rollback之后才能继续执行。

意向共享锁(IS)

表示事务准备给数据行加入共享锁，即一个数据行加共享锁前必须先取得该表的IS锁，意向共享锁之间是可以相互兼容的。

意向排它锁(IX)

表示事务准备给数据行加入排他锁，即一个数据行加排他锁前必须先取得该表的IX锁，意向排它锁之间也是可以相互兼容的。

意向锁(IS 、IX)是InnoDB在数据操作之前自动加的，不需要用户干预，我们编码时不需要对意向锁进行特殊处理。

意向锁是表级锁，之间是相互兼容的，也就是说多个持有锁的线程，可以同时持有意向锁。比如update id=1，update id=2，他们可以同时持有意向锁。

意向锁存在的意义：

只有当事务想去进行锁表时，意向锁才会发挥作用，事务会先判断意向锁是否存在，如果存在，说明此时肯定有行锁存在，这时候不能进行表锁，则可快速返回该表不能启用表锁，省略了进入底层扫描全表的数据。

自增锁

针对自增列自增长的一个特殊的表级别锁，可以使用如下语句查看自增锁的模式：

show variables like 'innodb_autoinc_lock_mode';

此参数可取的值有三个：0、1、2，默认取值1。

取值0：传统方式，串行自增，并且是连续的。这种模式下需要语句执行完成才释放自增锁，所以性能最低。例如：1、2、3、4、5、6，没有人为删除情况下，表中id字段一定是连续的。

取值1：连续方式，自增的，并且是连续的。当语句申请到自增锁就释放自增锁，自增锁就可以给其它语句使用，性能会好很多。但因为不会等待语句事务执行完毕就释放了自增锁，可能该事务回滚了，所以id可能会出现断续的情况，例如：1、2、6，8,10

2：交错方式，多语句插入数据时，有可能自增的序列号和执行先后顺不一致，并且中间可能有断裂。一次分配一批自增值，然后下个语句就再进行分配一批自增值，阻塞很小，性能很高。例如：1、2、3、6、5。

设置为2时，需要确认表是否需要连续的自增值，如果需要，就不要使用这个值。

临键锁(Next-key locks)

当sql执行按照索引进行检索，查询条件为范围查找(between and、<、>等)并且有数据命中，则此时SQL语句加上的锁为Next-key locks，锁住索引范围为记录的本区间 + 本区间下一个区间(左开右闭)。

mysql会对记录自动划分出区间，如下：

如果为1,2,4,7,10，区间则为（-&,1],(1,2],(2,4],(4,7],(7,10],(10,+&)。划分区间是依据B+树节点之间的间隙来划分的，1和2之间没有间隙，但是在树中，是两个不同的节点，它们之间是有间隙的。

update user set name=1 where id>5 and id<10

上面的sql选中的记录是id=7，这时候锁住的区间是(4,7]，(7,10]，(4,7]是本区间，而(7,10]是本区间的下一个区间。

锁住本区间和相邻区间就是防止幻读，例如这里的>5和<9条件，肯定是要锁定(4,7]，(7,10]区间才能实现的，也就是要锁住本区间和相邻区间。

锁住区间是因为B+树的特性，如果把这个例子中的id换成age就更好理解了。

因为innodb默认隔离级别是可重复读，而前边说了innodb的可重复读还捎带解决了幻读问题，而幻读问题就是临键锁配合mvcc一起解决的。

间隙锁(Gap locks)

当sql执行按照索引进行检索，查询条件为范围查找并且查询的数据不存在，这时SQL语句加上的锁即为Gap locks，锁住索引不存在的区间(左开右开)。

只在可重复读隔离级别存在是因为innodb的可重复读解决了幻读问题。

Record locks

当sql执行按照唯一性(Primary key、Unique key)索引进行检索，查询条件为精准等值匹
配且查询的数据是存在，这时SQL语句加上的锁即为记录锁Record locks，这种情况只针对唯一索引，所以对应的是const或equ_ref级别的查询。

innodb的行锁锁住了哪些内容

得出结论之前先做个测试。例如user表中，id，name，age，create_time字段，id和name有索引，create_time没有加索引。

测试1

begin；
select * from user where id=1;

不执行commit或rollback。然后在其他线程执行：

select * from user where id=1; 阻塞
select * from user where id=2; 非阻塞
select * from user where id=3; 非阻塞

测试2

begin；
select * from user where name=‘1’;

不执行commit或rollback。然后在其他线程执行：

select * from user where name=‘1’; 阻塞
select * from user where name=‘2’; 非阻塞
select * from user where name=‘3’; 非阻塞

测试3

begin；
select * from user where create_time=1;

不执行commit或rollback。然后在其他线程执行：

select * from user where create_time=1; 阻塞
select * from user where create_time=2; 阻塞
select * from user where create_time=3; 阻塞

结论：InnoDB的行锁是通过给索引树中的索引项加锁来实现的，如果是聚集索引，那么直接锁住聚集索引的索引项，如果是非聚集索引，那么会锁住当前索引的索引项，以及对应的聚集索引中的索引项。

也就是说对于非聚集索引，会在两棵索引树中分别上锁。只有通过索引条件进行数据检索，InnoDB才使用行级锁，否则，InnoDB将使用表锁(锁住索引的所有记录)。

表锁是非常耗费性能的，所以要避免表锁。这种特性可以给平时写sql带来一些启发，例如：

delete from user where create_time；导致表锁
delete from user where id=1；行锁；

写删改的sql时候，要考虑where条件是否命中了索引，要避免表锁出现，删一条记录，却导致整张表被锁住了，这是一件很郁闷的事。

锁如何解决并发问题

脏读

加上X锁可以解决脏读。

不可重复读

模拟不可重复读：

select name from user where id=1;// name='1'
update user set name='2' where id=1; // 执行成功
select name from user where id=1;// name='2'

上面两个select的结果不同，导致了不可重复读。解决方法是给这两个select加上S锁：

select name from user where id=1 LOCK IN SHARE MODE;// name='1'
update user set name='2' where id=1; // 阻塞
select name from user where id=1 LOCK IN SHARE MODE;// name='1'

这样在当前事务执行完毕之前，不可能被其他事务更改值，从而解决了不可重复读的问题。

幻读

加临键锁可以解决幻读。

死锁

多个并发事务每个事务都持有锁，每个事务都需要再继续持有其他事务持有的锁，但谁都不释放自己手中的锁，产生锁的循环等待，这就形成了死锁。

死锁的避免

类似的业务逻辑以固定的顺序访问表和行。

大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择

为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁(或者说是表锁)，表锁造成的锁冲突比行锁要严重的多