How InnoDB implements serialization isolation level

Serialization implementation

InnoDB implements serialization in two ways.

First, when the SELECT statement is in an explicit transaction block, such as executing case number 1 in Table 11-9, a LOCK_S lock will be applied. According to Table 11-6 (record lock transaction lock compatibility table), the LOCK_S lock excludes the write lock, so only concurrent read operations are allowed under the serialization isolation level, and concurrent writes are prohibited, thus achieving serializability.

The corresponding code is as follows:

ha_innobase::external_lock(...)

{...

 if (lock_type != F_UNLCK) {

 /* MySQL is setting a new table lock */

...

 if (trx->isolation_level == TRX_ISO_SERIALIZABLE //Serialization isolation level && m_prebuilt->select_lock_type == LOCK_NONE

  && thd_test_options(thd, OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)) { // and inside an explicit transaction block /* To get serializable execution, we let InnoDB conceptually add 'LOCK IN SHARE MODE' to all SELECTs

  which otherwise would have been consistent reads. An exception is consistent reads in the AUTOCOMMIT=1 mode:

  we know that they are read-only transactions, and they can be serialized also if performed as consistent reads. */

  m_prebuilt->select_lock_type = LOCK_S; //Add read lock, i.e. 'LOCK IN SHARE MODE'

  m_prebuilt->stored_select_lock_type = LOCK_S;

 } //Otherwise, no lock (this is also very important)

...

 } else {

 TrxInInnoDB::end_stmt(trx);

 DEBUG_SYNC_C("ha_innobase_end_statement");

 }

...}

The second way, when the SELECT statement is not within an explicit transaction block, is to obtain the latest snapshot (at the beginning of the transaction) and then read the data. At this time, because the snapshot-based consistent read does not require locking, its locking situation corresponds to the situation corresponding to number 2 in Table 11-9.

Table 11-9 Serialization isolation level locking

illustrate:

S0: SELECT * FROM bluesea WHERE c1=2; //Use the primary key index as the WHERE condition

In addition, for the FLUSH...WITH READ LOCK statement, a read lock LOCK_S is also required under the serialization isolation level.

The code is as follows:

ha_innobase::store_lock(

...

 /* Check for FLUSH TABLES ... WITH READ LOCK */

 if (trx->isolation_level == TRX_ISO_SERIALIZABLE) {

  m_prebuilt->select_lock_type = LOCK_S;

  m_prebuilt->stored_select_lock_type = LOCK_S;

 } else {

  m_prebuilt->select_lock_type = LOCK_NONE;

  m_prebuilt->stored_select_lock_type = LOCK_NONE;

 } 

...

}

Related to serialization is innobase_query_caching_of_table_permitted() function. The serialization isolation level does not allow cached queries.

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.