数据库事务的几个特性
做一个转账的功能
张三 李四
1000 1000
张三给李四转500
从张三的用户扣掉500
m1(){
update set zs_account = zs_account-500; 500
停电了
update set ls_account = ls_account+500; 1500
}
ACID
原子性 指的是一批逻辑上都操作要么同时成功 要么同时失败
一致性 事务执行之前与事务执行之后 总的数据保持一致
隔离性 一个事务的执行不影响另一个事务(A事务执行的时候 不会影响到B事务执行的数据)
持久性 事务一旦提交之后 就永久的保存到数据库中 不能回滚
事务的基本要素(ACID)
1、原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节。事务执行过程中出错,会回滚到事务开始前的状态,所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体,就像化学中学过的原子,是物质构成的基本单位。
2、一致性(Consistency):事务开始前和结束后,数据库的完整性约束没有被破坏 。比如A向B转账,不可能A扣了钱,B却没收到。
3、隔离性(Isolation):同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱,在A取钱的过程结束前,B不能向这张卡转账。
4、持久性(Durability):事务完成后,事务对数据库的所有更新将被保存到数据库,不能回滚。
并发事务 引发的问题
1、脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据
2、不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果 不一致。
3、幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。
小结:不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表
隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 | |
---|---|---|---|---|
READ-UNCOMMITTED | N | N | N | 读取未提交内容 |
READ-COMMITTED(RC) | Y | N | N | 读取提交内容 |
REPEATABLE-READ(RR) | Y | Y | N | 可重读 |
SERIALIZABLE | Y | Y | Y | 可串行化 |
1.查看隔离级别
mysql> select @@global.tx_isolation,@@tx_isolation;
±----------------------±----------------+
| @@global.tx_isolation | @@tx_isolation |
±----------------------±----------------+
| REPEATABLE-READ | REPEATABLE-READ |
±----------------------±----------------+
1 row in set (0.00 sec)
2.修改隔离级别
mysql> set session tx_isolation = 'READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+------------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+------------------+
| REPEATABLE-READ | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)
3.在mysql中默认情况下是自动提交SQL语句
3.1查看是否自动提交
mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
sh1 row in set (0.00 sec)
3.2关闭自动提交
mysql> set autocommit = off;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
4.事务控制语句
4.1显示的开启一个事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
4.2提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
4.3回滚事务
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
5.测试事务的隔离级别
READ-UNCOMMITTED 级别:
5.1准备测试数据
DROP TABLE IF EXISTS `tbl_tx_test`;
CREATE TABLE `tbl_tx_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` varchar(32) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`salary` double DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tbl_tx_test
-- ----------------------------
INSERT INTO `tbl_tx_test` VALUES ('1', 'A', 'tom', '1800');
INSERT INTO `tbl_tx_test` VALUES ('2', 'B', 'jack', '2100');
INSERT INTO `tbl_tx_test` VALUES ('3', 'C', 'pony', null);
INSERT INTO `tbl_tx_test` VALUES ('4', 'B', 'tony', '3600');
INSERT INTO `tbl_tx_test` VALUES ('5', 'B', 'marry', '1900');
INSERT INTO `tbl_tx_test` VALUES ('6', 'C', 'tack', '1200');
INSERT INTO `tbl_tx_test` VALUES ('7', 'A', 'tick', null);
INSERT INTO `tbl_tx_test` VALUES ('8', 'B', 'clock', '2000');
INSERT INTO `tbl_tx_test` VALUES ('9', 'C', 'noah', '1500');
INSERT INTO `tbl_tx_test` VALUES ('10', 'C', 'jarvis', '1800');
5.2设置会话级别为:READ-UNCOMMITTED
mysql> set session tx_isolation = 'READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)
5.3查看当前会话的隔离级别
mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
5.4开启A会话事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
5.5会话A查询id为1的数据
mysql> select * from tbl_tx_test where id = 1;
+----+------+------+--------+
| id | type | name | salary |
+----+------+------+--------+
| 1 | A | tom | 1800 |
+----+------+------+--------+
1 row in set (0.00 sec)
5.6开启会话B事务
mysql> start transaction;
Query OK, 0 rows affected
5.7会话B修改id为1的记录, 将 salary 修改为 2000,但是并不提交
mysql> update tbl_tx_test set salary = 2000 where id = 1;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
5.8会话A再次查询 tbl_tx_test 表中 id 为1的记录
mysql> select * from tbl_tx_test where id = 1;
+----+------+------+--------+
| id | type | name | salary |
+----+------+------+--------+
| 1 | A | tom | 2000 |
+----+------+------+--------+
1 row in set (0.00 sec)
READ-COMMITTED:
5.9 修改 “会话 A” 的事务隔离级别
mysql> SET session tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)
6.0 查看当前会话的隔离级别
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)
6.1 会话 A” 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
6.2 “会话 A” 查询 tbl_tx_test 表中 id 为1的记录
mysql> SELECT * FROM tbl_tx_test WHERE id = 1;
+----+------+------+--------+
| id | type | name | salary |
+----+------+------+--------+
| 1 | A | tom | 1800 |
+----+------+------+--------+
1 row in set (0.00 sec)
6.3 “会话 B” 开启事务
mysql> start transaction;
Query OK, 0 rows affected
6.4 “会话 B” 修改 id 为1的记录, 将 salary 修改为 2000,但是并不提交
mysql> UPDATE tbl_tx_test SET salary = 2000 WHERE id = 1;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
6.5“会话 A” 再次查询 tbl_tx_test 表中 id 为1的记录, 数据并没有发生变化, 因为 “会话 B” 并未提交
mysql> SELECT * FROM tbl_tx_test WHERE id = 1;
+----+------+------+--------+
| id | type | name | salary |
+----+------+------+--------+
| 1 | A | tom | 1800 |
+----+------+------+--------+
1 row in set (0.00 sec)
6.6“会话 B” 提交
mysql> commit;
Query OK, 0 rows affected
6.7“会话 A” 再次查询 tbl_tx_test 表中 id 为1的记录
mysql> SELECT * FROM tbl_tx_test WHERE id = 1;
+----+------+------+--------+
| id | type | name | salary |
+----+------+------+--------+
| 1 | A | tom | 2000 |
+----+------+------+--------+
1 row in set (0.00 sec)
REPEATABLE-READ级别
6.8 设置事务的隔离级别
SET session tx_isolation='repeatable-read';
6.9 会话 A” 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
7.0 会话 A” 查询 tbl_tx_test 表中 id 为1的记录
mysql> SELECT * FROM tbl_tx_test WHERE id = 1;
+----+------+------+--------+
| id | type | name | salary |
+----+------+------+--------+
| 1 | A | tom | 1800 |
+----+------+------+--------+
1 row in set (0.00 sec)
7.1会话 B” 开启事务
mysql> start transaction;
Query OK, 0 rows affected
7.2 “会话 B” 修改 id 为1的记录, 将 salary 修改为 2000, 但是并不提交
mysql> UPDATE tbl_tx_test SET salary = 2000 WHERE id = 1;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
7.3 “会话 A” 再次查询 tbl_tx_test 表中 id 为1的记录, 数据并没有发生变化
mysql> SELECT * FROM tbl_tx_test WHERE id = 1;
+----+------+------+--------+
| id | type | name | salary |
+----+------+------+--------+
| 1 | A | tom | 1800 |
+----+------+------+--------+
1 row in set (0.00 sec)
7.4 “会话 B” 提交
mysql> commit;
Query OK, 0 rows affected
7.5“会话 A” 再次查询 tbl_tx_test 表中 id 为1的记录, 数据仍没有发生变化
mysql> SELECT * FROM tbl_tx_test WHERE id = 1;
+----+------+------+--------+
| id | type | name | salary |
+----+------+------+--------+
| 1 | A | tom | 1800 |
+----+------+------+--------+
1 row in set (0.00 sec)
SERIALIZABLE级别:
7.6 修改 “会话 A” 的事务隔离级别
mysql> SET session tx_isolation='serializable';
Query OK, 0 rows affected (0.00 sec)
7.7 “会话 A” 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
7.8“会话 A” 查询 tbl_tx_test 表中 id 为1的记录
mysql> SELECT * FROM tbl_tx_test WHERE id = 1;
+----+------+------+--------+
| id | type | name | salary |
+----+------+------+--------+
| 1 | A | tom | 1800 |
+----+------+------+--------+
7.9“会话 B” 开启事务
mysql> start transaction;
Query OK, 0 rows affected
8.0“会话 B” 修改 id 为1的记录,将 salary 修改为 2000,由于 “会话 A” 事务没有提交,“会话 B” 的事务一直处于等待状态,直到超时
mysql> UPDATE tbl_tx_test set salary = 2000 WHERE id = 1;
1205 - Lock wait timeout exceeded; try restarting transaction