1.事务的ACID属性
原子性(Atomicity):即事务的不可分割性,事务中的操作要么全做,要么全不做。
一致性(Consistency):一个事务在执行前后,数据必须处于正确的状态,满足完整性约束。
隔离性(Isolation):多个事务并发执行时,一个事务的执行不应该影响其他事务的执行。
持久性(Durability):事务处理完成后,对数据的修改就是永久的,即便系统故障也不会丢失。
2.对ACID的理解:
现在有一个银行用户甲,他有账户A和账户B,账户A里有500元,账户B里有0元
(1)原子性:
甲从账户A向账户B转账200元,该过程至少包含两个操作,账户A钱数减少,账户B钱数增加,增加和减少的操作要么全部成功要么全部失败,这就是原子性。
(2)一致性
甲从账户A向账户B转账200元,但是账户A扣款了200,账户B却只收款了50,这就违背了数据必须处于一个合理的状态,违背了一致性。
还有一个方面,即满足完整性约束,比如甲从账户A向账户B转账510元,并且我们给定账户余额这一列的约束时不能小于0的,那么很明显这条事务一定会执行失败,如果执行成功的话就会破坏数据库的约束,违背一致性。
(3)隔离性
还是拿转账来说,在账户A向账户B转账的整个过程中,只要事务还没有提交,查询A账户和B账户的时候,账户里面钱的数量都不会变化。
(4)持久性
持久性意味着事务在完成以后,该事务对数据库所作的更改便持久的保存在数据库之中,并不会因为系统故障而回滚。
3.事务的隔离级别
(1)读未提交
如果一个事务已经开始写数据,则另外一个事务不允许同时对该行数据进行写操作,但是允许其他事务读此行数据,比如事务B读到了事务A未提交的数据。
例(使用MariaDB):
Session1 Session2
set tx_isolation=‘READ-UNCOMMITTED’; set tx_isolation=‘READ-UNCOMMITTED’;
begin; begin;
select * from t1;
±-----±-----±-----±-----±-----+
| a | b | c | d | e |
±-----±-----±-----±-----±-----+
| 19 | 1 | 1 | 1 | 1 |
| 19 | 2 | 2 | 3 | 2 |
| 19 | 3 | 3 | 3 | 3 |
| 19 | 4 | 4 | 4 | 4 |
| 19 | 1 | 1 | 1 | 1 |
| 19 | NULL | NULL | NULL | NULL |
| 19 | NULL | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL | NULL |
| 22 | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----+
9 rows in set (0.000 sec)
select * from t1;
±-----±-----±-----±-----±-----+
| a | b | c | d | e |
±-----±-----±-----±-----±-----+
| 19 | 1 | 1 | 1 | 1 |
| 19 | 2 | 2 | 3 | 2 |
| 19 | 3 | 3 | 3 | 3 |
| 19 | 4 | 4 | 4 | 4 |
| 19 | 1 | 1 | 1 | 1 |
| 19 | NULL | NULL | NULL | NULL |
| 19 | NULL | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL | NULL |
| 22 | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----+
9 rows in set (0.000 sec)
insert into t1(a) values(31);
Query OK, 1 row affected (0.000 sec)
select * from t1;
±-----±-----±-----±-----±-----+
| a | b | c | d | e |
±-----±-----±-----±-----±-----+
| 19 | 1 | 1 | 1 | 1 |
| 19 | 2 | 2 | 3 | 2 |
| 19 | 3 | 3 | 3 | 3 |
| 19 | 4 | 4 | 4 | 4 |
| 19 | 1 | 1 | 1 | 1 |
| 19 | NULL | NULL | NULL | NULL |
| 19 | NULL | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL | NULL |
| 22 | NULL | NULL | NULL | NULL |
| 31 | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----+
10 rows in set (0.000 sec)
如上,session2事务读到了session1事务未提交的数据a=31;
(2)读已提交
如果一个事务已经开始写数据,则禁止其他事务读此行数据,比如,事务B读不到事务A未提交的数据。
例1:
Session1 Session2
set tx_isolation=‘READ-COMMITTED’; set tx_isolation=‘READ-COMMITTED’;
begin; begin;
select * from t1;
±-----±-----±-----±-----±-----+
| a | b | c | d | e |
±-----±-----±-----±-----±-----+
| 19 | 1 | 1 | 1 | 1 |
| 19 | 2 | 2 | 3 | 2 |
| 19 | 3 | 3 | 3 | 3 |
| 19 | 4 | 4 | 4 | 4 |
| 19 | 1 | 1 | 1 | 1 |
| 19 | NULL | NULL | NULL | NULL |
| 19 | NULL | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL | NULL |
| 22 | NULL | NULL | NULL | NULL |
| 31 | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----+
10 rows in set (0.000 sec)
insert into t1(a) values(32);
Query OK, 1 row affected (0.000 sec)
select * from t1;
±-----±-----±-----±-----±-----+
| a | b | c | d | e |
±-----±-----±-----±-----±-----+
| 19 | 1 | 1 | 1 | 1 |
| 19 | 2 | 2 | 3 | 2 |
| 19 | 3 | 3 | 3 | 3 |
| 19 | 4 | 4 | 4 | 4 |
| 19 | 1 | 1 | 1 | 1 |
| 19 | NULL | NULL | NULL | NULL |
| 19 | NULL | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL | NULL |
| 22 | NULL | NULL | NULL | NULL |
| 31 | NULL | NULL | NULL | NULL |
| 32 | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----+
11 rows in set (0.000 sec)
select * from t1;
±-----±-----±-----±-----±-----+
| a | b | c | d | e |
±-----±-----±-----±-----±-----+
| 19 | 1 | 1 | 1 | 1 |
| 19 | 2 | 2 | 3 | 2 |
| 19 | 3 | 3 | 3 | 3 |
| 19 | 4 | 4 | 4 | 4 |
| 19 | 1 | 1 | 1 | 1 |
| 19 | NULL | NULL | NULL | NULL |
| 19 | NULL | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL | NULL |
| 22 | NULL | NULL | NULL | NULL |
| 31 | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----+
10 rows in set (0.000 sec)
commit;
Query OK, 0 rows affected (0.035 sec)
MariaDB [test]> select * from t1;
±-----±-----±-----±-----±-----+
| a | b | c | d | e |
±-----±-----±-----±-----±-----+
| 19 | 1 | 1 | 1 | 1 |
| 19 | 2 | 2 | 3 | 2 |
| 19 | 3 | 3 | 3 | 3 |
| 19 | 4 | 4 | 4 | 4 |
| 19 | 1 | 1 | 1 | 1 |
| 19 | NULL | NULL | NULL | NULL |
| 19 | NULL | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL | NULL |
| 22 | NULL | NULL | NULL | NULL |
| 31 | NULL | NULL | NULL | NULL |
| 32 | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----+
11 rows in set (0.000 sec)
如上,当session1的事务(insert a=32)未提交时,session2事务读不到该条数据,只有当session1事务提交后,session2事务才能读到a=32。
例2:
Session1 Session2
set tx_isolation=‘READ-COMMITTED’;
Query OK, 0 rows affected (0.000 sec) set tx_isolation=‘READ-COMMITTED’;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> begin;
Query OK, 0 rows affected (0.000 sec) MariaDB [test]> begin;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> select * from t1;
±-----±-----±-----±-----±-----+
| a | b | c | d | e |
±-----±-----±-----±-----±-----+
| 19 | 1 | 1 | 1 | 1 |
| 19 | 2 | 2 | 3 | 2 |
| 19 | 3 | 3 | 3 | 3 |
| 19 | 4 | 4 | 4 | 4 |
| 19 | 1 | 1 | 1 | 1 |
| 19 | NULL | NULL | NULL | NULL |
| 19 | NULL | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL | NULL |
| 22 | NULL | NULL | NULL | NULL |
| 31 | NULL | NULL | NULL | NULL |
| 32 | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----+
11 rows in set (0.000 sec)
MariaDB [test]> update t1 set a=41 where a=‘19’;
Query OK, 7 rows affected (0.000 sec)
Rows matched: 7 Changed: 7 Warnings: 0 MariaDB [test]> update t1 set a=42 where a=‘10’;
Query OK, 1 row affected (0.000 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> update t1 set a=43 where a=‘19’;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [test]> commit;
Query OK, 0 rows affected (0.034 sec)
MariaDB [test]> update t1 set a=43 where a=‘41’;
Query OK, 7 rows affected (0.000 sec)
Rows matched: 7 Changed: 7 Warnings: 0
如上例,解释了“如果一个事务已经开始写数据,则禁止其他事务读此行数据”的“禁止其他事务读此行数据”是什么含义。Session1事务中update a=19的数据,那么“此行”就是指a=19的所有行,session2的事务就不能写a=19的所有行,于是执行update t1 set a=43 where a=‘19’;时报错:ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction,但是session2仍然可以访问a<>19的行,比如a=10,于是执行update t1 set a=42 where a=‘10’;成功。
(3)可重复读
如果一个事务已经开始写数据,则禁止其他事务读写该数据,这样就可以在同一个事务内两次读到的数据是一样的。比如,只有当B事务也已经提交时,才能读到A事务提交的数据,若B事务未提交,读到的是A事务提交前的数据。
例1:
Session1 Session2
set tx_isolation=‘REPEATABLE-READ’;
Query OK, 0 rows affected (0.000 sec) set tx_isolation=‘REPEATABLE-READ’;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> begin;
Query OK, 0 rows affected (0.000 sec) MariaDB [test]> begin;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> select * from t1;
±-----±-----±-----±-----±-----+
| a | b | c | d | e |
±-----±-----±-----±-----±-----+
| 43 | 1 | 1 | 1 | 1 |
| 43 | 2 | 2 | 3 | 2 |
| 43 | 3 | 3 | 3 | 3 |
| 43 | 4 | 4 | 4 | 4 |
| 43 | 1 | 1 | 1 | 1 |
| 43 | NULL | NULL | NULL | NULL |
| 43 | NULL | NULL | NULL | NULL |
| 42 | NULL | NULL | NULL | NULL |
| 22 | NULL | NULL | NULL | NULL |
| 31 | NULL | NULL | NULL | NULL |
| 32 | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----+
11 rows in set (0.000 sec) MariaDB [test]> select * from t1;
±-----±-----±-----±-----±-----+
| a | b | c | d | e |
±-----±-----±-----±-----±-----+
| 43 | 1 | 1 | 1 | 1 |
| 43 | 2 | 2 | 3 | 2 |
| 43 | 3 | 3 | 3 | 3 |
| 43 | 4 | 4 | 4 | 4 |
| 43 | 1 | 1 | 1 | 1 |
| 43 | NULL | NULL | NULL | NULL |
| 43 | NULL | NULL | NULL | NULL |
| 42 | NULL | NULL | NULL | NULL |
| 22 | NULL | NULL | NULL | NULL |
| 31 | NULL | NULL | NULL | NULL |
| 32 | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----+
11 rows in set (0.000 sec)
MariaDB [test]> insert into t1(a) values(33);
Query OK, 1 row affected (0.000 sec) MariaDB [test]> insert into t1(a) values(34);
Query OK, 1 row affected (0.000 sec)
MariaDB [test]> select * from t1;
±-----±-----±-----±-----±-----+
| a | b | c | d | e |
±-----±-----±-----±-----±-----+
| 43 | 1 | 1 | 1 | 1 |
| 43 | 2 | 2 | 3 | 2 |
| 43 | 3 | 3 | 3 | 3 |
| 43 | 4 | 4 | 4 | 4 |
| 43 | 1 | 1 | 1 | 1 |
| 43 | NULL | NULL | NULL | NULL |
| 43 | NULL | NULL | NULL | NULL |
| 42 | NULL | NULL | NULL | NULL |
| 22 | NULL | NULL | NULL | NULL |
| 31 | NULL | NULL | NULL | NULL |
| 32 | NULL | NULL | NULL | NULL |
| 33 | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----+
12 rows in set (0.000 sec)
MariaDB [test]> commit;
Query OK, 0 rows affected (0.044 sec)
MariaDB [test]> select * from t1;
±-----±-----±-----±-----±-----+
| a | b | c | d | e |
±-----±-----±-----±-----±-----+
| 43 | 1 | 1 | 1 | 1 |
| 43 | 2 | 2 | 3 | 2 |
| 43 | 3 | 3 | 3 | 3 |
| 43 | 4 | 4 | 4 | 4 |
| 43 | 1 | 1 | 1 | 1 |
| 43 | NULL | NULL | NULL | NULL |
| 43 | NULL | NULL | NULL | NULL |
| 42 | NULL | NULL | NULL | NULL |
| 22 | NULL | NULL | NULL | NULL |
| 31 | NULL | NULL | NULL | NULL |
| 32 | NULL | NULL | NULL | NULL |
| 33 | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----+
12 rows in set (0.000 sec)
MariaDB [test]> commit;
Query OK, 0 rows affected (0.030 sec)
MariaDB [test]> select * from t1;
±-----±-----±-----±-----±-----+
| a | b | c | d | e |
±-----±-----±-----±-----±-----+
| 43 | 1 | 1 | 1 | 1 |
| 43 | 2 | 2 | 3 | 2 |
| 43 | 3 | 3 | 3 | 3 |
| 43 | 4 | 4 | 4 | 4 |
| 43 | 1 | 1 | 1 | 1 |
| 43 | NULL | NULL | NULL | NULL |
| 43 | NULL | NULL | NULL | NULL |
| 42 | NULL | NULL | NULL | NULL |
| 22 | NULL | NULL | NULL | NULL |
| 31 | NULL | NULL | NULL | NULL |
| 32 | NULL | NULL | NULL | NULL |
| 33 | NULL | NULL | NULL | NULL |
| 34 | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----+
13 rows in set (0.000 sec)
如上,当session2事务已提交insert a=34,但session1事务未提交时,session1仍读不到session2提交的数据,只有等待session1也提交后才能读到session2提交的数据。
例2:
Session1 Session2
set tx_isolation=‘REPEATABLE-READ’;
Query OK, 0 rows affected (0.000 sec) set tx_isolation=‘REPEATABLE-READ’;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> begin;
Query OK, 0 rows affected (0.000 sec) MariaDB [test]> begin;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> select * from t1;
±-----±-----±-----±-----±-----+
| a | b | c | d | e |
±-----±-----±-----±-----±-----+
| 45 | 1 | 1 | 1 | 1 |
| 45 | 2 | 2 | 3 | 2 |
| 45 | 3 | 3 | 3 | 3 |
| 45 | 4 | 4 | 4 | 4 |
| 45 | 1 | 1 | 1 | 1 |
| 45 | NULL | NULL | NULL | NULL |
| 45 | NULL | NULL | NULL | NULL |
| 42 | NULL | NULL | NULL | NULL |
| 22 | NULL | NULL | NULL | NULL |
| 31 | NULL | NULL | NULL | NULL |
| 32 | NULL | NULL | NULL | NULL |
| 33 | NULL | NULL | NULL | NULL |
| 34 | NULL | NULL | NULL | NULL |
| 36 | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----+
14 rows in set (0.000 sec)
MariaDB [test]> update t1 set a=46 where a=‘45’;
Query OK, 7 rows affected (0.000 sec)
Rows matched: 7 Changed: 7 Warnings: 0
MariaDB [test]> update t1 set a=46 where a=‘45’;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [test]> update t1 set a=47 where a=‘22’;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [test]> commit;
Query OK, 0 rows affected (0.041 sec)
MariaDB [test]> update t1 set a=46 where a=‘46’;
Query OK, 0 rows affected (0.000 sec)
Rows matched: 7 Changed: 0 Warnings: 0
MariaDB [test]> update t1 set a=47 where a=‘22’;
Query OK, 1 row affected (0.000 sec)
Rows matched: 1 Changed: 1 Warnings: 0
与读已提交的测试2中不同的是,session2既不能写session1事务update命中的行,也不能写update未命中的行。
(4)序列化
允许读并发,但不允许读写或写写并发。
允许读并发:
Session1 Session2
set tx_isolation=‘SERIALIZABLE’; set tx_isolation=‘SERIALIZABLE’;
MariaDB [test]> begin;
Query OK, 0 rows affected (0.000 sec) MariaDB [test]> begin;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> select * from t1;
±-----±-----±-----±-----±-----+
| a | b | c | d | e |
±-----±-----±-----±-----±-----+
| 47 | 1 | 1 | 1 | 1 |
| 47 | 2 | 2 | 3 | 2 |
| 47 | 3 | 3 | 3 | 3 |
| 47 | 4 | 4 | 4 | 4 |
| 47 | 1 | 1 | 1 | 1 |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----+
14 rows in set (0.000 sec)
MariaDB [test]> select * from t1;
±-----±-----±-----±-----±-----+
| a | b | c | d | e |
±-----±-----±-----±-----±-----+
| 47 | 1 | 1 | 1 | 1 |
| 47 | 2 | 2 | 3 | 2 |
| 47 | 3 | 3 | 3 | 3 |
| 47 | 4 | 4 | 4 | 4 |
| 47 | 1 | 1 | 1 | 1 |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----+
14 rows in set (0.000 sec)
不允许读写并发:
Session1 Session2
set tx_isolation=‘SERIALIZABLE’; set tx_isolation=‘SERIALIZABLE’;
MariaDB [test]> begin;
Query OK, 0 rows affected (0.000 sec) MariaDB [test]> begin;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> select * from t1;
±-----±-----±-----±-----±-----+
| a | b | c | d | e |
±-----±-----±-----±-----±-----+
| 47 | 1 | 1 | 1 | 1 |
| 47 | 2 | 2 | 3 | 2 |
| 47 | 3 | 3 | 3 | 3 |
| 47 | 4 | 4 | 4 | 4 |
| 47 | 1 | 1 | 1 | 1 |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
| 47 | NULL | NULL | NULL | NULL |
±-----±-----±-----±-----±-----+
14 rows in set (0.000 sec)
MariaDB [test]> insert into t1(a) values(29);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [test]> commit;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> insert into t1(a) values(29);
Query OK, 1 row affected (0.000 sec)
4.什么是脏读、不可重复读和幻读
脏读:隔离级别为读未提交时,事务A读到了事务B未提交的数据。
不可重复读:隔离级别为读已提交时,事务A在两次读操作期间(事务A未提交),事务B插入了一条数据且已提交,那么事务A两次读到的数据不一样。
幻读:隔离级别为可重复读时,事务A在提交前,事务B插入了一条数据且已提交,但是事务A在提交前看不到这条数据,只有当事务A也提交后,才能看到该条数据。
读已提交解决了脏读问题,可重复读解决了脏读、不可重复读问题,序列化解决了脏读、不可重复读、幻读问题。