Examples of transaction and concurrency issues in databases

Discussion on transactions and concurrency issues in databases

Introduction

Recently, a colleague wrote a piece of code responsible for the logic of creating orders. During the code review, it was discovered that there may be concurrency issues. My colleague disagreed. He thought that his logic was written in stored procedure, so there should be no problem.

The logic of the code is probably (pseudocode):

begin transaction

if 查询到客户存在进行中的订单
 rollback transaction

if 查询到设备存在进行中的订单
 rollback transaction

插入订单

commit transaction

The following will analyze this logic and why this transaction will have concurrency problems.

Transaction Overview

First, ask two questions, then discuss the knowledge points related to the transaction with the questions, and finally solve these two questions and answer the previous questions.

The first question, can transactions be concurrent?

The second question, how does the database isolate transactions?

Performance characteristics of transactions

Executing transactions in the database involves many aspects, including how to handle critical resources, how to lock and unlock, etc. However, no matter how the transaction is executed, the following characteristics need to be guaranteed:

• Atomicity

• Consistency

• Isolation

• Persistence

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

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

Isolation: Allows multiple transactions to proceed at the same time without destroying the correctness of the data Persistence and integrity;

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

Various locks of database

1. Shared lock

Shared lock is used for non-exclusive business and allows multiple transactions Also reads the locked resource, but does not allow the resource to be updated.

Locking timing: When executing the select statement, it will be added by default

Unlocking timing: Released by default after executing the read

Compatibility with other locks: If a shared lock is set on the data, no more shared locks and exclusive locks will be allowed to be added

Concurrency performance: has good concurrency Performance

1. Exclusive lock

Exclusive lock, also called exclusive lock. As the name suggests, a resource locked by an exclusive lock will not allow other transactions to perform any operations.

Locking timing: When executing insert, update, delete, it will be added by default

Unlocking timing: Can only be released after the transaction is completed

Compatibility: If there are other locks on the data, exclusive locks cannot be added; similarly, exclusive locks will not allow other locks to be added when they exist

Concurrency performance: Other transactions must wait for the end of the previous transaction before they can be executed. They cannot be concurrent and can only be serialized

1. Update lock

Used to lock the required resources in the initial stage of update to prevent deadlock caused by using shared locks in the reading stage.

Locking timing: When executing update, use update lock to lock related resources

Unlocking timing: After reading, when performing update operation, update lock is upgraded For exclusive locks

Compatibility: update locks are compatible with shared locks, that is, update locks and shared locks can exist at the same time, but there can only be one update lock

Concurrency performance: In the early reading phase of the update, other transactions can be allowed to read resources and limited concurrency is allowed; concurrency is not allowed when the resources are exclusive in the later stage.

Transaction isolation level

There are four general transaction isolation levels, and SQL Server has additional extended levels, which will not be introduced here.

1. Serializable

Works like repeatable read. But it doesn't just lock the affected data, it also locks the range. This prevents new data from being inserted into the range covered by the query, a situation that can lead to phantom reads.

1. Repeatable Read(repeatable read)

Read data like committed read level, but will maintain shared locks until the end of the transaction.

1. Read Commit

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

1. Read Uncommited

No locks are checked or used when reading data. Therefore, it is possible to read uncommitted data in this isolation level.

Answer the previous question

The first question, can transactions be concurrent?

The answer is yes, in order to improve performance, the database allows multiple simultaneous Transaction operation, this transaction has nothing to do with the initiation method. It makes no difference to use stored procedures to initiate, or to use code to initiate, or to use ordinary SQL statements to initiate.

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

To answer this question, you must first understand the lock mechanism and database transaction isolation level in the database. Locks in the database can be divided into three types: shared locks, exclusive locks and update locks. Use different levels of locks and cooperate with different locking scopes to achieve different transaction isolation levels and execute transactions concurrently or serially on this basis.

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

Because select is executed at the beginning of the transaction, and select uses a shared lock, it is possible that concurrent transactions execute select at the same time, causing them to think that they are all legal operations at the same time, and queue up to execute subsequent transactions. As a result, it is actually possible to insert duplicate data, for example, there is only one product left, but two sales orders are created.

How to prevent concurrency problems

1. In transactions

According to what was said before, using insert, update or delete can be used in transactions The default transaction level artificially causes transaction serialization, so you can use update at the beginning of the transaction to update a common data. In this case, transactions of the same type will be serialized, and then add a judgment statement, Used to determine whether subsequent transaction content should be executed. This is enough to ensure that all operations are performed reasonably and legally. The only disadvantage is that it may cause performance problems.

1. Outside of transactions

There are more and more distributed systems, but redistributed systems will also have some shared resources, such as redis Or zookeeper, you can use redis or zookeeper to create some distributed locks (this type belongs to other blog posts and will not be expanded upon here). Using locks outside the transaction to serialize transactions of the same type, and then cooperating with the internal checking mechanism of the transaction, is enough to ensure that the concurrency problem of the transaction is solved.

Reference materials

Transaction concurrency issues and processing

Four major characteristics of database transactions and transaction isolation levels

Database transactions and concurrency

Isolation level of SQLServer transactions

The above is the detailed content of Examples of transaction and concurrency issues in databases. For more information, please follow other related articles on the PHP Chinese website!