Database transaction isolation and lock, database transaction isolation

Database transaction isolation and lock, database transaction isolation
Overview

I have learned about database transaction isolation and lock. Now I want to explain my understanding.

Database transaction isolation Concept

Transaction: Multiple SQL statements are used as a whole to interact with the database. Note that a transaction can have multiple SQL statements. Although the final result of these SQL statements becomes a whole, success and failure are required, each SQL statement is executed in a single statement.

Database transaction isolation: it refers to the time when the execution result of a single select statement in one transaction is in another transaction between multiple transactions (operate the same table.

Transaction isolation level

Read Uncommitted: The Read is not committed. The select statement in the transaction queries the table and the result is the time when other transactions are not committed, that is, the select statement of transaction 1 in table 1, the result of the Update Statement of transaction 2 is found, although the statement after update in transaction 2 is not executed yet.

Read Committed, transaction 1 does not read the result.

Repeatable table: Repeatable table. The result read by select in table 1 in transaction 1 remains unchanged. Note: if there is an update operation and table 1 is read again, at this time, we read committed data. Therefore, this isolation level may generate phantom read (this concept will be discussed later ).

Serializable: serialization and transaction problems are all caused by the fact that the database allows multiple transactions to be executed in parallel. However, if we allow the database to execute transactions in a serial manner, no problem will occur, serializable is the isolation level.

Dirty read, phantom read, Repeatable read, and non-repeated read

These concepts are also generated by the concurrency of multiple transactions. Therefore, when understanding these concepts, you must consider the situation where multiple transactions are executed at the same time.

Dirty read: Transaction 1's select query (a record) on table 1 reads the content after the update result of transaction 2's table 1 (this record). At this time, transaction 2 rolls back, at this time, the data read by transaction 1 is dirty data, which is called dirty read, that is, the content of this record is not true.

Phantom read: After transaction 1 obtains the result (one record) from the select query on table 1, transaction 2 inserts one record on table 1. Then, transaction 1 uses the select statement to query the results (two records) and finds that the amount of information has changed. This is a phantom read, that is, the original information content has not changed, but the amount is different.

Repeated read: After the select query of Table 1 in transaction 1 returns the result, the select query after transaction 1 remains unchanged (no update statement was previously executed), which is called repeat. The opposite is non-repeated read.

Database lock

Only the features of shared locks, update locks, and exclusive locks are described.

Relationship between shared locks, update locks, and exclusive locks

A single resource can have multiple shared locks. After an exclusive lock is applied to a single resource, no other locks can be applied. After a single resource has other locks (shared locks or update locks, you cannot directly add an exclusive lock to this resource. A single resource can only have one update lock. A single resource can also add a shared lock to the resource when there is an update lock, the update lock of this resource can be upgraded when the shared lock exists and converted to the exclusive lock;

Deadlock

A single SQL statement has a lock. When the default select statement is executed, a shared lock is applied to the resource. Once the select statement is executed successfully, the shared lock is no longer available, even if the default select statement is in the transaction, at this time, it is easy to create a deadlock, so the execution time of a single select statement is too long and the number of executions is too large. At this time, the table content will be updated, it can be executed only when there is no shared lock in the table. In this case, we can use a single select statement without the shared lock.

The updated SQL statement is an exclusive lock. When the update statement is in a transaction, the lifecycle of the lock changes to the same length as that of the transaction. Pay attention to the order of resource access, it may cause deadlocks.

Summary

The preceding content is not complete, for example, the impact of the transaction isolation level on dirty reads, Phantom reads, and non-repeated reads. The deadlocked content can be connected to SQL Server for experiment.