[Database Transactions and locks] detailed four: The lock mechanism of the database

Note: This article is reproduced from http://www.hollischuang.com/archives/898

The analysis of the reading phenomenon of the database has been introduced, in the case of concurrent access, there may be dirty read, non-repeatable read and read the Phantom read phenomenon, in order to deal with these problems, the mainstream database has provided a lock mechanism, and introduced the concept of transaction isolation level.

concurrency control

In computer science, especially in the fields of program design, operating system, multiprocessor and database, concurrency control ( Concurrency control ) is a mechanism to ensure that errors caused by concurrent operations are corrected in a timely manner.

The task of concurrency control in a database management system (DBMS) is to ensure that multiple transactions concurrently access the same data in the database without disrupting the isolation and uniformity of the transaction and the consistency of the database. The following is an example of the data inconsistency problem caused by concurrent operations:

There are two tickets available at the ticket counter, while the ticket balance in the database for a train ticket is X. Two ticketing points sell a ticket at the same time, while the change balance is X-1 write back to the database, which results in the actual sale of two train tickets and the database records are only one missing. This occurs because two transactions are read into the same data and modified at the same time, and the result of one transaction commit destroys the result of another transaction commit, causing the modification of its data to be lost, which destroys the isolation of the transaction. Concurrency control is the kind of problem to solve.

Blocking, timestamp, optimistic concurrency control (optimistic lock) and pessimistic concurrency control (pessimistic lock) are the main techniques used in concurrency control.

Lock

When concurrent transactions access a resource at the same time, it is possible to cause inconsistent data, so a mechanism is needed to order data access to ensure consistency of database data. A lock is one of the mechanisms.

In computer science, a lock is a synchronization mechanism used to forcibly restrict access to resources when executing multiple threads, which is used to guarantee the satisfaction of mutex requirements in concurrency control.

Classification of Locks (Oracle)

First, according to the Operation division, can be divided into DML锁 ,DDL锁

Second, according to the size of the lock, can be divided into table-level lock, page-level lock, row-level lock (MySQL)

Three, according to the lock level, can be divided into shared lock, exclusive lock


Four, according to the lock way division, can be divided into 自动锁 ,显示锁

Five, according to the use of classification, can be divided into optimistic lock, pessimistic lock


A DML lock (data locks), which is used to protect the integrity of data, including row-level locks (Row locks (TX lock)), table-level locks (table lock (TM Lock)). DDL locks (Dictionary locks, Data dictionary locks) that protect the structure of database objects such as tables, indexes, and so on. Where Packet exclusive DDL lock (Exclusive DDL Lock), shared DDL Lock (Share DDL Lock), Interruptible parse lock (breakable parse locks)

[Database Transactions and locks] detailed four: The lock mechanism of the database