【MySQL 06】Transaction processing

1. ACID properties of transactions

Transactions have four characteristics: Atomicity, Consistency, Isolation and Durability.

Take "Bank Transfer" as an example:

• Atomicity: The statements that make up a transaction form a logical unit, and only part of it cannot be executed. In other words, a transaction is the smallest indivisible unit. For example: During the bank transfer process, the transfer amount must be subtracted from one account and added to another account at the same time. It is unreasonable to only change one account.

• Consistency: The database is consistent before and after transaction processing is executed. That is, transactions should correctly transform the system state. For example: During the bank transfer process, either the transfer amount is transferred from one account to another account, or both accounts remain unchanged, and there is no other situation.

• Isolation: One transaction has no impact on another transaction. That is to say, it is impossible for any transaction to see a transaction in an incomplete state. For example, during a bank transfer, before the transfer transaction is submitted, another transfer transaction can only be in a waiting state.

• Durability (Durablility): The effects of transaction processing can be preserved permanently. On the other hand, transactions should be able to withstand all failures, including server, process, communication, media failures, etc. For example: During the bank transfer process, the account status after the transfer must be saved.

2. Transaction status

SET AUTOCOMMIT = 0 , 禁止自动提交 SET AUTOCOMMIT = 1, 开启自动提交
START TRANSACTION：开始事务，autocommit设为0，如果已经有一个事务在运行，则会触发一个隐藏的COMMIT
COMMIT：提交事务，保存更改，释放锁
ROLLBACK：回滚本事务对数据库的所有更改，然后结束事务，释放锁
SAVEPOINT savepoint_name：创建一个savepoint识别符来ROLLBACK TO SAVEPOINT
ROLLBACK TO SAVEPOINT savepoint_name：回滚到从savepoint_name开始对数据库的所有更改，这样就允许回滚事务中的一部分，保证更改的一个子集被提交
SET TRANSACTION：允许设置事务的隔离级别
LOCK TABLES：允许显式的锁住一个或多个table，会隐式的关闭当前打开的事务，建议在执行LOCK TABLES语句之前显式的commit或rollback。
我们一般所以一般在事务代码里不会使用LOCK TABLES

3. Transaction operation

(1) First create the employee data table:

mysql> create table employee(
    -> employeeID char(4),
    -> name varchar(20) not null,
    -> job varchar(20),
    -> departmentID int
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> insert into employee value ('7513' , 'Nora Edwar' , 'Programmer', 128);
mysql> insert into employee value ('9006' , 'Candy Burn' , 'Systems Ad',128 );
mysql> insert into employee value ( '9842' , 'Ben Smith' ,  'DBA' , 42);
mysql> insert into employee value ('9843',  'Pert Park'  , 'DBA' , 42 );
mysql> insert into employee value ('9845' , 'Ben Patel'  , 'DBA' , 128 );
mysql> insert into employee value ('9846' , 'Red Right' ,  null, 128 );
mysql> insert into employee value ('9847' , 'Run Wild'  ,  null , 128 );
mysql> insert into employee value ('9848' , 'Rip This J' , null , 128 );
mysql> insert into employee value ('9849' , 'Rip This J' , null  , 128 );
mysql> insert into employee value ( '9850' , 'Reader U' ,   null , 128 );
mysql> insert into employee value ('6651',  'Ajay Patel' , 'Programmer', 128 );

mysql> select * from employee;
+------------+------------+------------+--------------+
| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+
| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 |
+------------+------------+------------+--------------+

( 2) SET AUTOCOMMIT=0：

mysql> set autocommit = 0;//禁止自动提交

mysql> insert into employee values(null,'test1',null,128);

mysql> savepoint s1;//创建一个savepoint识别符

mysql> insert into employee values(null,"test2",null,128);

mysql> savepoint s2;//创建一个savepoint识别符

mysql> insert into employee values(null,"test3",null,128);

mysql> savepoint s3;//创建一个savepoint识别符mysql> select * from employee;
+------------+------------+------------+--------------+| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 |
| NULL       | test1      | NULL       |          128 |
| NULL       | test2      | NULL       |          128 || NULL       | test3      | NULL    |    128 |
+------------+------------+------------+--------------+

(3) ROLLBACK TO SAVEPOINT：

mysql> rollback to savepoint s1;//回滚到s1标签处：mysql> select * from employee;
+------------+------------+------------+--------------+| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 || NULL       | test1      | NULL       |      128 |
+------------+------------+------------+--------------+

(4) COMMIT：

mysql> commit;//提交事务
mysql> rollback to savepoint s2;
//一旦事务提交了，就不能再回滚ERROR 1305 (42000): SAVEPOINT s2 does not exist

( 5) SET AUTOCOMMIT=1:

mysql> set autocommit = 1;//自动提交事务

mysql>  insert into employee values(null,"test4",null,128);

mysql> savepoint s4;//一旦创建，自动提交mysql> select * from employee;
+------------+------------+------------+--------------+| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 |
| NULL       | test1      | NULL       |          128 |
| NULL       | test2      | NULL       |          128 |
| NULL       | test3      | NULL       |          128 || NULL       | test4      | NULL       |          128 |
+------------+------------+------------+--------------+mysql> rollback to s4;//此时就无法回滚了
ERROR 1305 (42000): SAVEPOINT s4 does not exist

4, lock

Shared lock, exclusive lock, pessimistic lock, optimistic lock, row-level lock, table-level lock

• Shared lock: When reading data, add a shared lock to the data. Sharing does not conflict with sharing directly, but it conflicts with exclusive locks.

• Exclusive lock: When updating data, install an exclusive lock and prohibit all other actions.

• Pessimistic lock: Used when there are many updates and few queries. Pessimistic lock is not a real lock in the database, it is people's attitude towards affairs.

• Optimistic lock: There are few updates and it is used for many queries. Optimistic lock is not a real lock in the database, but is people’s attitude towards affairs.

5. Concurrent processing

• Dirty read: One transaction reads data that has not been submitted by another transaction
  Transaction 1: Update A piece of data
  ————>Transaction 2: Read the record updated by transaction 1
  Transaction 1: Call commit to submit
  At this time, the data read by transaction 2 is the data stored in the database memory , called dirty reading.
  The data read is dirty data
  Detailed explanation:
  Dirty reading means: when a transaction is accessing data and has modified the data, but this modification has not yet been submitted to the database, this At this time,
  Another transaction also accessed this data and then used this data. Because this data has not yet been submitted, the data read by another
  transaction is dirty data, and the operations based on the dirty data may be incorrect.

• Non-repeatable read: In the same transaction, the same data is read twice and the content is different
  Transaction 1: Query a record
  ————->Transaction 2: Update the record queried by transaction 1
  ————->Transaction 2: Call commit to submit
  Transaction 1: Query the last record again
  At this time, transaction 1 has queried the same data twice , the available content is different, which is called non-repeatable reading

• 幻读：同一事务中，用同样的操作读取两次，得到的记录数不相同 
  事务1：查询表中所有记录 
  ———->事务2：插入一条记录 
  ———->事务2：调用commit进行提交 
  事务1：再次查询表中所有记录 
  此时事务1两次查询到的记录是不一样的，称为幻读 
  详细解释： 
  幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改， 
  这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表 
  中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行， 
  就好象发生了幻觉一样。

6、事务隔离

事务隔离五种级别：

TRANSACTION_NONE 不使用事务。 
TRANSACTION_READ_UNCOMMITTED 允许脏读。 
TRANSACTION_READ_COMMITTED 防止脏读，最常用的隔离级别,并且是大多数数据库的默认隔离级别 
TRANSACTION_REPEATABLE_READ 可以防止脏读和不可重复读， 
TRANSACTION_SERIALIZABLE 可以防止脏读，不可重复读取和幻读，（事务串行化）会降低数据库的效率

以上的五个事务隔离级别都是在Connection接口中定义的静态常量，

使用setTransactionIsolation(int level) 方法可以设置事务隔离级别。 
如:con.setTransactionIsolation(Connection.REPEATABLE_READ);

注意：事务的隔离级别受到数据库的限制，不同的数据库支持的的隔离级别不一定相同

summary： 
（1）Serializable：可避免脏读、不可重复读、虚读情况的发生。 
（2）Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读，是 MySQL 默认的事务隔离级别） 
（3）Read committed：可避免脏读情况发生。（读取已提交的数据） 
（4）Read uncommitted：最低级别，以上情况均无法保证。（读取到了未提交的数据）

以上就是 【MySQL 06】事务处理的内容，更多相关内容请关注PHP中文网（www.php.cn）！