Talk about MySQL deadlock detection and processing source analysis of two deadlock

This article is mainly through an experiment to describe, the process is more boring.

Experiment Preparation

CREATE TABLE Test_lock (ID int auto_increment PRIMARY key, stock int) Engine=innodb;

Insert into Test_lock (id,stock) value (1,50);

Here I make the stack information as simple as possible, the names of the 25 main functions and the entry parameters

In order to highlight the topic, I add this to the opening deadlock detection function list, altogether 10 functions

Deadlock detection Function List A row_search_for_mysql (search line)

Deadlock detection Function List B sel_set_rec_lock (determines whether it is a level two index or a primary key, and then the row locking)

Deadlock detection Function list C lock_clust_rec_read_check_and_lock (check for lock blockage)

Deadlock detection Function List D lock_rec_lock processing logic (Detection lock compatibility)

Deadlock detection function List e lock_rec_lock_slow processing logic (confirm request not to lock)

Deadlock detection function List F lock_rec_enqueue_waiting

Deadlock detection Function List g lock_deadlock_check_and_resolve processing logic

Deadlock detection Function List h lock_deadlock_search function code processing logic

List of deadlock detection functions I lock_get_first_lock processing logic (first lock comparison)

Deadlock detection Function List J Lock_get_next_lock function code processing logic (Get lock continue comparison)

Let's take a look at the main roles of these functions

Deadlock detection Function List a row_search_for_mysql processing logic

Role: Searching the database for rows

The parameters passed in are:

1.buf cache, for fetching MySQL-formatted lines

How to search 2.mode

Information about the structure of the 3.prebuilt pre-construction table

4. Direction of search

return Result:

@return db_success, Db_record_not_found, Db_end_of_index, Db_deadlock,

Db_lock_table_full, db_corruption, or Db_too_big_record */

After the row_search_for_mysql has searched for rows, the rows are locked, which is called the function List B sel_set_rec_lock processing logic

Deadlock detection function List B sel_set_rec_lock processing logic

Function: Lock a record and return the result as (db_success, Db_success_locked_rec, or error code)

Parameter lists and notes

1.btr_pcur_get_block (pcur), record the corresponding page's pointer address

2.rec:rec = Btr_pcur_get_rec (pcur) for the pointer address of the line

3.index:prebuilt->index, Index of table

4.offsets:offsets is an array with default of 100 elements, offsets = Rec_get_offsets (Next_rec, Index, offsets,ulint_undefined, &heap);

5.prebuilt->select_lock_type Type of table lock (lock_none,lock_s, or lock_x)

6.lock_type: This refers to row-level locks (lock_rec_not_gap,lock_gap,lock_ordinary (Next-key Lock))

7.THR: Query graph query Thread node Assignment thr = Que_fork_get_first_thr (prebuilt->sel_graph);

Deadlock detection Function list C Lock_clust_rec_read_check_and_lock processing logic

Role:

Checks whether locks on other transactions prevent immediate read or pass reading of the clustered index record by reading the cursor.

This function is primarily to check if the lock on another transaction is blocking the current read, or to read the primary key index record by passing a read cursor.

If they do this, first Test

If the query thread should be paused for some reason;

If not, place the transaction and query threads in a lock-wait state and insert a record lock request waiting on the lock queue.

Set the requested mode lock on the record

Deadlock detection Function List D function lock_rec_lock processing logic

Role:

Attempt to lock the specified row, or enter the processing logic inside the Lock_rec_lock_slow function if it cannot be locked

Deadlock detection Function List e function Lock_rec_lock_slow processing logic

Role: The locks requested by the firm are in conflict with other transactions (others already hold them) and go into the lock_rec_enqueue_waiting for processing

Deadlock detection Function list f function lock_rec_enqueue_waiting processing logic

Function: Enter the deadlock detection function lock_deadlock_check_and_resolve

Deadlock detection Function List g function lock_deadlock_check_and_resolve processing logic

Function: Constructs the information of the lock competition, then brings the information of the lock competition into the Lock_deadlock_search function

Deadlock detection Function List h function lock_deadlock_search function code processing logic

Role: Through the lock competition information, compare two transactions for the record of the Heap_no, see two of the firm holds the lock is required for the other, into the lock_get_first_lock to traverse

Deadlock detection Function List I function lock_get_first_lock processing logic

Function: Compares the first lock of a transaction that participates in a lock competition with another waiting lock, and returns the lock information if an equivalent lock is detected.

Return value: Returns the first lock or null

Deadlock detection Function List J Lock_get_next_lock function code processing logic

Function: Returns a value that returns the next lock held by the transaction lock or NULL

Talk about MySQL deadlock detection and processing source analysis of two deadlock