轻松获知数据库事务
标题索引
为围场等地区用户提供了全套网页设计制作服务,及围场网站建设行业解决方案。主营业务为成都做网站、网站设计、围场网站设计,以传统方式定制建设网站,并提供域名空间备案等一条龙服务,秉承以专业、用心的态度为用户提供真诚的服务。我们深信只要达到每一位用户的要求,就会得到认可,从而选择与我们长期合作。这样,我们也可以走得更远!
事务作用
事务流程
隔离级别
实例验证
事务作用
事务是确保数据库系统数据的完整性的功能,如现在互联网行业支付业务,不管服务器出于什么原因异常中断,客户要么支付成功要么支付不成功,支付成功数据库金额即会发生变化,支付不成功客户的金额就不发生变化,确保了交易业务的稳定性。支持事物的引擎必须满足ACID,满足ACID后才能满足事物,另外事物的回滚或恢复主要靠事物日志来完成,ACID含义分别如下:
A:atomicity(原子性):整个事物中所有的操作为命令执行最小单元,全部执行、执行一半失败回滚或失败回滚;
C:consistency(一致性):数据库从一个状态转化为另外一个状态,状态在转化前和转换后一致;
I:isolation(隔离性):一个事物所做出的操作在提交之前,是不能被其他所见,因此隔离就出现多种隔离级别,具体包括read-uncommitted读为提交、read-committed读提交、repeatable-read可重复读和serializable串行化;
D:durability(持久性):一旦事物提交,所做的会永久性保存数据库中。
事务流程
事务的工作流程具体可见下图
图1-1 事物工作流程
由上图可知,当数据库通过start transaction启动一个事物,启动事物后对数据库进行一系列的操作,最后提交事物,提交事物又有两种,第一种为commit提交,第二种rollback回滚,一旦提交事物数据库即处于新的状态保持持久性。另外在防止数据库在事物提交后数据从内存写入磁盘时,操作系统异常掉电导致无法保存,而启用日志功能,只要启用事物日志功能,事物先在磁盘连续空间写写日志,然后通过内存同步到磁盘,确保万一内存同步磁盘时机器异常掉电,通过事物日志进行恢复数据库数据。
隔离级别
隔离级别(INNODB默认隔离级别为repeatable read):
READ UNCOMMITTED(读未提交):此种隔离级别带来问题有脏读和不可重复读。
READ COMMITTED(读提交):此种隔离级别解决了脏读,但仍然有不可重复度。
REPEATABLE READ(可重读):此种隔离级别解决了脏读和不可重复读,带来问题幻读。
SERIALIZABLE(可串行化):此种隔离级别解决了脏读、不可重复度和幻读,但带来的问题是加锁读。
问题解释:
脏读:当用户A修改数据但未提交,此时B用户读A修改后的数据,但是A用户将数据进行rollback回滚,因此B用户看到的是错误的数据;
不可重复读:如用户A启动一个事务设置某一值设为ON,经查询已经为ON状态,但B用户修改数值为OFF并提交,此时用户A再次查询时发现值又为OFF,或者数据库中又多了一条语句,表现为在同一事务中每次查询数据库总是不一致;
幻读:当用户A用户在同一事务中看到某一值为ON,用户B已经将值修改为OFF,并且已经提交,用户B看到的值为OFF,但用户A在此事务中一直看到的为ON,底层数据确实被修改为OFF,因此就体现了幻读,除非提交后再次生成一个事务查看值才为OFF;
加锁读:读数据时加锁,此时别人无法再读。
实例验证
根据理论概述,进行验证事物的工作流程和隔离级别,确保透彻了解事物的原理,具体操作如下
MariaDB [(none)]> show processlist; #查看数据库的进程列表,显示有两终端连接 +----+------+-----------+------+---------+------+-------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+------+-----------+------+---------+------+-------+------------------+----------+ | 2 | root | localhost | NULL | Sleep | 23 | | NULL | 0.000 | | 10 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 | +----+------+-----------+------+---------+------+-------+------------------+----------+ 2 rows in set (0.00 sec) MariaDB [(none)]> show global variables like 'tx_isolation'; #验证事物的隔离级别 +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.00 sec)
验证隔离级别 READ UNCOMMITTED(存在脏读、不可重复读)
第一步:创建表并插入数据
MariaDB [test]> create table employee(id int,name varchar(20),age char(3)); #创建表 Query OK, 0 rows affected (0.00 sec) MariaDB [test]> insert into employee values(1,'tangseng',38),(2,'sunwukong',505),(3,'zhubajie',485),(4,'shaheshang',408); #给表中添加用户 Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0
第二步:在两个数据库连接线程的会话变量中设置隔离级别为READ-UNCOMMITTED
会话1 MariaDB [(none)]> set tx_isolation='READ-UNCOMMITTED'; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> start transaction; #开启事物 Query OK, 0 rows affected (0.00 sec)
会话2 MariaDB [(none)]> set tx_isolation='READ-UNCOMMITTED'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> MariaDB [test]> start transaction; #开启事物 Query OK, 0 rows affected (0.00 sec)
第三步:两边同时启用事物,其中会话1添加bailongma,但不提交,在会话2上查看验证
会话1 #插入数据但未提交,会话2上查询后验证 MariaDB [test]> insert into employee values(5,'bailongma',300); Query OK, 1 row affected (0.00 sec) 会话2 #经查询验证会话1尚未提交已经可以读取,若会话1回滚,会话2读取数据为脏数据 MariaDB [test]> select * from employee; +------+------------+------+ | id | name | age | +------+------------+------+ | 1 | tangseng | 38 | | 2 | sunwukong | 505 | | 3 | zhubajie | 485 | | 4 | shaheshang | 408 | | 5 | bailongma | 300 | +------+------------+------+ 5 rows in set (0.00 sec)
验证隔离级别READ-COMMITTED(解决脏读问题,存在不可重复读)
第一步:创建表并插入数据
MariaDB [test]> create table employee(id int,name varchar(20),age char(3)); #创建表 Query OK, 0 rows affected (0.00 sec) MariaDB [test]> insert into employee values(1,'tangseng',38),(2,'sunwukong',505),(3,'zhubajie',485),(4,'shaheshang',408); #给表中添加用户 Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0
第二步:在两个数据库连接线程的会话变量中设置隔离级别为READ-COMMITTED
会话1 MariaDB [(none)]> set tx_isolation='READ-COMMITTED'; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> start transaction; #开启事务 Query OK, 0 rows affected (0.00 sec)
会话2 MariaDB [(none)]> set tx_isolation='READ-COMMITTED'; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> start transaction; #开启事务 Query OK, 0 rows affected (0.00 sec)
第三步:在两个连接数据库的线程进程添加bailongma,但不提交进行验证
会话1 MariaDB [test]> insert into employee values(5,'bailongma',305); Query OK, 1 row affected (0.00 sec) 会话2 #在会话1未提交时,会话2是无法读取数据 MariaDB [test]> select * from employee; +------+------------+------+ | id | name | age | +------+------------+------+ | 1 | tangseng | 38 | | 2 | sunwukong | 505 | | 3 | zhubajie | 485 | | 4 | shaheshang | 408 | +------+------------+------+ 4 rows in set (0.00 sec)
第四步:在连会话1上进行提交,然后在会话2上进行验证
会话1 MariaDB [test]> commit; Query OK, 0 rows affected (0.00 sec) 会话2 MariaDB [test]> select * from employee; +------+------------+------+ | id | name | age | +------+------------+------+ | 1 | tangseng | 38 | | 2 | sunwukong | 505 | | 3 | zhubajie | 485 | | 4 | shaheshang | 408 | | 5 | bailongma | 305 | +------+------------+------+ 5 rows in set (0.00 sec) #说明读提交可以解决脏读的问题
验隔离级别REPEATABLE READ(解决脏读和重复读的问题,带来新的问题幻读)
第一步:创建表并插入数据
MariaDB [test]> create table employee(id int,name varchar(20),age char(3)); #创建表 Query OK, 0 rows affected (0.00 sec) MariaDB [test]> insert into employee values(1,'tangseng',38),(2,'sunwukong',505),(3,'zhubajie',485),(4,'shaheshang',408); #给表中添加用户 Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0
第二步:在两个数据库连接线程的会话变量中设置隔离级别为REPEATABLE-READ
会话1 MariaDB [(none)]> set tx_isolation='REPEATABLE-READ'; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> start transaction; #开启事务 Query OK, 0 rows affected (0.00 sec)
会话2 MariaDB [(none)]> set tx_isolation='REPEATABLE-READ'; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> start transaction; #开启事务 Query OK, 0 rows affected (0.00 sec)
第三步:先会话2中开启一个事物查询表中数据,然后在会话1中添加bailongma用户,再次在会话2中的同一事务中查看表中数据(发现会话1中数据已经发生变化,会话2的同一事物中任然是之前的数据,因此解决了事物的可重复读)
会话2 #开启一个事物并查询表中数据 MariaDB [test]> select * from employee; +------+------------+------+ | id | name | age | +------+------------+------+ | 1 | tangseng | 38 | | 2 | sunwukong | 505 | | 3 | zhubajie | 485 | | 4 | shaheshang | 408 | +------+------------+------+ 4 rows in set (0.00 sec) 会话1 #添加bailongma数据后,提交并查询 MariaDB [test]> insert into employee values(5,'bailongma',305); Query OK, 1 row affected (0.00 sec) MariaDB [test]> commit; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> select * from employee; +------+------------+------+ | id | name | age | +------+------------+------+ | 1 | tangseng | 38 | | 2 | sunwukong | 505 | | 3 | zhubajie | 485 | | 4 | shaheshang | 408 | | 5 | bailongma | 305 | +------+------------+------+ 5 rows in set (0.00 sec) 会话2 #在会话2上再次查询,结果任然是4条数据,原因是会话2上的事物并未提交,并且解决了可重复读,因此只能看到4条,除非提交事物后再次查询; MariaDB [test]> select * from employee; +------+------------+------+ | id | name | age | +------+------------+------+ | 1 | tangseng | 38 | | 2 | sunwukong | 505 | | 3 | zhubajie | 485 | | 4 | shaheshang | 408 | +------+------------+------+ 4 rows in set (0.00 sec) MariaDB [test]> select * from employee; +------+------------+------+ | id | name | age | +------+------------+------+ | 1 | tangseng | 38 | | 2 | sunwukong | 505 | | 3 | zhubajie | 485 | | 4 | shaheshang | 408 | | 5 | bailongma | 305 | +------+------------+------+ 5 rows in set (0.00 sec)
验隔离级别SERIALIZABLE(解决重复读的问题,需注意每次操作都需要重启新的事物和提交,因为有加锁,一个事物只能是一组语句)
第一步:创建表并插入数据
MariaDB [test]> create table employee(id int,name varchar(20),age char(3)); #创建表 Query OK, 0 rows affected (0.00 sec) MariaDB [test]> insert into employee values(1,'tangseng',38),(2,'sunwukong',505),(3,'zhubajie',485),(4,'shaheshang',408); #给表中添加用户 Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0
第二步:在两个数据库连接线程的会话变量中设置隔离级别为SERIALIZABLE
会话1 MariaDB [(none)]> set tx_isolation='SERIALIZABLE'; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> start transaction; #开启事物 Query OK, 0 rows affected (0.00 sec)
会话2 MariaDB [(none)]> set tx_isolation='SERIALIZABLE'; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> start transaction; #开启事物 Query OK, 0 rows affected (0.00 sec)
第三步:当会话1上进行插入bailongma用户前,在会话2上进查询并提交,会话1添加bailongma并提交,然后再次在会话2上进行查询
会话1 #添加用户后,并未提交 MariaDB [test]> insert into employee values(5,'bailongma',305); Query OK, 1 row affected (0.00 sec) 会话2 #在会话1上未提交时,会话1对表进行加锁,因此会话2上时无法查询,因此解决幻读 MariaDB [test]> select * from employee; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
分享文章:轻松获知数据库事务
标题链接:http://myzitong.com/article/ghhjho.html