Mysql+spring database Isolation level and performance analysis _mysql

Here, for example, MySQL, first clear the following questions:

I. GENERAL projects if you do not configure the transaction itself, the general default is autocommit, that is, after performing an operation automatically commit, commit the transaction.

(Note: Transactions are bound to database operations, i.e. when a program executes (Statement.excute, etc.) to the database level, the transaction begins to occur)
Of course, spring can bind several database action actions to a transaction, so that you need to introduce the next spring transaction configuration method, the following is a common method, the other methods there are many online.
Spring provides a number of transactional configuration strategies that are convenient and brief:

Copy Code code as follows:

<property name= "Transactionattributes" >
<props>
<prop key= "save*" >PROPAGATION_REQUIRED</prop>
<prop key= "update*" >PROPAGATION_REQUIRED</prop>
<prop key= "delete*" >PROPAGATION_REQUIRED</prop>
<prop key= "get*" >PROPAGATION_REQUIRED,readOnly</prop>
<prop key= "find*" >PROPAGATION_REQUIRED,readOnly</prop>
</props>

General Spring configuration transactions are the above collocation method, the meaning of specific parameters do not understand the Internet to check it, then you need to pay attention to the following points: (digression)
1. I am accustomed to the transaction configuration in the service, it should be noted that only in the service to save, update, and so the beginning of the method, configured transactions only effect. If the method name in the service does not start with save, such as the Tasksave () method, even if the Update method in the service is invoked in the implementation class, the configuration transaction fails, as I have tried.
2.readOnly This property is interesting because it automatically increases the isolation level of the database by one level, from the commit read to the repeat read, which I will explain later.

Two. Database Isolation Level

There are four main database isolation levels: Non-submit read, submit read, repeat read, and serialized read (the following comprehension may not be read).
1. isolation_read_uncommitted: This is the lowest isolation level for a transaction, which allows an external transaction to see uncommitted data for this transaction.
This isolation level produces dirty reads, no repeat reads, and Phantom reads.
2. isolation_read_committed: Ensure that a transaction modified by the data submitted before being read by another transaction. Another transaction cannot read data that is not committed by the transaction
3. Isolation_repeatable_read: This transaction isolation level prevents dirty reads and cannot be read repeatedly. However, Phantom reads may occur.
In addition to ensuring that one transaction cannot read data that is not committed by another transaction, it also ensures that the following conditions are avoided (non-repeatable reads).
4. Isolation_serializable This is the highest cost but the most reliable level of transaction isolation. Transactions are processed for sequential execution.
In addition to preventing dirty reading, it is not repeatable to read, but also avoid phantom reading.
The default isolation level for MySQL is a repeat, that is, Isolation_repeatable_read.

Note: Uncommitted reading and serialization reading are not commonly used, and uncommitted reading is too risky to read a lot of dirty data. Serializable reading is achieved by locking every row of data that is read, at the expense of performance, so there is little use, and most databases are read-committed, such as Oracle, SQL Server. The MySQL default data isolation level is repeatable read.

Now I'll combine the project to analyze the following adjustment database isolation level impact on performance:
The local MySQL database is lowered from the isolation_repeatable_read level to the isolation_read_committed level:

Scenario: Without spring, User A makes two query requests to the database in one transaction, and User B modifies the records of the database between two queries.

Result: Isolation_repeatable_read level: User A two times query results are different.

Isolation_read_committed level: User A two query results, because the record is locked.

The

takes the task module as an example, running the tasks on the home page, by contrasting and analyzing the two transaction methods to get the following results (each statistic data before cleaning the browser cache, statistics 3 times average):


" to reduce the isolation level of database transactions, for some special logic operations, performance improved.
However, if the query process does not involve multiple complex logic for database operations in the same transaction and the logic of querying the same result set multiple times in the same transaction, the speed of ascension is not obvious, that is, transactions in the time to lock the dataset can be ignored, the following to understand the transaction isolation level and the relationship between the lock.

When it comes to the database isolation level, let's say the concept of a lock:

is mainly divided into shared and exclusive locks.
Shared Lock: A lock added by a read table operation that other users can only get shared locks on that table or row, cannot get exclusive locks, that is, read-only cannot write

exclusive locks: Locks added by write-table operations, After lock, other users cannot get any locks on the table or row, typically in MySQL transactions.

Personal Understanding: There is no strict boundary between shared and exclusive locks, and I think it should be determined by the results to add a shared or exclusive lock.

For example: User A modifies a piece of data, and User B modifies this data and hangs. But b check this data to prove that a user has added row-level shared locks.

Another example: User A modifies a piece of data, User B fails to query the data, and queries other data fails. So sure a adds table-level exclusive locks.

Another example: User A modifies a piece of data, User B queries the record, but this record cannot be modified, no other records are modified, and a table-level shared lock is added.

Different data isolation levels, plus locks are not the same.

Back to the previous question, once the ReadOnly property is set, if the database level is submitted for read, then the same transaction, if a query to two result sets, the intermediate interval to modify the database, it should be the same result set, which is equivalent to the query at the time of the duplicate read isolation level.