What are database transactions

A database transaction is a sequence of database operations that access and may operate on various data items, all of which are executed or none of which are an indivisible unit of work. A transaction consists of all database operations performed between the beginning of a transaction and the end of a transaction. — Baidu Encyclopedia

For example, if you have two SQL statements to execute, if you place them in a transaction, either both SQL statements will succeed or both will fail. Commit the transaction if it succeeds, and rollback if it fails. There is no such thing as one success and one failure.

The ACID principle of transactions

This is the heart of database transactions.

1. Atomicity

Let’s say now A has 800 yuan, B has 200 yuan, and A transfers 200 yuan to B. There are 2 steps to complete this scenario, which can be considered in a transaction:

2-B: 200+200=400Copy the code

Then, both steps will succeed, or both will fail. If one succeeds and one fails, then one person’s money is not right. Atomicity means that you can’t just have one of these actions happen.

2. Consistency

The consistency of the state before and after a transaction.

For example, now A has 800 yuan and B has 200 yuan, so the total for both of them is 1,000 yuan. So no matter how much these two people go back and forth, the total must still be 1000 yuan, money does not just happen or disappear.

3. Durability

For any committed transaction, the system must ensure that changes made by that transaction to the database are not lost, even if the database fails.

For example, now A has 800 yuan and B has 200 yuan, A wants to transfer 200 yuan to B, or there are two situations:

1. When the transaction is not committed and the service is suspended or powered off, the data status of the database is as follows: A has 800 yuan, B has 200 yuan 2. If the transaction has been committed and the service has been suspended or powered down, the data status should be: A has $600 and B has $400 after restarting the databaseCopy the code

As you can see, once a transaction is committed, it is persisted to the database without data loss due to external causes.

4. Isolation

Transaction execution is not interfered with by other transactions, and the intermediate results of transaction execution must be transparent to other transactions.

For example, there are two transactions going on at the same time, and A and C are transferring money to B at the same time:

Transaction 1: A has 800 yuan, B has 200 yuan, A transfers 200 yuan to BCopy the code
Transaction 2: C has 1000 yuan, B has 200 yuan, C transfers 100 yuan to BCopy the code

These two transactions do not affect each other. Isolation is to exclude the impact of other transactions on a transaction in the case of simultaneous operations by multiple users.

3. Problems caused by isolation

There are four transaction isolation levels for a database, from low to high: Read Uncommitted, Read committed, Repeatable Read, Serializable, and other problems may occur depending on the isolation level.

1. Dirty reads

A transaction that reads uncommitted data from another transaction is a dirty read.

Transaction 1: A transfers 500 to B, but the transaction is not committed. Transaction 2: B checked the account and found that A transferred 500, originally only transferred 300 to come over, found that the extra 200, my heart is very happy... Transaction 1: A finds that the transfer is 200 too much, changes the transfer to 300, and commits the transaction.Copy the code

Finally, B checks the account again and finds only 300 yuan more, which makes him happy. This is dirty reading. This can happen when the isolation level is set to Read Uncommitted. To avoid dirty reads, set the isolation level to Read COMMITTED.

2. It cannot be read repeatedly

If a transaction reads the same record successively, and the data is modified by other transactions between reads, the two reads are different, which is called unrepeatable reads.

Transaction 1: B goes to buy something, has 500 yuan in his card, spends 100 yuan, has not submitted the transaction. Transaction 2: B's wife transferred 500 yuan of B's money and submitted the transaction. Transaction 1: B commits the transaction and cannot pay. I read the card again and found no money to pay.Copy the code

When the isolation level is set to Read COMMITTED, dirty reads are avoided, but unrepeatable reads may occur. To avoid Repeatable reads, set the isolation level to Repeatable read.

3. The magic to read

A transaction that re-reads previously retrieved data under the same query criteria only to find that other transactions insert new data that meets its query criteria is called a phantom read.

Transaction 1: B's wife checks B's card consumption record and spends 500 yuan so far. Transaction 2: B has just invited friends out to dinner, paid 100, and the transaction has been committed. B's wife decided to print out the bill and checked with B in the evening, only to find that the printed consumption was 600 yuan. She just saw 500. How could it be 600? Was it a hallucination?Copy the code

The default isolation level of Mysql is Repeatable read to avoid unrepeatable reads, but phantom reads can occur.

If you continue to resolve phantom reads, you can set the isolation level to the highest level, Serializable, where transactions are executed sequentially and dirty reads, phantom reads, and non-repeatability are avoided, but the performance is poor.

4. Process of transaction under manual test

You can perform the transaction commit process manually in mysql to help understand. Now let’s simulate A transfer. A transfers 500 to B.

First create test conditions, build libraries, tables, data.

CREATE DATABASE shop CHARACTER SET utf8 COLLATE UTf8_general_ci; -- USE shop; CREATE TABLE 'account' (' id 'INT(3) NOT NULL AUTO_INCREMENT,' name 'VARCHAR(30) NOT NULL, 'money' DECIMAL(9,2) NOT NULL, PRIMARY KEY (' id '))ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO account(' name ', 'money') VALUES ('A', 2000.00),('B', 10000.00)Copy the code

The above SQL has been executed, now there are two test data.

The transaction commit process is then performed manually.

  1. Turn off automatic submission
SET autocommit = 0; -- Turn autocommit off, default is onCopy the code

Execute SQL.

  1. Start a transaction
START TRANSACTION -- Starts a TRANSACTIONCopy the code

Execute SQL.

  1. SQL defined in a transaction is defined in a transaction.
UPDATE account SET money= money-500 WHERE 'name' = 'A' -- A 'B' -- B goes up by 500Copy the code

After execution, data changes. A goes up by 500, B goes down by 500.

Now I’m not going to commit, I’m going to roll back.

ROLLBACK; - the rollbackCopy the code

The data goes back to where it started.

Re-execute 2 SQL and commit the transaction.

UPDATE `shop`.`account` SET `money`=`money` - 500 WHERE `name` = 'A'; UPDATE 'shop'. 'account' SET 'money' = 'money' WHERE 'name' = 'B'; -- B add 500 COMMIT; Commit transactionCopy the code

The data is successfully modified, and the rollback is performed again. The data is irreversible.