Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to understand MySQL database transaction transaction", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's ideas slowly in depth, together to study and learn "how to understand MySQL database transaction transaction" bar!

1. What is a transaction?

A transaction is a complete business logic unit, which can no longer be divided.

For example: bank account transfer, transfer 10000 from An account to B account, you need to execute two update statements:

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, and 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's

"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, update?"

Because these three statements are related to the "data" in the database table. The existence of transactions is to ensure the integrity and security of data.

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

No transaction is required.

However, this is not the case. Usually, one thing ("transaction") requires multiple DML statements to work together.

4. The principle of transaction

Note:

Once the transaction is committed, the historical operation is persisted to the hard disk, and after the persistence is completed, the history is cleared.

Once the transaction is rolled back, the history is emptied directly without persistence to the hard disk.

Transaction operation, you can also design SavePoint: understand.

5. Four characteristics of transaction: ACID

Transactions include four major features: ACID

An atomicity: transactions are the smallest units of work and can no longer be divided.

C consistency: transactions must guarantee multiple DML statements that succeed or fail at the same time.

I isolation: there is isolation between transaction An and transaction B.

D persistence: persistence means that the final data must be persisted to a hard disk file for the transaction to end successfully.

The following is a more detailed description of the four major features of the above transactions

Atomicity: a set of operations that either succeed or fail are inseparable.

Consistency: the total amount of data still matches before and after the transaction. To simulate the transfer of money 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 process of changing intermediate data, only the current window can see the process of changing data.

"persistence": once the commit is committed, the impact of the transaction cannot be undone, and the data has actually been modified.

6. About the isolation between transactions

"there are isolation levels for transaction isolation, which theoretically includes four"

Isolation levels generally start from level 2 and level 3, and level 1 is generally not needed.

1) level 1: read unsubmitted (read uncommitted)

The transaction of the other party has not been committed yet, and the current transaction can read the data committed by the other party.

There is a problem with reading unsubmitted: "dirty reading", which means that dirty data has been read.

"dirty read": means that a transaction is modifying the data, but the change is not committed to the database.

Another transaction accesses the data, which in this case belongs to dirty data, so it is called dirty reading.

2) level 2: read submitted (read committed)

We can read the data after the transaction of the other party is submitted.

This isolation level is solved: dirty reading is gone.

The problem with reading submitted is that it cannot be read repeatedly.

3) level 3: repeatable (repeatable read)

This isolation level solves the problem of non-repeatable reading.

The problem with this level is that the data read is an illusion, that is, the data read is backup data.

4) level 4: serialization read / serialization read (serializable)

Solved all the problems.

But it is inefficient and requires queuing of transactions.

"it's important to pay attention to."

The default isolation level for oracle databases is: read committed (level 2).

The default isolation level for mysq1 databases is repeatable (level 3).

7. Demonstrate the isolation level of the transaction (make some of the demo data by yourself) 1) the knowledge points you need to master before demonstrating the transaction. 1) mysql by default, transactions are committed automatically. 2) what is automatic submission? As long as any DML statement is executed, it is automatically submitted. Therefore, autocommit must be turned off before demonstrating the transaction. "turn off autocommit statement": start transaction; 3) "demonstrate three commands that a transaction needs to know:"-- turn off the autocommit transaction feature. Start transaction;-commit the transaction. Commit;-rollback the transaction, only to the last commit point. Rollback;2) sets the global transaction isolation level. "set the global transaction isolation level. When the setting is complete, log out and log back in." -- setting first level set global transaction isolation level read uncommitted;-- setting second level set global transaction isolation level read committed;-- setting third level (system default transaction level, not setting) set global transaction isolation level repeatable read;-- setting 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) Demo reading was not submitted.

First, set the global transaction isolation level.

Here is a formal demonstration:

4) Demo read submitted

First, set the global transaction isolation level.

Here is a formal demonstration:

5) demonstration can be read repeatedly

First, set the global transaction isolation level.

Here is a formal demonstration:

This needs to be noted:

What we demonstrate here is "readable". We demonstrate some of the column processes of the transaction in the window on the right, and the window on the left.

It is impossible to read at all, and the window on the left always reads the backup data of the original data.

How do you understand it?

Don't forget, the window on the left also turns on the transaction function, start transaction; only

Is the transaction function of the window on the left, there is no end (either commit or rollback can end the transaction), then

What we read in the window on the left is always the backup data of the original data, which is what we call "fantasy". Ren

With any changes made in the black window on the right, even if you commit the transaction, I can't see it in the window on my 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, read it again, and you can see it.

To the fact that the data has changed.

6) demonstrate serialization read

First, set the global transaction isolation level.

Here is a formal demonstration:

The first picture:

When the window on the left, using the "commit" command, commits the transaction, we look at the changes in the window on the right.

Thank you for your reading, the above is the content of "how to understand MySQL database transaction transaction". After the study of this article, I believe you have a deeper understanding of how to understand MySQL database transaction transaction, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.