Latch lock and Tlock (transaction Lock), DML lock rule, and deadlock analysis in MySQL

Latch locks and tlock (transaction locks), DML lock rules, and deadlock analysis in MySQL.

I. The relationship between latch and Tlock

Latch: Designed to protect the correctness of critical resources, such as protecting the memory pages that are being used from being destroyed.

There is no deadlock detection mechanism, a lightweight lock, and a memory page or memory sharing variable when the object is in effect.

Tlock: Transaction Lock, Object is transaction, deadlock detection mechanism.

Within InnoDB, to reduce the probability of a deadlock, latch will not wait for Tlock.

The process by which a thread acquires a row lock:

When you lock a row, you add lath to the page where the row is located, and then add Tlock to the row, and then release the lath of the page after you add Tlock to the row.

This mechanism is mainly to ensure the consistency and integrity of the row data that the thread obtains.

If the row is occupied by another thread, the thread first frees the page latch, waits for the row lock, and then adds latch to the page after the row lock is fetched, to see if the page data has changed and to retrieve the changed row again.

Two. DML plus lock rule

DML-locked statements have, insert/update ... where ...../DELETE From: Where: /select * FROM

Insert does not add locks, but when there is one, unique indexes are checked for uniqueness (plus S-lock, which converts S-lock to X-Lock), which is a row lock.

Update ... where: /delete ... where: /select. Where lock in Share mode/select. Where: For update

(1). The data lookup goes through the index, but the expression is not equal, and the interval lock (GAP) is added.

For example. > 10, the Lock interval is (10,+~)

<10, the lock interval is (-~,10).

(2). The data finds the unique index of the walk, and the expression is an equal sign (=), the row lock is added (record lock but not gap).

(3). Data lookup is not a unique index, and the expression is an equal sign, the interval lock (record lock gap) is added, and the interval locked is (], allowed to update, not allowed to insert.

Emphasis: Interval locks are designed to prevent hallucinations from being read, so only the insert operation is blocked. No impact on updates

Three. Deadlock case analysis.

(1). Insert, inserting the same data concurrently, and a unique key exists in the Insert field.

No locks are added during normal insertion, but unique keys are uniquely checked for the unique Key's field, and the record is shared with the lock (S) during the check.

When multiple threads add a shared lock (s) to the same row, the shared lock (s) is converted to an exclusive (X) lock, which in turn waits for a deadlock.

(2). Update concurrent update, updated fields are indexed

Line Cheng updates a row based on the index field, and the line Cheng updates the indexed field based on the primary key.

The line Cheng obtains the index wait line, and the line Cheng obtains the row wait index.

(3) Delete, the record is deleted concurrently according to a unique index field.

The process is very complex, see the link for details:

Http://www.cnblogs.com/sunss/p/3166550.html

(4). Insert and delete concurrency, with nonclustered indexes

Thread 1 deletes data based on a nonclustered index field, and thread 2 inserts the same record.

Thread 1 Gets the index, waits for the line, thread 2 Gets the row, waits for the index.

(5). Insert and update concurrency, with nonclustered indexes.

The deadlock principle is the same as 4.

(6) Delete and update concurrency.

Principle of the same 2.

Deadlock Case Connection:

InnoDB Next-key Lock-triggered deadlock:

Http://www.cnblogs.com/xhan/p/3701459.html

Deadlock caused by unique key:

Http://www.cnblogs.com/sunss/p/3166550.html

Delete deadlock in MySQL

http://hedengcheng.com/?p=844

http://dwchaoyue.blog.51cto.com/2826417/1749109

Latch lock and Tlock (transaction Lock), DML lock rule, and deadlock analysis in MySQL