Differences and connections between transaction isolation and locks

The transaction isolation level is the overall solution for concurrency control, which is actually a combination of various types of lock and row versioning to solve concurrency problems.

The lock is the internal mechanism of database concurrency control and is the foundation. Of course, the database also uses row versioning (SQL Server 2005 and above) for concurrency control, as well as the use of latches (latch), mutexes, and other mechanisms within the database to handle concurrent access to internal resources such as caching.

For users, it is necessary to manually set a lock in a statement only if the transaction isolation level does not resolve some concurrency problems and requirements. Improper setting of locks may result in serious blocking and deadlock. It is recommended that you manually set a lock in a statement only if you fully understand the lock mechanism, or you should use the transaction isolation level.

http://bbs.csdn.net/topics/340190533 (9/F)

The following article visually sees the isolation level and the use of locks at different isolation levels through practical operations:

There are four levels of database isolation, as described in the book High performance MySQL:

Then tell me how to modify the transaction isolation level:

1. Global modification, modify Mysql.ini configuration file, at the end Plus

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

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

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

Remember that MySQL has a autocommit parameter, the default is on, and his role is that each individual query is a transaction and automatically starts, autocommit (automatically ends after execution, if you want to apply select for update without manually calling start Transaction, the row lock mechanism for update is useless because the row lock is released after it is automatically committed, so the transaction isolation level and lock mechanism are applicable in a separate query statement even if you do not explicitly call start transaction. You must pay attention to this when analyzing the operation of the lock.

Again, lock mechanism:
shared lock : A lock that is added by a read table operation, and other users can only get shared locks on that table or row, and cannot get exclusive locks, which means they can only read and write

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

Start transaction;

SELECT * from user where userId = 1 for update;

After this sentence is done

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 performed

SELECT * from user where userId = 1 for update;

Update user Set userage = where userId = 1;

is also suspended because the for update gets the exclusive lock on this row of data and waits until the previous transaction releases the exclusive lock before it can proceed

Scope of the Lock:

row Lock: lock on a row record

table Lock: Lock the entire table

This can be combined, row-level shared locks, table-level shared locks, row-level exclusive locks, table-level exclusive locks

The following is a different transaction isolation level instance effect, with the example using InnoDB, opening two client a,b, modifying the transaction isolation level in a, opening the transaction in B and modifying the data, and then viewing the transaction modification effect of B in the transaction in a:

1.read-uncommitted (Read UNCOMMITTED content) level

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

　　　

2) b Update a record

　　　

3 At this time the B transaction has not been submitted, a query within the transaction, found that the query results have changed

　　　

4) b Transaction rollback

　　　

5) A again query, the results of the query changed back

　　　

6) A table to modify the user table data

　　　

7 B Table After restarting the transaction, the user table record is modified, the modification is suspended until the timeout, but the modification of another data succeeds, stating that a modifies the data row of the user table to share the lock (because you can use Select)

　　　

You can see that the Read-uncommitted isolation level, when two transactions are in progress, even if the transaction is not committed, the changes will affect the query within the transaction, this level is clearly not secure. However, when a table modifies a row, it adds a row-sharing lock to the row

2. read-committed (read submission)

1 set a transaction isolation level and enter a transaction to do a query

　　　

2) b Start the transaction and modify the records

　　　

3) A again query the user table, found that the record is not affected

　　　

4) b Submitting the transaction

　　　

5) A again to the User table query, found that the record was modified

　　　

6) A modifies the user table

　　　

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

　　　

　　　

Read-committed transaction isolation level, which affects another transaction only after a transaction commits, and when a table is modified, the table data rows are shared with rows

3. Repeatable-read (can be reread)

1) A sets the transaction isolation level and queries once after entering the transaction

　　　

2 b Start the transaction and modify the user table

　　　

3 A view of the user table data, the data has not changed

　　　

4) b Submitting the transaction

　　　

5) A one more query, the result remains unchanged

　　　

6) A commits the transaction, then looks at the result and the result has been updated

　　　

7) A restarts the transaction and modifies the user table

　　　

　　　

8 Table B Restarts the transaction and modifies the user table, the modification is suspended until the timeout, and the modification to the other record succeeds, stating that A is modified with a row shared lock (can be Select)

　　　

　　　

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

Modifying a record in a transaction adds a row share lock to the record, which is not released until the end of the transaction.

4.SERIERLIZED (Serializable)

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

　　　

2 b to the table query, the normal results, you can see that the User table query is possible

　　　

3 b start the transaction and make changes to the record, because a transaction is not committed, so B's modification is waiting, waiting for a transaction to end, and finally time out, stating that A has a shared lock on the table after a query operation on the user table