MySQL database transaction isolation levels (Transaction isolation level)

Turn from: http://www.cnblogs.com/zemliu/archive/2012/06/17/2552301.html There are four levels of database isolation, as described in the "high Performance MySQL" book:

Then talk about how to modify the transaction isolation level:

1. Global modification, modify the Mysql.ini configuration file, at last add

1 #可选参数有: read-uncommitted, read-committed, Repeatable-read, serializable.2 [mysqld]3 transaction-isolation = Repeatable-read

Here the global default is Repeatable-read, in fact, MySQL is the default is this level

2. Modify the current session, after logging in to the MySQL client, execute the command:

To remember that MySQL has a autocommit parameter, the default is on, his role is that each individual query is a transaction, and automatically start, auto-commit (after the execution is finished automatically, if you want to apply the select for update, not manually call start Transaction, the row lock mechanism for the for update is useless because the row lock is freed after autocommit, so the transaction isolation level and locking mechanism are also applicable in a single query statement even if you do not explicitly call start transaction. Be sure to pay attention to this when analyzing the operation of the lock.

Again, the lock mechanism:
shared lock : The lock that is added by the Read table operation, after locking, the other user can only get the shared lock of the table or row, can not get the exclusive lock, that is, can only read can not write

Exclusive Lock: The lock that is added by the Write table operation, the other user cannot get any lock of the table or row after lock, typical is in MySQL transaction

Start transaction;

SELECT * from user where userId = 1 for update;

After executing this sentence

1) When other transactions want to acquire shared locks, such as transactions with a transaction isolation level of Serializable, execute

select * from user;

will be suspended because the serializable SELECT statement needs to acquire a shared lock

2) When other transactions are executed

SELECT * from user where userId = 1 for update;

Update user Set userage = n where userId = 1;

is also suspended because the for update acquires an exclusive lock on this row of data and needs to wait until the previous transaction releases the exclusive lock before it can proceed

Range of Locks:

row Lock: adds a lock to a row of records

table Lock: Lock the entire table

This is combined to include row-level shared locks, table-level shared locks, row-level exclusive locks, table-level exclusive locks

The following is an example of different transaction isolation levels, using INNODB, open two client, a, B, modify the transaction isolation level in a, open a transaction in B, modify the data, and then view the transaction modification effect of B in the transaction in a:

1.read-uncommitted (Read UNCOMMITTED content) level

1) A modify the transaction level and start the transaction, making a query to the user table

　　　

2) b Update a record

　　　

3) At this time B transaction has not been committed, a within the transaction to do a query, found that the query results have changed

　　　

4) b for transaction rollback

　　　

5) A To do another query, the results of the query turned back

　　　

6) A table changes the user table data

　　　

7) The B table restarts the transaction, modifies the user table record, the modification is suspended until time-out, but the modification to the other data succeeds, stating that A's modification adds a row shared lock to the data row of the user table (because a select can be used)

　　　

You can see the read-uncommitted isolation level, which is obviously unsafe when two transactions are in progress, even if the transaction is not committed, and the changes will affect queries within the transaction. However, when the table modifies a row, a row-sharing lock is added to the row

2. read-committed (read submit content)

1) Set the transaction isolation level of a and enter the transaction to make a query

　　　

2) b Start the transaction and modify the record

　　　

3) A then query the user table to find that the records are not affected

　　　

4) B Commit Transaction

　　　

5) A again to the User table query, the discovery record is modified

　　　

6) A Modify the user table

　　　

7) b Restart the transaction and modify the same bar of the user table to find that the modification was suspended until the time-out, but the modification to another record was successful, stating that A's modification added a row-sharing lock to the user table (because you can select)

　　　

　　　

Read-committed The transaction isolation level, which affects another transaction only after the transaction commits, and row-sharing locks are added to table data rows when the table is modified

3. Repeatable-read (can be reread)

1) A set the transaction isolation level and query once after entering the transaction

　　　

2) b Start the transaction and modify the user table

　　　

3) A View user table data, the data has not changed

　　　

4) B Commit Transaction

　　　

5) A another query, the results are still unchanged

　　　

6) A after committing the transaction, and then review the results, the results have been updated

　　　

7) A re-start the transaction and modify the user table

　　　

　　　

8) The B table restarts the transaction and modifies the user table, the modification is suspended until the time-out, and the modification to the other record succeeds, indicating that a row shared lock is added when the table is modified (can select)

　　　

　　　

Repeatable-read transaction ISOLATION level, when two transactions are in progress, one of the transaction modification data does not affect another transaction, even if the modified transaction has been committed and does not affect the other transaction.

When a record is modified in a transaction, a row-shared lock is added to the record until the end of the transaction is released.

4.SERIERLIZED (Serializable)

1) Modify the transaction isolation level of a and make a query

　　　

2) b Query the table, the normal results, that the user table query can be carried out

　　　

3) b Start the transaction, and make changes to the record, because a transaction is not committed, so the modification of B is waiting, waiting for a transaction to end, and finally timeout, indicating a after doing a query operation on the user table, the table was added a shared lock

　　　

The serializable transaction isolation level is the most severe, and when a query is made, a shared lock is added to the table or row, and other transactions are read-only to the table and cannot be written.

MySQL database transaction isolation levels (Transaction isolation level)