catalogue

1. What is a transaction? 2. There are only three DML statements related to transactions: insert, delete and update 3. Assuming that all businesses can be handled with one DML statement, do you still need a transaction mechanism? 4. Principle of transaction 5. Four characteristics of transaction: ACID 6. Isolation between transactions 7. Demonstrate the isolation level of transactions (demonstrate that the data is self-made)

1. What is a transaction?

A transaction is a complete business logic unit and cannot be further divided. For example, when transferring 10000 from A bank account to B 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 or fail at the same time. One success and one failure are not allowed. To ensure that the above two DML statements succeed or fail at the same time, you need to use the database "Transaction mechanism".

2. There are only three DML statements related to transactions: insert, delete and update

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

3. Assuming that all businesses can be handled with one DML statement, do you still need a transaction mechanism?

No transactions are required. However, this is not the case. Usually, one thing ("transaction") needs to be completed jointly by multiple DML statements.

4. Principle of transaction

be careful: Once the transaction is committed, the historical operation will be persisted to the hard disk. After the persistence is completed, the historical record will be cleared. Once the transaction is rolled back, the history is cleared directly instead of being persisted to the hard disk. For transaction operations, you can also design savepoints: understand.

5. Four characteristics of transaction: ACID

Transaction includes four features: ACID A atomicity: transactions are 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. I isolation: there is isolation between transaction A and transaction B. D persistence: persistence means that the final data must be persisted to the hard disk file before the transaction is successfully completed.

The following is a more detailed description of the above four transaction features "Atomicity": a group of operations either succeed or fail. This group of operations is inseparable. "Consistency": the total amount of data is still matched before and after the transaction. Simulate the transfer from one person to another. Before the transfer, the total amount of the two persons is 400; After the transfer, the total amount of the two persons is still 400. "Isolation": before all operations are completed, other session windows cannot see the change process of intermediate data, and only the current window can see the data change process. "Persistence": once the commit is committed, the impact of the transaction cannot be revoked, and the data has been actually modified.

6. Isolation between transactions

"There are isolation levels for transaction isolation. Theoretically, there are four isolation levels"
Isolation level generally starts from level 2 and level 3, and level 1 is generally unavailable.
1)Level 1: read uncommitted( read uncommitted)
   The other party's transaction has not been committed. The current transaction can read the data submitted by the other party.
   Read uncommitted problems:"Dirty reading phenomenon"，Indicates that dirty data has been read.
   "Dirty reading": It means that a transaction is modifying data, but the modification is not committed to the database.
Another transaction accesses the data. At this time, the data belongs to dirty data, so it is called dirty reading.

2)Level 2: read submitted( read committed)
   We can read the data after the opposite transaction is committed.
   This isolation level solves the problem: dirty reads are gone.
   Read submitted problem: cannot be read repeatedly.
   
3)Level 3: repeatable( repeatable read)
   This isolation level solves the problem of non repeatable reads.
   The problem with this level is that the data read is fantasy, that is, the data read is backup data.
   
4)Level 4: serialized read/Serial read( serializable)
   Solved all the problems.
   However, it is inefficient and requires transaction queuing.
   
"It should be noted that"
oracle The default isolation level of the database is read committed (the second level).
mysq1 The default isolation level of the database is repeatable read (the third level).

7. Demonstrate the isolation level of transactions (demonstrate that the data is self-made)

1) What you need to know before presenting a transaction.

1)mysql By default, transactions are automatically committed.
2)What is auto submit?
   As long as any one is executed DML Statement, it is automatically submitted once.
   Therefore, auto commit must be turned off before presenting a transaction.
   "Turn off auto commit statements": start transaction;
    
3)"Three commands that need to be known to demonstrate transactions:"
   -- Turn off auto commit transactions.
   start transaction;
  -- Commit the transaction.
  commit;
  -- Rollback transactions can only be rolled back to the last commit point.
  rollback;   

2) Set the global transaction isolation level.

"Set the global transaction isolation level. After setting, exit and log in again."
-- Set first level
set global transaction isolation level read uncommitted;
-- Set second level
set global transaction isolation level read committed;
-- Set the third level (the default transaction level of the system is not set)
set global transaction isolation level repeatable read;
--Set the fourth level
set global transaction isolation level serializable;

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

3) Demo read not submitted.

First, set the global transaction isolation level.

Here's a formal demonstration:

4) Demo read submitted

First, set the global transaction isolation level.

Here's a formal demonstration:

5) Demonstrate readability

First, set the global transaction isolation level.

Here's a formal demonstration:

This requires attention:
    What is demonstrated here is"Repeatable reading"，We demonstrate some column processes of transactions in the right window and the left window
 It cannot be read at all. What is read in the left window is always the backup data of the original data.
    How to understand?
    Don't forget that the transaction function is also enabled in the left window, start transaction;as long as
 It's the transaction function of the window on the left. It doesn't end(commit perhaps rollback Can end the transaction)，that
 What is read in the left window is always the backup data of the original data, which is what we call it"fantasy". let
 If you make any changes in the black window on the right, even if you commit the transaction, I can't see it in the window on the left.
    "How can I see this change in the black window on the left?"
    Only the black window on the left ends the transaction of the current black window and reads it again
 To the data is the real change.  

6) Demonstrate serialized read

First, set the global transaction isolation level.

Here's a formal demonstration: First picture:

In the left window, use the "commit" command. After the transaction is committed, we will look at the changes in the right window.