Discussion of transaction and concurrency problems in database

Discussion of transaction and concurrency problems in database

Recently, a colleague wrote a piece of code that was responsible for creating the logic of the order, and the code review found that there might be concurrency problems. Colleagues do not agree that he believes his logic is written in the stored procedure, should be no problem.

The logic of the Code is presumably (pseudo-code):

begin transactionif 查询到客户存在进行中的订单     rollback transactionif 查询到设备存在进行中的订单 rollback transaction 插入订单commit transaction

The following is an analysis of this logic, and why there is a concurrency problem with this transaction.

Transaction overview

First of all, ask two questions, then take the problem to discuss the relevant knowledge points, finally to solve the two problems and answer the previous question.

First question, can a transaction be concurrent?

The second question is, how does the database isolate the transaction?

Performance characteristics of a transaction

There are many aspects involved in executing transactions in a database, including how to handle critical resources, such as Hega unlocking, and so on. However, no matter how the transaction executes, there are several features that need to be guaranteed:

• Atomic Nature
• Consistency
• Isolation of
• Durability

Atomicity: All operations are a logical unit that either commits successfully or fails;

Consistency: Only legitimate data is written to the database, otherwise the transaction is rolled back to its original state;

Isolation: Allows multiple transactions to be performed simultaneously without destroying the correctness and completeness of the data;

Persistence: After the transaction has ended, the submitted results are cured.

Various locks on the database

1. Shared locks

A shared lock is used for a non-exclusive business, allowing multiple transactions to read a locked resource at the same time, but does not allow resources to be updated.

• Lock time: When executing a select statement, it is added by default
• Unlock time: Default cancellation after read execution
• Compatibility with other locks: shared locks are set on the data and no additional shared and exclusive locks are allowed
• Concurrency performance: good concurrency

2. Exclusive lock

An exclusive lock, also called an exclusive lock. As the name implies, a resource that is locked by an exclusive lock does not allow other transactions to do anything.

• Lock time: Execution insert,update,delete will be added by default
• Unlock time: End of transaction to be lifted
• Compatibility: If there are other locks on the data, no exclusive locks can be added, and no additional locks are allowed when the same exclusive lock exists
• Concurrency performance: Other transactions must wait for the previous transaction to finish before they can be executed, not concurrently, but serially

3. Update lock

The initial phase of the update is used to lock in the required resources, preventing the use of shared locks to cause deadlocks during the read phase.

• Lock time: update lock related resources with update lock when executing
• Unlock opportunity: The update lock is upgraded to an exclusive lock when the update operation is completed
• Compatibility: Update locks are compatible with shared locks, i.e. update and shared locks can exist at the same time, but only one update lock
• Concurrency performance: The initial read phase of the update allows other transactions to read resources, allows for limited concurrency, and does not allow concurrency when the resource is exclusive at a later time.

Transaction ISOLATION LEVEL

There are four common levels of transaction isolation, and SQL Server has a different level of extensibility, not much to describe here.

1. Serializable (serialization)

Works similarly to repeatable reads. But it will not only lock the affected data, but also lock the range. This prevents new data from being inserted into the scope of the query, which can lead to phantom reads.

2. REPEATABLE READ (Repeatable Read)

Reads the data as read-committed, but retains the shared lock until the transaction ends.

3. Read Commit

Only read committed data and wait for other transactions to release exclusive locks. Shared locks for read data are released as soon as the read operation is complete. Read Committed is the default isolation level for SQL Server.

4. Read uncommited

No locks are checked or used when reading data. Therefore, data that is not committed may be read in this isolation level.

Answer the questions in the previous article

First question, can a transaction be concurrent?

The answer is yes, in order to improve performance in the database, allowing multiple transactional operations at the same time, the transaction is independent of the initiating method, initiated with a stored procedure, or initiated with code, or is not distinguished by the use of ordinary SQL statements.

The second question is, how does the database isolate the transaction?

To answer this question, first understand the locking mechanism in the database and the database transaction isolation level. Locks in the database can be divided into three types: shared, exclusive, and update locks. Using different levels of locks and cooperating with different locking ranges has reached different levels of transaction isolation and executed transactions on this basis concurrently or serially.

Third question, why is there a concurrency problem with the transaction at the beginning of this article?

Since the start of the transaction is select that the select uses a shared lock, it is possible that concurrent transactions are queued to select perform subsequent transactions at the same time that cause both of them to be considered legitimate operations. As a result, it is possible to actually insert duplicate data, such as leaving only one item, but creating two sales orders.

How to prevent concurrency problems

1. In the transaction

As mentioned earlier, using insert,update或delete can artificially cause transactions to be serialized at the default transaction level, so you can use the update of a common data at the beginning of a transaction update so that the same type of transaction is serialized, and then a judgment statement is added to determine whether the subsequent transaction content should be executed. This is sufficient to ensure that all operations are reasonable and legitimate, and that the only drawback is that it can cause performance problems.

2. Outside the transaction

There are more and more distributed systems now, but the redistribution of the system will also have some shared resources, such as Redis or zookeeper, can use Redis or zookeeper to create some distributed locks (this category belongs to other blog content, no longer expand). The use of locks outside the transaction to do some serialization of the same type of transaction, together with the check mechanism inside the transaction, is sufficient to ensure the resolution of the concurrency problem of the transaction.

Resources

• Problems and handling of transaction concurrency
• Four characteristics of database transactions and the isolation level of transactions
• Database Transactions and concurrency
• Isolation level for SQL Server transactions

Discussion of transaction and concurrency problems in database