Discussion on the transaction and concurrency issues in the database and the transaction concurrency issues

Discussion on the transaction and concurrency issues in the database and the transaction concurrency issues
Introduction to transactions and concurrency in Databases

Recently, a colleague wrote a code segment to create the order logic. The Code review may cause concurrency issues. My colleague does not agree. He thinks that his logic is written in the stored procedure and should be correct.

The logic of the Code is (pseudo code ):

Begin transactionif query to the order rollback transaction inserted to the order commit transaction

The following is an analysis of the logic. Why is the transaction concurrency.

Transaction Overview

First, two questions are raised, and then the transaction-related knowledge points are discussed. Finally, the two questions are solved and the previous questions are answered.

The first question is, can a transaction be concurrent?

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

Transaction performance characteristics

The execution of transactions in the database involves many aspects, including how to handle critical resources and how to unlock locks. However, no matter how the transaction is executed, the following features must be ensured:

• Atomicity
• Consistency
• Isolation
• Durability

Atomicity: all operations are a logical unit, either submitted successfully or failed;

Consistency: only valid data is written to the database. Otherwise, the transaction rolls back to the initial state;

Isolation: Allows multiple transactions to be performed at the same time without compromising data correctness and integrity;

Persistence: after the transaction ends, the submitted results are solidified and saved.

Various database locks

1. Shared lock

A shared lock is used for a non-exclusive business. Multiple transactions are allowed to read the locked resources at the same time, but the resources cannot be updated.

• Lock time: executedselectThe statement is added by default.
• Unlock time: Unlock by default after reading
• Compatibility with other locks: Shared locks and exclusive locks are not allowed if the data is configured with shared locks.
• Concurrency performance: excellent concurrency Performance

2. Exclusive lock

Exclusive locks are also called exclusive locks. As the name suggests, resources locked by exclusive locks do not allow other transactions to perform any operations.

• Lock time: executedinsert,update,deleteIs added by default.
• Unlock time: the transaction can be terminated
• Compatibility: if there are other locks on the data, you cannot add an exclusive lock. If an exclusive lock exists, other locks are not allowed.
• Concurrent performance: other transactions can only be executed after the previous transaction ends.

3. Update lock

In the initial phase of the update, the required resources are locked to prevent deadlock caused by the use of shared locks in the read phase.

• Lock time: executedupdateUse the update lock to lock related resources.
• Unlock time: the read is complete. When the update operation is executed, the update lock is upgraded to an exclusive lock.
• Compatibility: Update locks are compatible with shared locks, that is, update locks and shared locks can exist simultaneously, but only one update lock can exist.
• Concurrent performance: the read phase at the early stage of the update allows other transactions to read resources, and allows limited concurrency. in the later stage, resources are exclusively occupied and no concurrency is allowed.

Transaction isolation level

There are four general transaction isolation levels, and SQL Server has another extended level, which is not described here.

1. Serializable)

The operation method is similar to repeated read. However, it not only locks the affected data, but also locks the range. This prevents the scope involved in the new data insertion query, which can lead to phantom reading.

2. Repeatable Read (Repeatable Read)

Reads data as if it had already been committed, but will keep the shared lock until the transaction ends.

3. Read Commit

Read Only committed data and wait for other transactions to release the exclusive lock. The shared lock of read data is released immediately after the read operation is completed. Committed read is the default isolation level of SQL Server.

4. Read Uncommited

No lock is checked or used during data reading. Therefore, the uncommitted data may be read at this isolation level.

Answer previous questions

The first question is, can a transaction be concurrent?

The answer is yes. in the database, to improve performance, multiple transaction operations are allowed at the same time. This transaction has nothing to do with the initiation method. It is initiated using a stored procedure or using code, or, there is no difference in using a common SQL statement to initiate.

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

To answer this question, we must first understand the lock mechanism in the database and the transaction isolation level in the database. There are three types of locks in the database: Shared locks, exclusive locks, and update locks. Different levels of locks have been used and different lock ranges have reached different transaction isolation levels, and concurrent or serial transaction execution is performed on this basis.

The third question is, why does the transaction at the beginning of this article have a concurrency problem?

Because the start of the transaction isselectThe select statement uses a shared lock and may execute concurrent transactions at the same time.selectAs a result, the system considers itself as a legal operation and queues for subsequent transactions. As a result, duplicate data may be inserted. For example, if there is only one commodity, two sales orders are created.

How to Prevent concurrency Problems

1. In the transaction

According to the previous articleInsert, update, or deleteTransactions can be serialized manually at the default transaction level, so they can be used at the beginning of the transaction.updateUpdate a public data record so that all transactions of the same type are serialized. 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 justified and valid. The only drawback is that performance problems may occur.

2. Outside the transaction

There are more and more distributed systems, but distributed systems also share some resources, such as redis or zookeeper. You can use redis or zookeeper to create some distributed locks (this is part of other blog posts, do not expand here ). The external locks of transactions are used to perform some serial processing for the same type of transactions, and the internal check mechanism is used together to ensure that the transaction concurrency problem is solved.

References

• Transaction concurrency problems and handling
• Four features of database transactions and isolation levels of transactions
• Database transactions and concurrency
• SQL Server transaction isolation level