TCL: transaction control language

TCL(Transaction Control Language)

1, Business

1. Concept of services

One or a group of sql statements form an execution unit, which either executes all or does not execute all

2. Transaction characteristics (ACID)

• Atomicity: a transaction can no longer be split, either all or none
• Consistency: the execution of a transaction will switch the data from one consistent state to another
• Isolation: the execution of one transaction is not disturbed by other transactions
• Persistence: once a transaction is committed, it will permanently change the data in the database

3. Usage steps of transaction

• Implicit (automatic) transaction: without obvious start and end, it is a transaction and can be submitted automatically, such as insert, update and delete. When autocommit is ON, it indicates that it is in automatic transaction state

  #View autocommit status in MySQL
  SHOW VARIABLES LIKE 'autocommit';
  

• Explicit transaction: the transaction has obvious opening and ending marks; Premise: you must first set the auto submit function to disabled (set autocommit to OFF)

3.1 transaction submission

Transaction statements are generally only basic additions, deletions, modifications and queries: select insert update delete. Operations on database tables, such as alter drop, are not used as transaction statements

#Step 1: start the transaction
set autocommit=0;#Set the auto submit feature to disabled
start transaction;#Optional

#Step 2: write the sql statement in the transaction (select insert update delete)
Statement 1;
Statement 2;
...

#Step 3: end the transaction, generally commit the transaction or roll back the transaction
commit;Commit transaction

#Open transaction
SET autocommit=0;
START TRANSACTION;

#Write statements for a set of transactions
INSERT INTO major VALUES(1,'java');
INSERT INTO major VALUES(2,'c++');

#End transaction
commit;

It is worth noting that update and submission are two concepts. In the above sql statement, before the commit statement is executed, the two pieces of data are only temporarily placed in memory. Only after the commit statement is executed, the database in the disk will be changed.

If the statement ending the transaction is not commit but rollback, the two pieces of data will not be saved to the disk, that is, the database will not change

3.2 transaction rollback

3.2.1 basic operation

At the end of the transaction, change the commit to rollback

#Open transaction
SET autocommit=0;
START TRANSACTION;

#Write statements for a set of transactions
INSERT INTO major VALUES(1,'java');
INSERT INTO major VALUES(2,'c++');

#End transaction
commit;
#rollback;

3.2.2 save point

savepoint: the node that saves the transaction state

#Set save point
SAVEPOINT Save roll call;

#Rollback to specified savepoint
ROLLBACK TO Save roll call;

After the following transaction ends, the one with id 2 will not be deleted, and the one with id 1 will be deleted

SET autocommit=0;

DELETE FROM major WHERE id = 1;
SAVEPOINT a;#Set savepoint a
DELETE FROM major WHERE id = 2;

ROLLBACK TO a;#Rollback to savepoint a

3.2.3 difference between delete and truncate during rollback

delete deletion can be rolled back, truncate deletion cannot be rolled back

#Delete delete
SET autocommit=0;
START TRANSACTION;
DELETE FROM major;#It will not be deleted after execution
ROLLBACK;

#truncate delete
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE major;#Delete after execution
ROLLBACK;

SELECT * FROM major;

2, Concurrent transaction

1. Concept of concurrent transaction

When multiple transactions operate the same data in the same database at the same time, it is called concurrent transaction. If the necessary isolation mechanism is not taken, the concurrency of transactions is easy to occur

2. Common concurrency problems

• Dirty read: a transaction reads data updated by other transactions but not committed
• Phantom read: a transaction reads data inserted or deleted by other transactions but not committed
• Non repeatable read: a transaction is read multiple times and the results are different

3. Transaction isolation and isolation level

Isolation of database transactions: the database system must have the ability to isolate and run various transactions concurrently so that they will not affect each other. Therefore, you need to set the isolation level to solve the concurrency problem

Isolation level: the degree to which a transaction is isolated from other transactions is called isolation level. The database specifies multiple transaction isolation levels. Different isolation levels correspond to different interference levels. The higher the isolation level, the better the data consistency, but the weaker the concurrency. The following table shows the corresponding relationship between the isolation level and whether the concurrency problem can be solved

Isolation level	Dirty reading	Non repeatable reading	Unreal reading
Read uncommitted: read uncommitted (transactions are allowed to read changes that are not committed by other transactions)	×	×	×
Read committed: read committed (only changes committed by other transactions are allowed to be read by transactions, and dirty reading can only be avoided)	√	×	×
Repeatable read: repeatable read (ensure that the transaction reads the same value from a field multiple times. During the duration of the transaction, other transactions are prohibited from updating the field to avoid dirty reading and non repeatable reading)	√	√	×
serializable: serialization (ensure that transactions can read the same rows from a table. During the duration of the transaction, other transactions are prohibited from performing insert, update and delete operations on the table. All concurrency problems can be avoided, but the performance is low)	√	√	√

(Oracle supports two transaction isolation levels: read committed (default) and serializable; MySQL supports four isolation levels: repeatable read by default)

Every time you start a MySQL program, you will get a separate database connection. Each connection has a global variable @ @ transaction_isolation, indicating the current transaction isolation level

#View the isolation level of the current MySQL connection
select @@transaction_isolation; # Older versions can use the alias tx_isolation, the new version is not allowed

#Set the isolation level of the current MySQL connection
#set session transaction isolation level;
set session transaction isolation level read uncommitted;

#Set the global isolation level of the entire database system. After setting, you need to restart
set global transaction isolation level Isolation level;