concurrency control and transactions in the database

concurrency control and transactions in the database are often asked during an interview, but the basic knowledge of the 2 aspects of the database must also be clear to guide their daily routine. Below I summarize, this 2 days I encountered problems, basically is the carding of the points of knowledge.

concurrency control

Basic Theories and concepts:

When dealing with concurrency or writing, you can solve the problem by implementing a lock system consisting of 2 types of locks, which are commonly referred to as shared and exclusive locks, also called Read and write locks. Read locks are shared, non-blocking, and multiple clients can read the same resource at the same time without interfering with each other. Write locks are exclusive, and write locks block other read and write locks.

The size of the lock classification:

are table locks and row locks, respectively. The table lock overhead is minimal, blocking all read and write operations to the table by other users, and row locks support concurrency to the greatest extent.

Transaction

1. Features

A well-run transactional database that requires acid characteristics:

Atomicity of-atomicity

Conformance-consistency

Isolation-isolation

Persistent-durability

2. Transaction ISOLATION LEVEL

Read uncommited: Uncommitted reads

Read commited: Submission degree

REPEATABLE READ: Repeatable reading

Serializable: Serializable

	Dirty Read possibility	Non-REPEATABLE READ	Phantom reading	Locking Read
Non-committed read	Yes	Yes	Yes	NO
Submit Read	NO	Yes	Yes	No
REPEATABLE READ	No	No	Yes	No
Serializable	No	No	No	Yes

3. Deadlock

In order to solve the deadlock, the database system realizes the deadlock detection and deadlock time-out mechanism; currently INNODB handles deadlocks by rolling back transactions that hold the least row-level exclusive locks.

concurrency control and transactions in the database