MySQL 在存储引擎级别上提供事务支持，以下示例基于InnoDB

事务操作指令

###开启事务
start transaction或begin
###提交事务
commit;
###回滚事务
roolback;
###--------------设置autocommit=0，需要显示调用commit或rollback

死锁

mysql> create table dlock(a int(10) unsigned not null auto_increment primary key)engine innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> show create table dlock;
+-------+---------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                    |
+-------+---------------------------------------------------------------------------------------------------------------------------------+
| dlock | CREATE TABLE `dlock` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into dlock values();
Query OK, 1 row affected (0.01 sec)

mysql> insert into dlock select null from dlock;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into dlock select null from dlock;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into dlock select null from dlock;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dlock;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.00 sec)

mysql>

session1:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into dlock values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from dlock;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 6 |
+---+
5 rows in set (0.00 sec)

session2:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into dlock values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from dlock;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 7 |
+---+
5 rows in set (0.00 sec)

session1

mysql> update dlock set a = 7 where a=6;
#~output 此时处于等待状态

session2:

mysql> update dlock set a = 6 where a = 7;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
#output 失败并报告死锁，于此同时session1完成

多事务之间挂起解决方案

session1:

session2:

session3:
同行锁状态一致，此时并无其他有用信息

隐式提交

DDL语句create会产生隐式提交问题

step1—-
session1:

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> create table t1(f1 int)engine =innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(100);
Query OK, 1 row affected (0.00 sec)

session2:

mysql> select * from t1;
Empty set (0.00 sec)

step2—-
session1:

mysql> create table t2 like t1;
Query OK, 0 rows affected (0.06 sec)

session2:

mysql> select * from t1;
+------+
| f1   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

step3—-
session1:

mysql> insert into t2 values(200);
Query OK, 1 row affected (0.00 sec)

session2:

mysql> select * from t1;
+------+
| f1   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)