简介：
在默认的情况下，MySQL在自动提交（autocommit=1/ON）模式运行，这种模式会在每条语句执行完毕后把它作出的修改立刻提交给数据库并使之永久化。事实上，这相当于把每一条语句都隐含地当做一个事务来执行。如果你想明确地执行事务，需要禁用自动提交模式并告诉MySQL你想让它在何时提交或回滚有关的修改。
我们想要明确的执行事务的话，用START TRANSACTION（或BEGIN）开启事务，其实它会自动挂起自动提交事务模式（即会忽略autocommit=1），然后执行本次事务的各语句，最后用COMMIT语句结束事务并把它们做出的修改永久性记入数据库。万一事务过程中发生错误，用一条ROLLBACK语句撤销事务并把数据库恢复到事务开始之前的状态。
START TRANSACTION语句在COMMIT/ROLLBACK之后会做什么？
答：在事务被提交或回滚之后，该模式将恢复到开始本次事务的START TRANSACTION语句被执行之前的状态，这里有两种情况：1.如果自动提交模式原来是激活的，结束事务将让你回到自动提交模式
2.如果它原来是非自动提交模式的，结束当前事务将开始下一个事务
下面实例说明
假如有表 t（a, b, c）主键为a，b为唯一索引，c为常规字段
查看是否自动提交
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
可见以上为自动提交
mysql> select * from t;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
+---+------+------+
2 rows in set (0.00 sec)
以下全为自动模式下做的实验：
1。查看自动提交模式数据提交情况。连接1，连接2都未开启事务
连接1 | 连接2 |
| |
| |
| |
| |
2. 查看自动提交模式下start transaction的作用。连接1开启事务，连接2未开启
连接1 | 连接2 |
| |
| |
| |
| |
| |
| |
3. 连接1，连接2同时开启事务时
连接1 | 连接2 |
| |
| |
| |
| |
| |
| |
| |
| |
| |
4. 假如有这样的场景，连接1里给c+1，连接2里的给c-2
连接1 | 连接2 |
| |
| |
| |
| |
| |
| |
| |
| |
5. 下面测试下死锁情况
连接1-事务1 | 连接2-事务2 |
| |
| |
| |
| |
| |
| |
| |
| |
5.1 死锁日志分析
使用show engine innodb status\G查看死锁信息，下面只摘取了死锁信息部分，其他的省略。
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2015-05-21 16:12:55 7fe02cfd2700 *** (1) TRANSACTION: ## 事务1 TRANSACTION 7651536, ACTIVE 218 sec starting index read ## 事务ID=7651536, 活跃了218秒 mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1248, 3 row lock(s) ## 有3个行锁 MySQL thread id 192071, OS thread handle 0x7fe02ce0b700, query id 13896576 114.112.84.198 root updating ## 该事务的线程ID=192071 update t set c=3 where b=1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: ##等待要加的锁 RECORD LOCKS space id 3864 page no 4 n bits 72 index `ib` of table `test`.`t` trx id 7651536 lock_mode X locks rec but not gap waiting ## ## 等待在唯一索引ib上的page num=4上加一个X锁（lock_mode X locks rec but not gap）waiting意指等待的锁 Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 4; hex 80000001; asc ;;
*** (2) TRANSACTION: ## 事务2 TRANSACTION 7651538, ACTIVE 200 sec starting index read ## 事务ID=7651538, 活跃了200秒 mysql tables in use 1, locked 1 4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 ## 4个锁，3个行锁，1个undo log MySQL thread id 192072, OS thread handle 0x7fe02cfd2700, query id 13896591 114.112.84.198 root updating ## 该事务的线程ID=192072 update t set c=6 where b=2 *** (2) HOLDS THE LOCK(S): ## 这个事务持有的锁信息 RECORD LOCKS space id 3864 page no 4 n bits 72 index `ib` of table `test`.`t` trx id 7651538 lock_mode X locks rec but not gap ## 在唯一索引ib上page num=4上已持有一个X锁 Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 4; hex 80000001; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: ## 同时这个事务还等待的锁信息 RECORD LOCKS space id 3864 page no 4 n bits 72 index `ib` of table `test`.`t` trx id 7651538 lock_mode X locks rec but not gap waiting ## 同样等待在唯一索引ib上的page num=4上加一个X锁（lock_mode X locks rec but not gap） Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 4; hex 80000002; asc ;;
*** WE ROLL BACK TRANSACTION (1) ## 这里选择回滚了事务7651536 也就是事务7651536的sql没有执行 update t set c=3 where b=1 事务7651538的sql执行了 update t set c=6 where b=2 |
6. for update实验（for update是排它锁X）
注意：如果autocommit为on的状态的话，必须手动begin开启事务，否则for update锁不住
off状态的话会自动锁（因为auto模式下sql执行完了就commit了）
连接1 - 事务1 | 连接2 - 事务2 |
| |
| |
| |
| |
7. for update范围
有如下表：
MariaDB [t]> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`a` varchar(255) DEFAULT NULL,
`b` varchar(255) DEFAULT NULL,
`c` int(10) DEFAULT NULL,
`d` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_b` (`b`) USING BTREE,
KEY `index_a` (`a`) USING BTREE,
KEY `index_c` (`c`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MariaDB [t]> SHOW VARIABLES LIKE '%AUTOCOMMIT%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [t]> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set (0.00 sec)
MariaDB [t]> select * from t;
+----+------+------+------+------+
| id | a | b | c | d |
+----+------+------+------+------+
| 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | 2 |
| 3 | 3 | 3 | 3 | 3 |
| 4 | 4 | 4 | 4 | 4 |
| 5 | 5 | 5 | 5 | 5 |
| 6 | 1 | 6 | 1 | 2 |
| 7 | 1 | 7 | 1 | 2 |
+----+------+------+------+------+
7 rows in set (0.00 sec)
7.1 事务1开事务，事务2开事务/不开事务
事务1 | 事务2 |
| |
|
|
7.2 事务1不开事务，事务2开事务/不开事务
事务1 | 事务2 |
| |
|
|
| |
7.3 主键索引锁一条，当查询不到记录时，不锁任何记录
事务1 | 事务2 |
| |
|
|
以下为间隙锁讲解
| |
|
|
| |
|
|
7.4 非主键索引查询，会锁住查询的记录，其余不锁
事务1：
MariaDB [t]> begin;
Query OK, 0 rows affected (0.01 sec)
MariaDB [t]> select * from t where a='1' for update;
+----+------+------+------+------+
| id | a | b | c | d |
+----+------+------+------+------+
| 1 | 1 | 1 | 1 | 1 |
| 6 | 1 | 1 | 1 | 2 |
| 7 | 1 | 2 | 2 | 2 |
+----+------+------+------+------+
3 rows in set (0.00 sec)
MariaDB [t]> explain select * from t where a='1' for update;
+------+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| 1 | SIMPLE | t | ref | index_a | index_a | 768 | const | 3 | Using index condition |
+------+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
查看查询计划，用的索引，所以锁全表记录
一定要注意：varchar的索引如果查询时不用''单引号包起来不会走索引
MariaDB [t]> explain select * from t where a=1 for update;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | index_a | NULL | NULL | NULL | 17 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+事务2：
MariaDB [t]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [t]> select * from t where a=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where a='1' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where a='3' for update;
+----+------+------+------+------+
| id | a | b | c | d |
+----+------+------+------+------+
| 3 | 3 | 3 | 3 | 3 |
+----+------+------+------+------+
1 row in set (0.00 sec)
MariaDB [t]> select * from t where b='1' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where b='3' for update;
+----+------+------+------+------+
| id | a | b | c | d |
+----+------+------+------+------+
| 3 | 3 | 3 | 3 | 3 |
+----+------+------+------+------+
1 row in set (0.00 sec)
MariaDB [t]> select * from t where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where id=3 for update;
+----+------+------+------+------+
| id | a | b | c | d |
+----+------+------+------+------+
| 3 | 3 | 3 | 3 | 3 |
+----+------+------+------+------+
1 row in set (0.00 sec)
可见，只锁住了查询出来的3条记录，其余都未加锁
MariaDB [t]> insert into t(a,b,c,d) values('1','2',3,'3');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> insert into t(a,b,c,d) values('2','2',3,'3');
Query OK, 1 row affected (0.00 sec)
可见插入时，a为'1'值的记录插入不进去，其余都可以
MariaDB [t]> update t set d='3' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> update t set d='3' where id=3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
可见修改记录时也是，如果此条记录被锁住的话需要等待锁
7.5 非索引字段锁全部记录
事务1
MariaDB [t]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [t]> select * from t where d='1' for update;
+----+------+------+------+------+
| id | a | b | c | d |
+----+------+------+------+------+
| 1 | 1 | 1 | 1 | 1 |
+----+------+------+------+------+
1 row in set (0.00 sec)
MariaDB [t]> explain select * from t where d='1' for update;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 18 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
可见全表扫描事务2
MariaDB [t]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [t]> select * from t where a='1' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where a='1' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where a='3' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where b='1' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where id=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
非主键索引时锁全表的记录，其他记录都获取不到锁（包括主键索引），所以一直等待到超时
MariaDB [t]> insert into t(a,b,c,d) values('1','2',3,'3');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
非主键索引时锁全表的记录，可见插入不可插
MariaDB [t]> update t set d='3' where id=3;
修改记录也是不行的
8.幻读
在可重复读的隔离级别下，可以解决不可重复读，以及幻读(按定义是有幻读的，但mysql通过mvcc解决了（一致性读是通过 MVCC 为查询提供了一个基于时间的点的快照。这个查询只能看到在自己之前提交的数据，而在查询开始之后提交的数据是不可以看到的。在默认隔离级别REPEATABLE READ下，同一事务的所有一致性读只会读取第一次查询时创建的快照）， 但加锁读还是可以读到的)，见下
MariaDB [demo]> desc user;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id | varchar(50) | NO | PRI | NULL | |
| name | varchar(255) | YES | | NULL | |
| create_time | datetime | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)事务1 | 事务2 |
| |
|
|
| |
|
|
| |
|
|
|
|
|
|
8.1 那就来详细分析下RR隔离级别下是如何防止幻读的
幻读问题是指一个事务的两次不同时间的相同查询返回了不同的的结果集。例如:一个 select 语句执行了两次，但是在第二次返回了第一次没有返回的行,那么这些行就是“phantom” row.
read view(或者说 快照读 或者说MVCC)实现了一致性不锁定读(Consistent Nonlocking Reads)，从而避免了幻读
深一点这里讲解也不错
首先读分为：
快照读
select * from table where ?;
当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。
对于快照读来说，幻读的解决是依赖mvcc解决。而对于当前读则依赖于gap-lock解决(因为间隙锁会锁定当前查询间隙中暂时不存在的数据，其他事务想插入或删除或修改都会有当前这个间隙锁而被hang住)。
需要注意的是快照读是在select查询后而不是事务开启后
看下面一个异常现象（也不算异常了）
SESSION_A开始事务并创建快照，或一个普通的查询也会创建一个快照读
SESSION_A>START TRANSACTION WITH CONSISTENT SNAPSHOT;
Query OK, 0 rows affected (0.00 sec)
SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| init |
| after session A select |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)
SESSION_B>insert into read_view values('anomaly'),('anomaly');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
SESSION_B>update read_view set text='INIT' where text='init';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| init |
| after session A select |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)
# SESSION_A更新了它并没有"看"到的行
SESSION_A>update read_view set text='anomaly!' where text='anomaly';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
# 这里居然看到了SESSION_B commit的数据了
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| init |
| after session A select |
| before Session_A select |
| anomaly! |
| anomaly! |
+-------------------------+
5 rows in set (0.00 sec)
SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| INIT |
| after session A select |
| before Session_A select |
| anomaly! |
| anomaly! |
+-------------------------+
5 rows in set (0.00 sec)观察实验步骤可以发现，在倒数第二次查询中，出现了一个并不存在的状态，这里A的前后两次读，均为快照读，而且是在同一个事务中。但是B先插入直接提交，此时A再update，update属于当前读，所以可以作用于新插入的行，并且将修改行的当前版本号设为A的事务号，所以第二次的快照读，是可以读取到的，因为同事务号。这种情况符合MVCC的规则，如果要称为一种幻读也非不可，算为一个特殊情况来看待吧。
InnoDB通过Nextkey lock解决了当前读时的幻读问题，就是读取时采用当前读
Innodb行锁分为:
类型 | 说明 |
Record Lock: | 在索引上对单行记录加锁. |
Gap Lock: | 锁定一个范围的记录,但不包括记录本身.锁加在未使用的空闲空间上,可能是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间. |
Next-Key Lock: | 行锁与间隙锁组合起来用就叫做Next-Key Lock。锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。 |
假如有下
(mysql@localhost) [fandb]> create table t5(id int,key(id));
Query OK, 0 rows affected (0.02 sec)
(mysql@localhost) [fandb]> insert into t5 values(1),(4),(7),(10);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0开始实验
SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from t5;
+------+
| id |
+------+
| 1 |
| 4 |
| 7 |
| 10 |
+------+
4 rows in set (0.00 sec)
SESSION_A>select * from t5 where id=7 for update;
+------+
| id |
+------+
| 7 |
+------+
1 row in set (0.00 sec)
SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_B>insert into t5 values(2);
Query OK, 1 row affected (0.00 sec)
SESSION_B>insert into t5 values(12);
Query OK, 1 row affected (0.00 sec)
SESSION_B>insert into t5 values(4); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
^[[AERROR 1317 (70100): Query execution was interrupted
SESSION_B>insert into t5 values(5); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
^[[AERROR 1317 (70100): Query execution was interrupted
SESSION_B>insert into t5 values(7); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
SESSION_B>insert into t5 values(9); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
SESSION_B>insert into t5 values(10);
Query OK, 1 row affected (0.00 sec)
SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from t5;
+------+
| id |
+------+
| 1 |
| 4 |
| 7 |
| 10 |
+------+
4 rows in set (0.00 sec)
SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from t5;
+------+
| id |
+------+
| 1 |
| 2 |
| 4 |
| 7 |
| 10 |
| 10 |
| 12 |
+------+
6 rows in set (0.00 sec)当以当前读模式select * from t5 where id=7 for update;获取 id=7的数据时,产生了 Next-Key Lock,锁住了4-10范围和 id=7单个record
从而阻塞了 SESSION_B在这个范围内插入数据，而在除此之外的范围内是可以插入数据的。
在倒数第二个查询中,因为 read view 的存在，避免了我们看到 2和12两条数据，避免了幻读
同时因为 Next-Key Lock 的存在,阻塞了其他会话插入数据，因此当前模式读不会产生幻读(select for update 是以当前读模式获取数据)
尽量使用唯一索引,因为唯一索引会把Next-Key Lock降级为Record Lock
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
