MySQL database transaction example tutorial

1. What is a transaction?

A transaction is a complete business logic unit and cannot be divided.
For example, to transfer 10,000 yuan from account A to account B in a bank account, two update statements need to be executed:

update t_act set balance=balance-10000 where actno='act-001';
update t_act set balance=balance+10000 where actno='act-0021';

The above two DML statements must succeed at the same time or fail at the same time. It is not allowed for one to succeed and the other to fail.
To ensure that the above two DML statements succeed or fail at the same time, you need to use the database's "transaction mechanism".

2. The only statements related to transactions are these three DML statements: insert, delete, update

“Why are there only these three DML statements: insert, delete, update?”
Because these three statements are all "related to the data" in the database table. Transactions exist to ensure data integrity and security.

3. Assuming that all business operations can be handled using one DML statement, is a transaction mechanism still needed?

No transactions are required.
But the actual situation is not like this. Usually one thing ("transaction") requires multiple DML statements to complete together.

4. Principles of Transactions

Notice:
Once a transaction is committed, the historical operations will be persisted to the hard disk. After persistence is completed, the historical records will be cleared.
Once the transaction is rolled back, the historical records will be cleared directly without being persisted to the hard disk.
Transaction operations can also design save points: understand.

5. Four characteristics of transactions: ACID

Transactions include four major characteristics: ACID
AAtomicity: A transaction is the smallest unit of work and cannot be divided.
C consistency: The transaction must ensure that multiple DML statements succeed or fail at the same time.
Isolation: Transaction A is isolated from transaction B.
D. Persistence: Persistence means that the final data must be persisted to the hard disk file for the transaction to be successfully completed.

The following is a more detailed explanation of the four major characteristics of the above transactions: "Atomicity": A set of operations either succeeds or fails, and this set of operations cannot be split.
"Consistency": The total amount of data still matches before and after the transaction occurs. Let’s simulate a transfer from one person to another. Before the transfer, the total amount of the two people is 400; after the transfer, the total amount of the two people is still 400.
"Isolation": Before all operations are completed, other session windows cannot see the intermediate data changes, and only the current window can see the data changes.
"Persistence": Once a transaction is committed, the effects of the transaction cannot be undone, and the data has actually been modified.

6. Isolation between transactions

"Transaction isolation has isolation levels, theoretically there are 4 isolation levels"
The isolation level usually starts from level 2 or 3, and level 1 is generally not used.

1) First level: read uncommitted

The other party's transaction has not been submitted yet, and the current transaction can read the data that the other party has not submitted.
Problems with read-uncommitted transactions: "dirty read phenomenon", which means that dirty data is read.
"Dirty read": refers to a transaction that is modifying data, but the modification has not been committed to the database.
When another transaction accesses the data, the data is considered dirty data, so it is called dirty read.

2) Second level: read committed

We can read the data after the other party's transaction is submitted.
This isolation level solves the problem: there is no dirty read phenomenon.
Problems with read committed: non-repeatable read.

3) Third level: repeatable read

This isolation level solves the problem of non-repeatable reads.
The problem at this level is that the data read is fantasy, that is, what is read is the backup data.

4) Fourth level: serializable read

Solved all the problems.
However, the efficiency is low and transactions need to be queued.

"It should be noted that"
The default isolation level of the Oracle database is: Read Committed (second level).
The default isolation level of the mysq1 database is: repeatable read (level 3).

7. Demonstration transaction isolation level (create some demonstration data yourself)

1) Knowledge points that need to be mastered before demonstrating transactions.

1) By default, MySQL transactions are automatically committed.
2) What is auto-commit?
   Whenever any DML statement is executed, it is automatically submitted once.
   Therefore, before demonstrating transactions, you must first turn off autocommit.
   "Turn off auto-commit statement": start transaction;    
3) "3 commands you need to know to demonstrate transactions:"
   -- Turn off the automatic transaction commit feature.
   start transaction;
  -- Commit the transaction.
  commit;
  -- Roll back the transaction, only roll back to the last commit point.
  rollback;   

2) Set the global transaction isolation level.

"Set the global transaction isolation level. After setting, log out and log in again."
-- Set the first level set global transaction isolation level read uncommitted;
-- Set the second level set global transaction isolation level read committed;
-- Set the third level (system default transaction level, no need to set)
set global transaction isolation level repeatable read;
--Set the fourth level set global transaction isolation level serializable;

"View global transaction isolation level"
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

3) Demonstrate read uncommitted.

First, set the global transaction isolation level.

The following is a formal demonstration:

4) Demonstration Read Committed

First, set the global transaction isolation level.

The following is a formal demonstration:

5) Demonstrate repeatable read

First, set the global transaction isolation level.

The following is a formal demonstration:

This needs attention:

What is demonstrated here is "repeatable read". We demonstrate a series of transaction processes in the right window. The left window cannot read at all. What is read in the left window is always the backup data of the original data.

How do you understand it?

Don't forget that the transaction function is also turned on in the left window, start transaction; as long as the transaction function of the left window is not ended (commit or rollback can end the transaction), the data read by the left window is always the backup data of the original data. This is what we call "fantasy". No matter what changes are made in the black window on the right, even if you commit the transaction, I cannot see it in the window on the left.

There is a black window on the left. How can I see this change? "

Only the black window on the left ends the transaction of the current black window first, and then reads it again, you can see that the data has actually changed.

6) Demonstrate serialized reading

First, set the global transaction isolation level.

The following is a formal demonstration:
First picture:

After the transaction is committed in the left window using the "commit" command, we can see the changes in the right window.

The above is the detailed content of the MySQL database transaction example tutorial. For more information about MySQL database transactions, please pay attention to other related articles on 123WORDPRESS.COM!