【MySql】MySql之事务

    MySQL 事务主要用于处理操作量大，复杂度高的数据。MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关1.MyISAM：不支持事务，用于只读程序提高性能 2.InnoDB：支持ACID事务、行级锁、并发 3.Berkeley DB：支持事务

在 MySQL 中只有使用了 Innodb数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

事务用来管理insert,update,delete 语句

注意

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显示地开启一个事务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句

 BEGIN或START TRANSACTION；显示地开启一个事务；

 COMMIT；提交事务，也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；

 ROLLBACK；放弃事务，有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

 SAVEPOINT identifier；保存点，SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT，发生在保存点之前的事务被提交，之后的被忽略；

 RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

 ROLLBACK TO identifier；把事务回滚到标记点；

 SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

MySQL事务处理主要有两种方法

 使用BEGIN（开始事务）、ROLLBACK（事务回滚），COMMIT（事务提交）实现

 直接使用SET来改变MySQL的自动提交方式

 SET AUTOCOMMIT=0 禁止自动提交

 SET AUTOCOMMIT=1 开启自动提交

事务锁定模式

系统默认：不需要等待某事务结束，可直接查询到结果，但不能再进行修改、删除。

缺点：查询到的结果，可能是已经过期的数据。

优点：不需要等待某事务结束，可直接查询到结果。

需要用以下模式来设定锁定模式

1、SELECT …… LOCK INSHARE MODE（共享锁）

会话事务中查找的数据，加上一个共享锁。若会话事务中查找的数据已经被其他会话事务加上独占锁的话，共享锁会等待其结束再加，若等待时间过长就会显示事务需要的锁等待超时。

2、SELECT …… FOR UPDATE（排它锁）

例如 SELECT * FROM tablename WHERE id<200

那么id<200的数据，被查询到的数据，都将不能再进行修改、删除、SELECT …… LOCK IN SHARE MODE操作

一直到此事务结束

共享锁和 排它锁 的区别：在于是否阻断其他客户发出的SELECT …… LOCK IN SHARE MODE命令

3、INSERT / UPDATE /DELETE

所有关联数据都会被锁定，加上排它锁

4、防插入锁

例如 SELECT * FROM tablename WHERE id>200

那么id>200的记录无法被插入

5、死锁

自动识别死锁

先进来的进程被执行，后来的进程收到出错消息，并按ROLLBACK方式回滚

innodb_lock_wait_timeout = n 来设置最长等待时间，默认是50秒

事务隔离模式

1、READ UNCOMMITED

SELECT的时候允许脏读，即SELECT会读取其他事务修改而还没有提交的数据。

2、READ COMMITED

SELECT的时候无法重复读，即同一个事务中两次执行同样的查询语句，若在第一次与第二次查询之间时间段，其他事务又刚好修改了其查询的数据且提交了，则两次读到的数据不一致。

3、REPEATABLE READ

SELECT的时候可以重复读，即同一个事务中两次执行同样的查询语句，得到的数据始终都是一致的。

 

4、SERIALIZABLE

与可重复读的唯一区别是，默认把普通的SELECT语句改成SELECT …. LOCK IN SHARE MODE。即为查询语句涉及到的数据加上共享琐，阻塞其他事务修改真实数据。

语句实例

mysql> use RUNOOB;

Database changed

mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表

Query OK, 0 rows affected (0.04 sec)

 

mysql> select * from runoob_transaction_test;

Empty set (0.01 sec)

 

mysql> begin;  # 开始事务

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into runoob_transaction_test value(5);

Query OK, 1 rows affected (0.01 sec)

 

mysql> insert into runoob_transaction_test value(6);

Query OK, 1 rows affected (0.00 sec)

 

mysql> commit; # 提交事务

Query OK, 0 rows affected (0.01 sec)

 

mysql>  select * from runoob_transaction_test;

+------+

| id   |

+------+

| 5    |

| 6    |

+------+

2 rows in set (0.01 sec)

 

mysql> begin;    # 开始事务

Query OK, 0 rows affected (0.00 sec)

 

mysql>  insert into runoob_transaction_test values(7);

Query OK, 1 rows affected (0.00 sec)

 

mysql> rollback;   # 回滚

Query OK, 0 rows affected (0.00 sec)

 

mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入

+------+

| id   |

+------+

| 5    |

| 6    |

+------+

2 rows in set (0.01 sec)

 

mysql>