This is the third day of my participation in the August More text Challenge. For details, see:August is more challenging
MySql Repeatable Read Isolation Level MVCC snapshot read problem
- Locking is the best way to solve illusion reading
MySQL lock classification![](//cloud.mo4tech.com/images/ldWYtlmLrJXYtJXZ0F2dtA3YmBnYmFTdzsWL2xGc05HOzQ2NkJTZ4ImNjhjY3ITOiRjM0UDOlJDZ2IWYzQGZl9CcjZGciZWM1NzatkWLuNWLz9Gdv02bj5yZtlWZ0lnYu4WaqVWdq1yMw9yL6MHc0RHa/bced640c34fb8bb26b2c50c7de32e999.image)
Read and write locks and intention locks
Read lock
Shared lock (S lock)
As the name implies, multiple processes holding a shared lock can enter the protected space at the same time. Because the locked resources can be shared, the lock is usually applied before the data is read. In this way, multiple data reading processes can execute concurrently without blocking, so it is often called a read lock. InnoDB uses the MVCC mechanism to avoid read/write collisions without locking. At the repeatable read level, ordinary reads are unlocked. But the select… Lock in Share mode adds a shared lock to a row.
Exclusive lock (X lock)
Unlike a shared lock, an exclusive lock blocks any other lock request. An exclusive lock is usually locked before data is written, so that each write operation can remain mutually exclusive. Therefore, it is also called a “write lock”.
Intent locks
Only read/write locking can be implemented during the read/write process. Consider a scenario where a transaction passes a select… Lock in share mode: lock in share mode: lock in share mode: lock in share mode: lock in share mode: lock in share mode: lock in share mode: lock in share mode: lock in share mode: lock in share mode However, if another transaction is to add an exclusive lock to the entire table, then it is inefficient to iterate through all the records in the table and check the lock status of each record to determine whether the lock can be successfully implemented.
The solution is simple: when we need to lock a row, we will mark the entire table as “some rows are already locked”, so that another transaction will lock the entire table without traversing every row.
All shared locks must be added to the table before they are added to the table. All exclusive locks must be added to the table before they are added to the table. The exclusive locks are not mutually exclusive.
Timing of locking for intent locks and exclusive locks
- Add an IS lock before an S lock
- IX lock before X lock
Global lock
How do I perform a full library backup
When we want to backup A database, the banking system as A backup account after we finish, at that moment, from A account to transfer money to B account just happened, if at this time we just backup account, B will find more B account A sum of money, must be in the backup, the account can no longer write, static backup is the safest.
The solution
- Transaction – establishment – consistency view mysqldump – single -transaction
- Globally lock database instances
- Set global read,
set global readonly=true
Scheme selection
- MyISAM does not support transactions
- Once the global read-only setting takes effect permanently, security risks may arise if you forget to restore the configuration
- The readonly configuration is used to determine the current primary or secondary database. Changing the configuration can have unexpected consequences
Global lock lock & unlock
Flush tables with read lock unlock tables // Unlock tablesCopy the code
If another session locks a table, another session’s request for a global lock will be blocked until the lock is released. If the current session locks a table or is in a transaction, the request for a global lock will fail.
Once the global lock is successful, all tables that are currently open will be closed. After that, the instance will become read-only and all update, INSERT, DELETE, exclusive lock, and table structure modification operations on the database will be blocked.
When the link breaks, the global lock is released automatically.
Table level lock
classification
- Table locks
- Meta Data Lock (MDL)
Table locks
Lock tables <tablename> read -- table level shared lock lock tables <tablename> write -- add table level exclusive lock UNLOCK tables <tablename> -- Unlock tablesCopy the code
MDL lock
To ensure the consistency of DDL statements and add, delete, modify, and check statements, the DDL statements do not need to be displayed and used. The execution of any statement is mutually exclusive with the MDL exclusive lock. Therefore, the DDL operation may take a long time to lock.
The table structure modification process is optimized in MySQL 5.6
- Obtain an MDL exclusive lock — prevents other statements from being executed
- Degraded to MDL shared lock – prevents data from being read after the lock (” degraded “includes shared lock and exclusive lock)
- DDL
- Acquire an MDL exclusive lock – The final completion phase needs to ensure that no statements are executing
- Release the MDL shared lock
- Releases the MDL exclusive lock
Problems with MDL locks
- A statement is executing (e.g., slow query, long transaction)
- DDL statement plus MDL exclusive lock blocking and so on.
- After that, all add, delete, change and check, transaction opening operation will be blocked.
The solution
Increase the supermarket parameters or simply make it non-blocking:
ALTER TABLE table_name NOWAIT add column ... ALTER TABLE table_name WAIT N add column.... ALTER TABLE table_name WAIT N add column.... -- Maximum timeout of N secondsCopy the code
Row-level locks
The Innodb engine implements row-level locking, which can reduce lock conflicts compared to MyISAM, which only supports table-level locking.
- Innodb row-level locking is automatic and can be triggered by certain SQL statements, but cannot be unlocked freely.
- If certain rows or intervals are locked during a transaction, the locks are automatically unlocked only at the end of the transaction.
- Innodb uses MVCC to implement snapshot reads without locking at the repeatable read transaction isolation level. All row-level locks do not affect snapshot reads of other transactions
Row level lock classification
- Record lock – – Locks a row
- Clearance lock – – Locks an interval
- Critical lock – – Lock a section of the left open and right closed
Locking scenario
select ... lock in share mode
A Shared lockselect ... for update
Exclusive lockinsert
Exclusive lockupdate
Exclusive lockdelete
Exclusive lock
Record locks
A record lock is applied to a written line to prevent the new line from being modified or deleted by other operations. InnoDB can also lock records that do not exist. The storage engine first creates a hidden aggregate and records it as locked.
Select * from test where dix_field = 2 select * from test where dix_field = 2Copy the code
Gap lock
The record lock locks several records, and the gap lock rule locks the gap between several indexes. Gap locking exists to prevent another transaction from inserting into the gap during transaction execution, so as to avoid magic reads. InnoDB automatically disables gap locking at read committed and uncommitted isolation levels.
Gap locks are not mutually exclusive
Multiple transactions can be locked at the same time on the same gap, the time added is exclusive lock. Consider another common case in which transaction 1 holds a gap lock (1, 3) and transaction 2 holds a gap lock (3, 5). If record 3 is deleted, then the gap locks held by transaction 1 and transaction 2 will both become (1, 5). If the gap locks are mutually exclusive, then an error will occur in this case
Key in the lock
Special clearance lock, his interval is open in front and close in back
Locking scenario
- A range query on a primary key or a unique key increases the number of adjacent key locks in the smallest range before and after the query range
If id >= 2 and id <= 4, add (1, 4] 2). If id <= 4, add (1, 4] 2). Queries with non-primary or unique keys lock the corresponding index record and the gap before it 3. If no index is created, the entire table is actually scanned during the query, so the entire table will eventually be locked. However, for statements such as SELECT * from test where XXX limit 1, the actual scan will end when the first matching row is encountered, so all gaps will be locked
A deadlock
scenario
- Update test set k = k + 1 where id = 1; The record whose ID is 1 will be locked
- Update test set k = k + 2 where id = 2; The record with ID 2 will be locked
- Update test set k = k + 3 where id = 2; Update test set k = k + 4 where id = 1; A deadlock occurs
The solution
- Set timeout to innodb_lock_WAIT_TIMEOUT. The default value is 50, which means that a lock request will automatically return failure after waiting 50 seconds
Existing problems:
- Only second granularity is supported
- Setting too small easy friendly fire
Active deadlock detection
If innodb_deadlock_detect is set to on, Innodb will automatically scan to see if the statement causes a deadlock. If it does, it will return an error immediately
Existing problems:
- If 1000 concurrent threads are updating the same row at the same time, active deadlock detection will require 1 million comparisons, and the CPU will be extremely high
Reduce the concurrency of a single record
In a high concurrency scenario, splitting fields reduces the concurrency of a single record. For example, in the page UV record scenario, the UV of a page is split into multiple rows. Each time the UV is added, one row is randomly selected for execution
- Existing problems
Applies only to monotonically increasing scenarios