Dirty read, non-repeatable read and phantom read

• Dirty read: the illusion that one transaction has access to uncommitted data from another transaction
• Non-repeatable read: Multiple queries for row records based on the same condition within a transaction return inconsistent results
• Phantom read: multiple queries within the same transaction return different result sets (increased or decreased)

Isolation level

There are four isolation levels in Mysql: Read Uncommit, Read Commit, Repeatable Read (default), and serializable read.

The mysql isolation boundary has two scopes, the current session isolation level and the system isolation level.

Read uncommit

Before a transaction commits, its changes can be seen by other transactions. No operation is locked.

Read Commit

After a transaction commits, its changes are seen by other transactions.

At the RC level, data is read without locking, but data is written, modified, and deleted without locking.

Repeatable read (default)

The data seen during the execution of a transaction is always the same as the data seen when the transaction is started. Of course, at the repeatable read isolation level, uncommitted changes are also invisible to other transactions.

Write with lock read without lock.

With the help of MVCC and 2PL, InnoDB achieves “read without lock, read without collision” concurrency.

Serializable Read

As the name implies, for the same row, “write” will add “write lock”, “read” will add “read lock”. When a read-write lock conflict occurs, the last accessed transaction must wait for the previous transaction to complete before execution can continue.

The lock

Two pieces of lock

The transaction is divided into two phases, the lock phase and the release phase. Lock phase: In this phase, the lock can be performed. If the lock is unsuccessful, the object enters the wait state until the lock is successful. Unlock phase: After a transaction has released a lock, the transaction enters the unlock phase. During this phase, the transaction can only be unlocked but cannot be locked.

S2PL: In a transaction, only commit or rollback is the unlocking phase, and the rest of the time is the locking phase.

If a condition cannot be quickly filtered through the index, the storage engine layer will lock all records and return them to the mysql Server layer for filtering. In practice, MySQL has made some improvements. When the MySQL Server filter criteria are not met, the unlock_row method is called to release the lock of the records that do not meet the criteria (violating the constraints of the two-segment lock protocol).

Lock in share mode,

Different transactions are allowed to share the lock read before, but other transactions are not allowed to modify or join the exclusive lock

Exclusive lock (for Update)

When an exclusive lock is added to a transaction, no other transaction is allowed to add a shared lock or read an exclusive lock, and no other transaction is allowed to modify the locked row.

Row-level locking

InnoDB defaults to row-level locking. InnoDB locks rows by locking index entries, unlike Oracle, which locks rows in data blocks. The nature of InnoDB’s row-locking implementation means that InnoDB uses row-level locks only if data is retrieved by index criteria, otherwise InnoDB uses table locks.

Table locks

The syntax for table locking is lock tables… The read/write.

The Lock Tables syntax, in addition to limiting reads and writes by other threads, also limits what the thread can manipulate next. Lock tables T1 read, T2 write; This statement blocks statements that write to or write to T1 or T2 from other threads. In addition, thread A can only read T1 and read T2 before executing unlock tables. It is not allowed to write to T1, so it is not allowed to access other tables.

Meta Data Locks are table-level locks that do not need to be used explicitly but are automatically added when accessing a table. Prevent table structure changes while reading or writing. Add MDL read lock when adding, deleting, modifying or checking a table; Add an MDL write lock to a table when making structural changes to the table.

Theoretically, DDL changes will lock the table, leaving a large number of threads “Waiting for Meta Data lock”. Mysql 5.6 provides the Online DDL feature to solve this problem.

1. MDL write locks
2. Degraded to an MDL read lock
3. Really do DDL
4. Upgrade to MDL write lock,
5. The release of MDL lock

Next, the Key lock

Next-key locks are a combination of row locks and GAP locks.

A conflict with gap locking is the operation “insert a record into the gap”. There is no conflict between gap locks.

Gap locks are only effective at repeatable read isolation levels.

Global lock

1. MySQL provides a method for adding a global read lock by using Flush tables with read lock (FTWRL). Use this command when you need to make the entire library read-only
2. A typical use scenario for global locking is to do a full database logical backup (InnoDB does not need this. For a non-transactional engine like MyISAM, if there are updates during the backup, only the latest data is always retrieved, which breaks the consistency of the backup. At this point, we need to use the FTWRL command.

Deadlock in Mysql

Deadlock definition: Two or more processes or transactions waiting on each other

There are two ways to resolve deadlocks:

1. One strategy is to simply wait until time out. This timeout can be set with the innodb_lock_WAIT_timeout parameter.
2. Another strategy is to initiate deadlock detection, where a deadlock is found and one of the transactions in the chain is actively rolled back to allow the other transactions to continue. Setting innodb_deadlock_detect to ON indicates that this logic is enabled. Active monitoring of deadlocks consumes CPU resources and can cause CPU spikes if multiple concurrent rows are updated simultaneously. The basic idea is to queue updates to the same row before entering the engine. So InnoDB doesn’t have a lot of deadlock detection inside.

MVCC multi-version concurrency control

define

Multiple versions of the same record can exist in the system

Basic concept

• Snapshot reads: Under MVCC, a typical SELECT is a snapshot read, not the latest value in the database, but a snapshot at the time the transaction is started
• Current read: Processes current data and locks. (select * from table where? Lock in share mode or select * from table where? For update)

MVCC in InnoDB implementation

Each row of Innodb storage engine contains hidden fields: DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR, and DELETE_BIT.

• DB_TRX_ID: specifies the id of the transaction that last modified the row record. Internally, the delete operation is an update operation that updates the delete identifier bit DELELE_BIT in the row.

• DB_ROLL_PTR: Indicates the undo log that points to the current data. This pointer is used to retrieve the data before it was updated.

• DB_ROW_ID: contains a row ID that increases monotonically as new rows are inserted. When innoDB automatically generates a clustered index, the clustered index includes the value of this row ID. Otherwise, this row ID will not appear in any index.

• DELELE_BIT: indicates whether the record is deleted.

One row of data corresponds to multiple rows of such records, for example, if more than one thing updates the same row of data.

Low_limit_id = low_limit_id; low_limit_id = low_limit_id; low_limit_id = low_limit_id The maximum number of transaction ids that have been created in the current system plus 1 is marked as up_limit_id.

Read a row of data that is visible if its latest transaction ID is less than low_limit_ID

If the transaction ID is greater than up_limit_ID, the version is not visible

If the transaction ID falls between low_limit_ID and up_limit_ID if array A contains the transaction ID, the version is not visible; if array A does not contain the transaction ID, the version is visible