mysql事务和锁

浏览数：28 / 时间：2015年06月12日

一、事务
事务是一组原子性的SQL查询语句，也可以被看做一个工作单元。
1、特性（ACID）
automicity：原子性，事务所引起的数据库操作，要么都成功，要么都失败
consisitency：一致性，事务执行前的总和和事务执行后的总和是不变的
isolation：隔离性，某个事务的结果只有在完成之后才对其他事务可见
durability：持久性，一旦事务成功完成，系统必须保证任何故障都不会引起事务表现出不一致性
2、隔离级别
read uncommitted: 可能读取到其他事务中未提交修改的数据（脏读）
read committed: 只能读取到已经提交的数据（不能重复读）
repeatable read: 在同一个事务内的查询都是事务开始时刻一致的，innodb的默认级别。（可重复读，幻读）
serializable: 完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
RU	可能	可能	可能
RC	不可能	可能	可能
RR	不可能	不可能	可能
Serializable	不可能	不可能	不可能

3、持续读
基于MVCC，提供给查询一个数据库在一个时间点的快照；只读取同一个在事务中第一次读所确定的快照
4、锁定读
二、锁
1、Innodb是行级锁：
S：共享锁
X：排他锁
IS：意向共享锁
IX：意向排他锁

（1）Record lock:记录锁
（2）Gap lock:间隙锁
（3）Next-key lock:记录锁和间隙锁的结合
（4）Insertion intention gap lock:看名字就知道了

2、锁兼容
（1）INSERT操作之间不会有冲突。
（2）GAP,Next-Key会阻止Insert。
（3）GAP和Record,Next-Key不会冲突
（4）Record和Record、Next-Key之间相互冲突。
（5）已有的Insert锁不阻止任何准备加的锁。

3、sql对应的锁
（1）select...from：读数据库的快照，不加锁，除非数据库的隔离界别为serializable。
（2）select...from...in share mode：在扫描到的所有索引上加next-key S锁
（3）select...from...for update：在扫描到的所有索引上加next-key X锁
（4）update...where...：在扫描到的每个索引上加next-key X锁
（5）delete from...where...：在扫描到的每个索引上加next-key X锁
（6）insert：在被插入的行上加X锁。这个锁是索引记录锁，不是next-key锁（所以没有gap锁），同时不阻止别的事务往这个记录的gap区间插入新的记录。
在插入这行之前，会首先在这行加一个Insertion intention gap锁。这个锁表示多个事务插入只要不是在同一个gap中的同一个点，那么向这个gap插入记录时不需要等待。
先加Insertion intention gap lock（IX），再加Record lock(X)

4、隔离级别对Next-Key锁的影响
A. Read Uncommitted和Read Committed时,不需要在间隙上加锁,Nexk-Key变成Record锁。
B. Repeatable Reads 和 Serializable时，通常情况下使用Next-key锁。

5、死锁
（1）死锁事务的回滚机制：
a、如果一个事务修改了non-transactional表(如MyISAM表，修改不能回滚),另一个表没有。
则没有修改non-transactional的会被回滚。
b、如果2个事务都修改了non-transactional表或者都没有。则比较2个事务修改的记录数和加
的锁数量。总和小的事务会被回滚。

6、实验

现有表t7，结构如下：

CREATE TABLE `t7` (
  `i` int(11) NOT NULL DEFAULT ‘0‘,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

表中数据：

+----+
| i  |
+----+
|  1 |
|  3 |
|  7 |
| 10 |
+----+

使用默认的RR隔离级别

事务1	事务2
begin;
	begin;
delete from t7 where i=5;(在区间(3,7)加X锁)
	insert into t7 values (4);(wait，4已经在t1的锁区间内了，所以不能获得锁，阻塞)
	insert into t7 values (6);(wait，6已经在t1的锁区间内了，所以不能获得锁，阻塞)
	insert into t7 values (7);( Duplicate entry ‘7‘ for key ‘PRIMARY‘，并且会在7上加S锁)
	insert into t7 values (3);( Duplicate entry ‘3‘ for key ‘PRIMARY‘，并且会在3上加S锁)
	insert into t7 values (2);(ok，不在锁区间，不会阻塞)
rollback;	rollback;

begin
	begin
delete from t7 where i=3;(在3上加X锁)
	insert into t7 values (2);(ok)
	insert into t7 values (6);(ok)
	insert into t7 values (3);(wait，3上的锁已经被t1获得，阻塞)
rollback;	rollback;

begin;
	begin;
insert into t7 values(5);(在5上加record锁)
	insert into t7 values(6);(ok)
	insert into t7 values(5);(wait，3上的锁已经被t1获得，阻塞)
rollback;	rollback;