mysql事務是指對資料庫執行一批操作,在同一個事務當中,這些操作最終要么全部執行成功,要么全部失敗,不會存在部分成功的情況;事務是一個原子操作,是一個最小執行單元,可以由一個或多個SQL語句組成。
本教學操作環境:Windows10系統、MySQL5.7版本、Dell G3電腦。
資料庫中的事務是指對資料庫執行一批操作,在同一個事務當中,這些操作最終要麼全部執行成功,要麼全部失敗,不會存在部分成功的情況。
舉例:
例如A用戶給B用戶轉帳100操作,流程如下:
如果在交易的支援下,上面最終只有2種結果:
如果沒有事務的支持,可能發生錯:A帳戶減少了100,此時系統掛了,導致B帳戶沒有加上100,而A帳戶憑空少了100。
事務的整個過程如原子操作一樣,最終要麼全部成功,或全部失敗,這個原子性是從最終結果來看的,從最終結果來看這個過程是不可分割的。
一個交易必須使資料庫從一個一致性狀態轉換到另一個一致性狀態。
首先回顧一下一致性的定義。所謂一致性,指的是資料處於一種有意義的狀態,這種狀態是語意上的而不是語法上的。最常見的例子是轉帳。例如從帳戶A轉一筆錢到帳戶B上,如果帳戶A上的錢減少了,而帳戶B上的錢卻沒有增加,那麼我們認為此時資料處於不一致的狀態。
從這段話的理解來看,所謂一致性,即,從實際的業務邏輯上來說,最終結果是對的、是跟程式設計師的所期望的結果完全符合的
一個交易的執行不能被其他交易幹擾。即一個事務內部的操作及使用的資料對並發的其他事務是隔離的,並發執行的各個事務之間不能互相干擾。
隱含交易和明確交易。
mysql> show variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | ON |+---------------+-------+1 row in set, 1 warning (0.00 sec)autocommit為ON表示開啟了自動提交。
//设置不自动提交事务set autocommit=0;//执行事务操作commit|rollback;範例1:提交交易操作,如下:
mysql> create table test1 (a int);Query OK, 0 rows affected (0.01 sec)mysql> select * from test1;Empty set (0.00 sec)mysql> set autocommit=0;Query OK, 0 rows affected (0.00 sec)mysql> insert into test1 values(1);Query OK, 1 row affected (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 |+------+1 row in set (0.00 sec)範例2:回滾交易操作,如下:
mysql> set autocommit=0;Query OK, 0 rows affected (0.00 sec)mysql> insert into test1 values(2);Query OK, 1 row affected (0.00 sec)mysql> rollback;Query OK, 0 rows affected (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 |+------+1 row in set (0.00 sec)可以看到上面資料回滾了。 我們把autocommit還原回去:
mysql> set autocommit=1;Query OK, 0 rows affected (0.00 sec)
start transaction;//开启事务//执行事务操作commit|rollback;範例1:提交交易操作,如下:
mysql> select * from test1;+------+| a |+------+| 1 |+------+1 row in set (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into test1 values (2);Query OK, 1 row affected (0.00 sec)mysql> insert into test1 values (3);Query OK, 1 row affected (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 || 2 || 3 |+------+3 rows in set (0.00 sec)
上面成功插入了2条数据。
示例2:回滚事务操作,如下:
mysql> select * from test1;+------+| a |+------+| 1 || 2 || 3 |+------+3 rows in set (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> delete from test1;Query OK, 3 rows affected (0.00 sec)mysql> rollback;Query OK, 0 rows affected (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 || 2 || 3 |+------+3 rows in set (0.00 sec)
上面事务中我们删除了test1的数据,显示删除了3行,最后回滚了事务。
在事务中我们执行了一大批操作,可能我们只想回滚部分数据,怎么做呢?
我们可以将一大批操作分为几个部分,然后指定回滚某个部分。可以使用savepoin来实现,效果如下:
先清除test1表数据:
mysql> delete from test1;Query OK, 3 rows affected (0.00 sec)mysql> select * from test1;Empty set (0.00 sec)
演示savepoint效果,认真看:
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into test1 values (1);Query OK, 1 row affected (0.00 sec)mysql> savepoint part1;//设置一个保存点Query OK, 0 rows affected (0.00 sec)mysql> insert into test1 values (2);Query OK, 1 row affected (0.00 sec)mysql> rollback to part1;//将savepint = part1的语句到当前语句之间所有的操作回滚Query OK, 0 rows affected (0.00 sec)mysql> commit;//提交事务Query OK, 0 rows affected (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 |+------+1 row in set (0.00 sec)
从上面可以看出,执行了2次插入操作,最后只插入了1条数据。
savepoint需要结合rollback to sp1一起使用,可以将保存点sp1到rollback to之间的操作回滚掉。
表示在事务中执行的是一些只读操作,如查询,但是不会做insert、update、delete操作,数据库内部对只读事务可能会有一些性能上的优化。
用法如下:
start transaction read only;
示例:
mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> start transaction read only;Query OK, 0 rows affected (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 || 1 |+------+2 rows in set (0.00 sec)mysql> delete from test1;ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 || 1 |+------+2 rows in set (0.00 sec)
只读事务中执行delete会报错。
这些问题主要是基于数据在多个事务中的可见性来说的。也是并发事务产生的问题。
丢失更新就是两个不同的事务(或者Java程序线程)在某一时刻对同一数据进行读取后,先后进行修改。导致第一次操作数据丢失。
「
第一类丢失更新 :A,B 事务同时操作同一数据,A先对改数据进行了更改,B再次更改时失败然后回滚,把A更新的数据也回滚了。(事务撤销造成的撤销丢失)
第二类丢失更新:A,B 事务同时操作同一数据,A先对改数据进行了更改,B再次更改并且提交,把A提交的数据给覆盖了。(事务提交造成的覆盖丢失)
」
一个事务在执行的过程中读取到了其他事务还没有提交的数据。 这个还是比较好理解的。
「
两个事务同时操作同一数据,A事务对该数据进行了修改还没提交的时候,B事务访问了该条事务,并且使用了该数据,此时A事务回滚,那么B事务读到的就是脏数据。
比如事务1,修改了某个数据 事务2,刚好访问了事务1修改后的数据
此时事务1,回滚了操作 事务2,读到还是回滚前的数据
」
从字面上我们就可以理解,即一个事务操作过程中可以读取到其他事务已经提交的数据。
事务中的每次读取操作,读取到的都是数据库中其他事务已提交的最新的数据(相当于当前读)
在同一事务中,多次读取同一数据返回的结果有所不同,换句话说,后续读取可以读到另一事务已提交的更新数据。相反,“可重复读” 在同一事务中多次读取数据时, 能够保证所读数据一样, 也就是后续读取不能读到另一事务已提交的更新数据。
「
这种情况发生 在一个事务内多次读同一数据。A事务查询某条数据,该事务未结束时,B事务也访问同一数据并进行了修改。那么在A事务中的两 次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可能是不一样的。
事务1,查询某个数据 事务2,修改了某个数据,提交
事务1,再次查询这个数据
这样事务1两次查询的数据不一样,称为不可重复读
」
一个事务操作中对于一个读取操作不管多少次,读取到的结果都是一样的。
脏读、不可重复读、可重复读、幻读,其中最难理解的是幻读
以mysql为例:
幻读现象例子:
看第二种解释:
如果还是理解不了的,继续向下看,后面后详细的演示。
当多个事务同时进行的时候,如何确保当前事务中数据的正确性,比如A、B两个事物同时进行的时候,A是否可以看到B已提交的数据或者B未提交的数据,这个需要依靠事务的隔离级别来保证,不同的隔离级别中所产生的效果是不一样的。
事务隔离级别主要是解决了上面多个事务之间数据可见性及数据正确性的问题。(或者说为了解决并发控制可能产生的异常问题,数据库定义了四种事务的隔离级别)
隔离级别分为4种:
上面4中隔离级别越来越强,会导致数据库的并发性也越来越低。
mysql> show variables like 'transaction_isolation';+-----------------------+----------------+| Variable_name | Value |+-----------------------+----------------+| transaction_isolation | READ-COMMITTED |+-----------------------+----------------+1 row in set, 1 warning (0.00 sec)
分2步骤,修改文件、重启mysql,如下:
修改mysql中的my.init文件,我们将隔离级别设置为:READ-UNCOMMITTED,如下:
# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行transaction-isolation=READ-UNCOMMITTED
以管理员身份打开cmd窗口,重启mysql,如下:
C:\Windows\system32>net stop mysql mysql 服务正在停止..mysql 服务已成功停止。 C:\Windows\system32>net start mysql mysql 服务正在启动 .mysql 服务已经启动成功。
隔离级别 | 脏读可能性 | 不可重复读可能性 | 幻读可能性 |
---|---|---|---|
READ-UNCOMMITTED | 有 | 有 | 有 |
READ-COMMITTED | 无 | 有 | 有 |
REPEATABLE-READ | 无 | 无 | 有 |
SERIALIZABLE | 无 | 无 | 无 |
下面我们来演示一下,各种隔离级别中可见性的问题,开启两个窗口,叫做A、B窗口,两个窗口中登录mysql。
将隔离级别置为READ-UNCOMMITTED:
# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行transaction-isolation=READ-UNCOMMITTED
重启mysql:
C:\Windows\system32>net stop mysql mysql 服务正在停止..mysql 服务已成功停止。 C:\Windows\system32>net start mysql mysql 服务正在启动 .mysql 服务已经启动成功。
查看隔离级别:
mysql> show variables like 'transaction_isolation';+-----------------------+----------------+| Variable_name | Value |+-----------------------+----------------+| transaction_isolation | READ-UNCOMMITTED |+-----------------------+----------------+1 row in set, 1 warning (0.00 sec)
先清空test1表数据:
delete from test1;select * from test1;
按时间顺序在2个窗口中执行下面操作:
时间 | 窗口A | 窗口B |
---|---|---|
T1 | start transaction; | |
T2 | select * from test1; | |
T3 | start transaction; | |
T4 | insert into test1 values (1); | |
T5 | select * from test1; | |
T6 | select * from test1; | |
T7 | commit; | |
T8 | commit; |
A窗口如下:
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from test1;Empty set (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 |+------+1 row in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)
B窗口如下:
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into test1 values (1);Query OK, 1 row affected (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 |+------+1 row in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)
看一下:
T2-A:无数据,T6-A:有数据,T6时刻B还未提交,此时A已经看到了B插入的数据,说明出现了脏读。
T2-A:无数据,T6-A:有数据,查询到的结果不一样,说明不可重复读。
结论:读未提交情况下,可以读取到其他事务还未提交的数据,多次读取结果不一样,出现了脏读、不可重复读、幻读
将隔离级别置为READ-COMMITTED
# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行transaction-isolation=READ-COMMITTED
重启mysql:
C:\Windows\system32>net stop mysql mysql 服务正在停止..mysql 服务已成功停止。 C:\Windows\system32>net start mysql mysql 服务正在启动 .mysql 服务已经启动成功。
查看隔离级别:
mysql> show variables like 'transaction_isolation';+-----------------------+----------------+| Variable_name | Value |+-----------------------+----------------+| transaction_isolation | READ-COMMITTED |+-----------------------+----------------+1 row in set, 1 warning (0.00 sec)
先清空test1表数据:
delete from test1;select * from test1;
按时间顺序在2个窗口中执行下面操作:
时间 | 窗口A | 窗口B |
---|---|---|
T1 | start transaction; | |
T2 | select * from test1; | |
T3 | start transaction; | |
T4 | insert into test1 values (1); | |
T5 | select * from test1; | |
T6 | select * from test1; | |
T7 | commit; | |
T8 | select * from test1; | |
T9 | commit; |
A窗口如下:
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from test1;Empty set (0.00 sec)mysql> select * from test1;Empty set (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 |+------+1 row in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)
B窗口如下:
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into test1 values (1);Query OK, 1 row affected (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 |+------+1 row in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)
看一下:
T5-B:有数据,T6-A窗口:无数据,A看不到B的数据,说明没有脏读。
T6-A窗口:无数据,T8-A:看到了B插入的数据,此时B已经提交了,A看到了B已提交的数据,说明可以读取到已提交的数据。
T2-A、T6-A:无数据,T8-A:有数据,多次读取结果不一样,说明不可重复读。
结论:读已提交情况下,无法读取到其他事务还未提交的数据,可以读取到其他事务已经提交的数据,多次读取结果不一样,未出现脏读,出现了读已提交、不可重复读、幻读
将隔离级别置为REPEATABLE-READ
# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行transaction-isolation=REPEATABLE-READ
重启mysql:
C:\Windows\system32>net stop mysql mysql 服务正在停止..mysql 服务已成功停止。 C:\Windows\system32>net start mysql mysql 服务正在启动 .mysql 服务已经启动成功。
查看隔离级别:
mysql> show variables like 'transaction_isolation';+-----------------------+----------------+| Variable_name | Value |+-----------------------+----------------+| transaction_isolation | REPEATABLE-READ |+-----------------------+----------------+1 row in set, 1 warning (0.00 sec)
先清空test1表数据:
delete from test1;select * from test1;
按时间顺序在2个窗口中执行下面操作:
时间 | 窗口A | 窗口B |
---|---|---|
T1 | start transaction; | |
T2 | select * from test1; | |
T3 | start transaction; | |
T4 | insert into test1 values (1); | |
T5 | select * from test1; | |
T6 | select * from test1; | |
T7 | commit; | |
T8 | select * from test1; | |
T9 | commit; | |
T10 | select * from test1; |
A窗口如下:
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from test1;Empty set (0.00 sec)mysql> select * from test1;Empty set (0.00 sec)mysql> select * from test1;Empty set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 || 1 |+------+2 rows in set (0.00 sec)
B窗口如下:
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into test1 values (1);Query OK, 1 row affected (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 || 1 |+------+2 rows in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)
看一下:
T2-A、T6-A窗口:无数据,T5-B:有数据,A看不到B的数据,说明没有脏读。
T8-A:无数据,此时B已经提交了,A看不到B已提交的数据,A中3次读的结果一样都是没有数据的,说明可重复读。
结论:可重复读情况下,未出现脏读,未读取到其他事务已提交的数据,多次读取结果一致,即可重复读。
将隔离级别置为REPEATABLE-READ
# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行transaction-isolation=REPEATABLE-READ
重启mysql:
C:\Windows\system32>net stop mysql mysql 服务正在停止..mysql 服务已成功停止。 C:\Windows\system32>net start mysql mysql 服务正在启动 .mysql 服务已经启动成功。
查看隔离级别:
mysql> show variables like 'transaction_isolation';+-----------------------+----------------+| Variable_name | Value |+-----------------------+----------------+| transaction_isolation | REPEATABLE-READ |+-----------------------+----------------+1 row in set, 1 warning (0.00 sec)
准备数据:
mysql> create table t_user(id int primary key,name varchar(16) unique key);Query OK, 0 rows affected (0.01 sec)mysql> insert into t_user values (1,'路人甲Java'),(2,'路人甲Java');ERROR 1062 (23000): Duplicate entry '路人甲Java' ***\*for\**** key 'name'mysql> select * from t_user;Empty set (0.00 sec)
上面我们创建t_user表,name添加了唯一约束,表示name不能重复,否则报错。
按时间顺序在2个窗口中执行下面操作:
时间 | 窗口A | 窗口B |
---|---|---|
T1 | start transaction; | |
T2 | start transaction; | |
T3 | – 插入路人甲Java insert into t_user values (1,‘路人甲Java’); |
|
T4 | select * from t_user; | |
T5 | – 查看路人甲Java是否存在 select * from t_user where name=‘路人甲Java’; |
|
T6 | commit; | |
T7 | – 插入路人甲Java insert into t_user values (2,‘路人甲Java’); |
|
T8 | – 查看路人甲Java是否存在 select * from t_user where name=‘路人甲Java’; |
|
T9 | commit; |
A窗口如下:
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from t_user where name='路人甲Java';Empty set (0.00 sec)mysql> insert into t_user values (2,'路人甲Java');ERROR 1062 (23000): Duplicate entry '路人甲Java' ***\*for\**** key 'name'mysql> select * from t_user where name='路人甲Java';Empty set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)
B窗口如下:
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into t_user values (1,'路人甲Java');Query OK, 1 row affected (0.00 sec)mysql> select * from t_user;+----+---------------+| id | name |+----+---------------+| 1 | 路人甲Java |+----+---------------+1 row in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)
看一下:
A想插入数据路人甲Java,插入之前先查询了一下(T5时刻)该用户是否存在,发现不存在,然后在T7时刻执行插入,报错了,报数据已经存在了,因为T6时刻B已经插入了路人甲Java。
然后A有点郁闷,刚才查的时候不存在的,然后A不相信自己的眼睛,又去查一次(T8时刻),发现路人甲Java还是不存在的。
此时A心里想:数据明明不存在啊,为什么无法插入呢?这不是懵逼了么,A觉得如同发生了幻觉一样。
SERIALIZABLE会让并发的事务串行执行(多个事务之间读写、写读、写写会产生互斥,效果就是串行执行,多个事务之间的读读不会产生互斥)。
读写互斥:事务A中先读取操作,事务B发起写入操作,事务A中的读取会导致事务B中的写入处于等待状态,直到A事务完成为止。
表示我开启一个事务,为了保证事务中不会出现上面说的问题(脏读、不可重复读、读已提交、幻读),那么我读取的时候,其他事务有修改数据的操作需要排队等待,等待我读取完成之后,他们才可以继续。
写读、写写也是互斥的,读写互斥类似。
这个类似于java中的java.util.concurrent.lock.ReentrantReadWriteLock类产生的效果。
下面演示读写互斥的效果。
将隔离级别置为SERIALIZABLE
# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行transaction-isolation=SERIALIZABLE
重启mysql:
C:\Windows\system32>net stop mysql mysql 服务正在停止..mysql 服务已成功停止。 C:\Windows\system32>net start mysql mysql 服务正在启动 .mysql 服务已经启动成功。
查看隔离级别:
mysql> show variables like 'transaction_isolation';+-----------------------+--------------+| Variable_name | Value |+-----------------------+--------------+| transaction_isolation | SERIALIZABLE |+-----------------------+--------------+1 row in set, 1 warning (0.00 sec)
先清空test1表数据:
delete from test1;select * from test1;
按时间顺序在2个窗口中执行下面操作:
时间 | 窗口A | 窗口B |
---|---|---|
T1 | start transaction; | |
T2 | select * from test1; | |
T3 | start transaction; | |
T4 | insert into test1 values (1); | |
T5 | commit; | |
T6 | commit; |
按時間順序執行上面的指令,會發現T4-B這樣會被阻塞,直到T5-A執行完畢。
上面這個示範的是讀寫互斥產生的效果,大家可以自己去寫一下寫讀、寫寫互斥的效果。
可以看出來,交易只能串列執行了。串行情況下不存在髒讀、不可重複讀、幻讀的問題了。
讀取未提交( Read Uncommitted )
讀取已提交( Read Committed )
可重複讀取( Repeatable Read )
可串列化( Serializable )
預設隔離級別:如果沒有指定隔離級別,資料庫就會使用預設的隔離級別。在MySQL中,如果使用 InnoDB,預設的隔離等級是Repeatable Read。
髒讀、不可重複讀取、幻讀
【相關推薦: mysql影片教學】
以上是mysql事務是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章!