MySQL Lock series 2 table lock

The previous article introduces the lock that protects memory structure or variable in MySQL source code, and here we begin to introduce the table lock in MySQL transaction.

Note 1: On the implementation of the table lock, the structure of "mutex+condition+queue" is used to implement the table lock function of concurrency, blocking and waking.

Note 2: Some of the experiments carried out in this article, important Configuration items:

1. autocommit=02.　　Tx_isolation=read-commited3. Engine=InnoDB

1. mysql Lock order:

This includes a SQL execution process, including commit, adding and releasing the locks associated with the transaction, plus the lock order of different locks, which focuses on MySQL's table lock first.

2. mysql table lock

Note: By testing, the table lock of MySQL is implemented in the code that falls into the INNODB layer.

2.1 Important Data structures

struct ST_THR_LOCK_INFO

struct St_thr_lock_data

struct St_thr_lock

With these three main structs, the table lock is implemented, and the relationships between the three are:

Description

1. Each table, when opened, creates a Innobase_share object and initializes a St_thr_lock structure to associate, all the table locks that request this table, need to associate this object

2. Every SQL request, in the process of open table, creates a Table,handler object, which handler initializes a st_thr_lock_data, associated st_thr_lock structure.

2.2 Testing

1. Test Cases:

Session1:session2:

Lock table pp write; select * from PP;

2. Test the main steps:

1. Open Table

2. Lock table

3. Table lock-related case scenario:

1. Add lock

2. Blocking

3. Wake up

2.2.1 Open Table

The details of open table can also be consulted: Open table

Here we mainly introduce the main data structures created by the INNODB layer at Open table:

1. Innobase_share

The INNODB layer represents the structure of a table, including the initialization of a thr_lock, the association structure of all request table locks

2. ib_table

The INNODB layer represents the statistics for a table

3. Handler

The structure provided to the Sever layer table, all operations on the INNODB layer through handler, and initializes a st_thr_lock_data

　　

Main function Call stack:

Open_table

Open_table_from_share

Ha_innobase::open:get_share/thr_lock_init/thr_lock_data_init

1. When you open this table for the first time, create a innobase_share, initialize the Thd_lock, initialize the ib_table

2. Initialize the handler, initialize the Thd_lock_data.

Description

1. All innobase_share structures are saved to a global hash table: Innobase_open_tables, shared globally

2. After the open is finished, create all the associated data structure correlation diagrams as follows:

　　　　

Explanation: The red part is the key structure of the table lock, the mutex is used to protect the Thr_lock queue structure, all locks, wait locks, the line layer structure all need to enter the queue in the Thr_lock, a total of four queue:read,read_wait, write , write_wait.

Each handler is associated with a condition of thread, and all wait,wakeup are done using this condition, which enables directed wakeup and avoids broadcasts.

2.2.2 Lock Table

Session1: Requested Lock_type = Tl_write

Session2: Requested Lock_type = Tl_read

There are 14 kinds of thr_lock_type, namely:

Tl_ignore=-1, Tl_unlock,            tl_read_default,tl_read,            tl_read_with_shared_locks,tl_ Read_high_priority,tl_read_no_insert,tl_write_allow_write,tl_write_concurrent_insert,tl_write_delayed,tl_write _default,tl_write_low_priority,tl_write,tl_write_only

Main function Call stack:

Mysql_lock_tables: Put all table thr_lock_data into the mysql_lock structure.
Get_lock_data
Ha_innobase::store_lock: Clean off the previous lock. Replace with the lock type you are currently requesting.

Thr_multi_lock: Request Lock table

Thr_lock: Single thr_lock_data lock table

Here are the key three steps: Lock table, block, wake

Lock table: Session 1

Session1 uses lock write to lock, this Riga is an exclusive lock, there is no other lock on the thr_lock structure, this will add success,

The main code:

       Mysql_mutex_lock (&lock, mutex); The mutex that locks the table, starts the serial operation to enter this table lock.       Lock_type = Tl_write       (*lock-write.last)=data;                  /*  */       data-prev=Lock-  Write.last;       Statistic_increment (Locks_immediate,&Thr_lock_lock); Cumulative Locks_immediate Count

　　Blocking: Session 2

Session 2 applies the Read table lock of the PP table, but Session1 has acquired an exclusive lock, which will block here and wait for the condition of this thread.

Wait_for_lock (Wait_queue, data,0, Lock_wait_timeout) statistic_increment (locks_waited,&Thr_lock_lock); Cumulative locks_waited count (*Wait -Last=Data/*Wait for Lock* * Join the wait queueData -Prev=Wait -Last ; Wait -Last= &Data -Next; Mysql_cond_timedwait (COND,&Data -Lock -Mutex&Wait_timeout); Waiting for this thread to condition

Here the condition to note, is the condition in the THD structure, the thread blocking, regardless of what reason, only need a condition to complete, there is no need for different lock waits, create different condition.

Wakeup: Session 1 unlock.

Session1: Use unlock tables operation.

　　　　/*  /thr_unlock:wake_up_waiters (lock), wake up waiting for the same lock thread, here you need to determine the compatibility mode of the lock request,　　　　　　And since the request wait queue was saved with a queue, it prevented starvation.  mysql_cond_signal (cond);

Attention:

For InnoDB, regardless of the setting of the autocommit, each DML select ends with the use of Thr_unlock to release the table lock, which is understood as follows: InnoDB tend to use row-level locks to support transactions, for the Protection of Table metadata information, The MDL is used to protect it, so InnoDB is not willing to use it for table locks.

Something:

For the above test, you can try, first operation Session2. After Operation Session1. The result is the same, it's all blocked, but there's a whole different kind of blockage in the lock.

Briefly test:

Session 2:select * from PP:

Open

Gets the MDL lock,

Gets the table lock,

End of execution

Release the table lock: (because autocommit=0, this does not release the MDL Lock)

Session 1:lock Table PP Write

Open

Get MDL Exclusive Lock: (blocking: Because Session2 does not release the MDL lock, it is blocked here)

As you can see, this is because the DDL needs to get the MDL exclusive lock and block.

Next blog, we will take a look at the MDL lock situation.