Mysql transaction _ MySQL

Mysql supports transaction processing starting from 41, but transaction operations are supported only for databases created by engines of the InnoDBBDB type. View the mysql database Creation Engine type: showcreatetabletable_name

Mysql supports transaction processing since 4.1, but only databases created using InnoDB/BDB engines support transaction operations.

View the mysql database Creation Engine type: show create table table_name

Create or modify a database of the specified type: Create table... type = InnoDB; Alter table table_name type = InnoDB;

Mysql transaction type

There are two types:

1. begin, rollback, commit. of course some people use begin/begin work. it is recommended to START a TRANSACTION with start transaction as the SQL-99 standard.

start transaction；update from account set money=money-100 where name='a';update from account set money=money+100 where name='b';commit；

Explanation: In this case, start transaction is used to manually start transactions and commit is used to manually close transactions.

2. by default, autocommit = 1 is enabled for automatic submission, so you can understand that each statement is committed as soon as it is input into mysql. When you set autocommit = 0, you can do this:

update from account set money=money-100 where name='a';update from account set money=money+100 where name='b';commit；

Mysql transaction level
Before introducing the transaction level, you need to understand some common problems in the database query and update process. Simply put, these transaction levels exist to solve some problems. ?

1. repeatable read means that the results of each read must be consistent. This problem occurs when the data read from the database is inconsistent. The reason for this is that the record was modified after another session was read.

2. Phantom read refers to the record added to another session when the current session reads the relevant record. To avoid phantom reads, the transaction isolation level is usually set to serializable. However, the problem arises. when the transaction level of the database is set to the serializable level, the database becomes a single-threaded access to the database, resulting in a very high performance reduction.

3. for example, if user A \ B operates on the t_accout table at the same time, user A performs this operation on t_accout, and update account set money = money + 100; before submitting, user B queries the uncommitted money, and then User A has A rollback, and User B finds that the money is less than 100. in this case, we usually set the transaction levelRead committed. Only the submitted stuff can be read;

In order to solve the above problems, the transaction level appears. The following are common transaction levels in the database.

Level

1. Read Uncommitted: this isolation level allows the current transaction to Read data that has not been committed by other transactions. This type of read should be completed in the rollback segment. Through the above analysis, this isolation level is the lowest, which will lead to dirty reads, non-repeated reads, and phantom reads. 2. Read Committed: this isolation level allows the current transaction to Read data that has been Committed by other transactions. Through the above analysis, this isolation level will lead to non-repeated reads and phantom reads. 3. Repeatable Read: This isolation level ensures that a specific record is Read multiple times in a transaction. Through the above analysis, this isolation level will lead to Phantom read. 4. Serializable: this isolation level places things in a queue. after each thing starts, other things are suspended. At the same time point, only one thing can operate on database objects. This isolation level is the highest for data integrity, but it also greatly reduces the system concurrency.
Possible problems corresponding to each level



Lock mechanism
In addition to transactions to handle non-repeated reads, phantom reads, dirty reads, and other issues. The above problems can also be solved by locking.Before unlocking, you need to have a perceptual knowledge of several common terms of locks.
Exclusive lock:
The lock added by the write table operation. after the lock is applied, other users cannot obtain any lock for the table or row. this is typically a mysql transaction. That is, you cannot query or modify a table.
For example, apply an exclusive lock to the user table.

start transaction; select * from user where userId = 1 for update;

Shared lock:
The lock added by the read table operation. after the lock is applied, other users can only obtain the shared locks of the table or row, but cannot obtain the exclusive locks. that is to say, the read/write exclusive locks and shared locks can only be applied to a database, what we usually hear is pessimistic locks, optimistic locks, row locks, and table locks.
Pessimistic locks are intended for developers or programmers. Pessimistic locks mean that concurrent access occurs at the same time during database operations. It is usually used when there are many updates and few queries. It corresponds to an optimistic lock, which is generally used when there are few updates and many queries.

Here, we cite a classic instance from the Internet to explain the practical use of transactions. Instance description
Scenario: My husband takes money from an ATM and his wife saves money at the counter. assume that the account has 1000 yuan. The husband first executes the query operation and finds that the account balance is 1000. at this time, the program will get 1000 to the memory, and the husband will get 200 yuan, and the program will execute the update operation to change the account balance to 800, however, when the husband's program does not have a commit, the wife queries the account, the account balance is still 1000 yuan, the wife saves 200 yuan, the program executes the update operation to change the account balance to 1200, then, the husband submits the update statement, and then the wife submits the update statement. The final result is that the account balance is 1200, which is an update loss problem. The root cause of the loss of updates is the query, because both parties update the data in the database based on the data queried from the database. There are three solutions to update loss: (1) set the transaction isolation level to the highest and adopt the deadlock policy. (2)Pessimistic lockPessimistic locks are not the real locks in databases, but attitudes towards transactions. (3)Optimistic lockOptimistic locks are not the real locks in the database.

If we use the first solution, the husband adds a shared lock to the table for the database query operation, and the wife adds a shared lock to the database for the query operation. However, when my husband updates the database, the husband cannot add the exclusive lock because her wife holds the shared lock. when she updates the database, the wife cannot obtain the exclusive lock because her husband holds the shared lock, there is a deadlock. you wait for me and I will wait for you. In mysql, the solution to handle deadlocks is to release the lock of one party. In this way, the update is successful, but the performance is extremely low, because the database frequently solves the deadlock problem.

Pessimistic lock (many updates, use when querying)

If we adopt the second solution, we adopt the pessimistic lock. We adopt a pessimistic attitude when operating the database, and think that others will access the database concurrently at this time. In the query statement, select * from account where name = 'AAA' for update; equals to an exclusive lock. When the husband queries the balance, select money from account where name = 'AAA' for update; adds the exclusion lock. when the wife queries the balance of the account, select money from account where name = 'AAA' for update; also requires an exclusive lock to the database. because the husband has obtained the exclusive lock, the wife cannot lock the database. Therefore, the wife can only wait for the execution of the lock, after the lock is released, the operation can continue.

Optimistic lock (fewer updates and more queries)

If we adopt the third scheme, that is, the optimistic lock, that is, when we operate the database, we will think that there is no concurrent access from other users, but the optimistic lock is not completely optimistic, optimistic locks are controlled by version number. There is a version number in the database table. When querying from the database, the version number is also queried. during the update operation, the version number is added to 1, and the version number of the query condition is also the queried version number. For example, when the husband executes the query operation, select money, version from account where name = 'aaa'; assume that the query version is 0, during the update operation, the husband updated the account set money = money + 100, version = version + 1 where name = 'AAA' and version = 0. when not submitted, the wife came to query, the queried version number is still 0, and the wife also performs the update operation update account set money = money + 100, version = version + 1 where name = 'AAA' and version = 0; now my husband has committed the transaction. when I submit the transaction again, I find that the record with the version number 0 is missing, so the data loss problem is avoided. However, this also results in multiple user update operations, and only one user's update is executed.

Appendix: default transaction level of each database

1. isolation level of default mysql transactions: repeatable read );

2. SQL server default transaction isolation level: read committed );

3. isolation level of oracle Default transactions: read committed ).