Database read and write concurrency control

In the database, concurrency control refers to how transactions are guaranteed to be consistent and isolated while simultaneously maximizing concurrency when multiple users/processes/threads operate on the database at the same time.

There are 3 conflicting scenarios when multiple users/processes/threads operate on the database at the same time:

1. Read-read, no problems exist
2. Read-write, have isolation problems, may encounter dirty read (will read UNCOMMITTED data), Phantom Read (repeat read) and so on.
3. Write-Write, possibly missing updates

One way to resolve conflicts is to lock-based concurrency control, such as 2PL, which is expensive and does not prevent deadlocks.

Multi-version concurrency control (MVCC) is a lock-free concurrency control that is used to resolve read-write collisions , which is to allocate a one-way growth timestamp for a transaction, a version for each modification, a version associated with a transaction timestamp, and a read-only snapshot of the database before the transaction begins. This prevents the read operation from blocking the write operation, while the write operation avoids the dirty read and non-repeatable reads without blocking the read operation

Optimistic concurrency control (OCC) is a lock-free concurrency control used to solve write-write collisions , arguing that there is not so much contention between transactions, so make a change before committing the transaction, check for the start of the transaction, have no new commit changes, if there is no commit, discard and retry if there is one. Optimistic concurrency control is similar to an optional lock. Optimistic concurrency control is suitable for low-data contention, with less write-conflict environments.

Pessimistic locking, as its name implies, is a conservative attitude to the data being modified by the outside world (including other transactions currently in the system, as well as transactions from external systems), so that the data is locked during the entire data processing process. Summarize:

Pessimistic lock (pessimistic lock), as the name implies, is very pessimistic, every time to take the data when they think others will change, so every time when the data are locked, so that others want to take this data will block until it gets the lock. Traditional relational database in the use of a lot of this locking mechanism, such as row locks, table locks, read locks, write locks, etc., are in operation before the lock.

Optimistic lock (optimistic lock), as the name implies, is very optimistic, every time to take the data when they think others will not be modified, so will not be locked, but in the update will be judged in the period when others have to update this data, you can use the version number and other mechanisms. Optimistic locking is useful for multi-read application types, which can improve throughput, such as the fact that a database provides an optimistic lock similar to the write_condition mechanism.

The two kinds of locks have advantages and disadvantages, not to think of one better than the other, like the optimistic lock for less write, that is, the conflict really rarely occurs, this can save the lock overhead, increase the overall system throughput. However, if there is frequent conflict, the upper application will continue to retry, which is to reduce the performance, so in this case, pessimistic locking is more appropriate.

Issues that occur when there is no isolation of the transaction:

Dirty read: (read not committed at the same time)

Dirty Read is also called invalid data read out. A transaction that reads another transaction that has not yet committed data is called dirty read.

For example, transaction T1 modifies a row of data but has not yet committed, at which point the transaction T2 reads the data modified by the transaction T1, and after the transaction T1 for some reason rollback, the data that the transaction T2 reads is dirty.

Workaround: Adjust the transaction isolation level of the database to read_committed

Non-repeatable READ: (simultaneous operation, transaction one reads transaction two operation and post-commit data, read record content inconsistent)

Non-repeatable reads refer to two identical queries returning different results within the same transaction.

For example: Transaction T1 reads a data, transaction T2 reads and modifies the data, and T1 reads the data again in order to verify the read value, it gets different results. Workaround: Adjust the transaction isolation level of the database to Repeatable_read

Phantom reads: (similar to repeatable reads, but transaction two data operations are only insertions and deletions, not modified data, and the number of records read is inconsistent)

For example: System administrator a changes the scores of all students in the database from the specific score to the ABCDE level, but system Administrator B is inserted at this time (note insert or delete, not modified)) a specific score of the record, when the system administrator a after the end of a change to find a record has not changed, It's like a hallucination happened. This is called Phantom Reading.

Workaround: Adjust the transaction isolation level of the database to Serializable_read

Database read and write concurrency control