Shulou(Shulou.com)05/31 Report--

This article mainly introduces the MySQL lock mechanism example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Lock is a very important part of MySQL, and lock plays an important role in the concurrency of MySQL data access. The lock also involves a lot of knowledge, so if you want to eat it and digest it into your stomach, you need to calm down and savor it over and over again. This article is a general arrangement of the lock, some relevant in-depth details, or need to find relevant books to continue to tamp.

The understanding of Lock

1.1 explanation of the lock

A mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently.

1.2 importance of locks

In the database, in addition to the competition of traditional computing resources (CPU, RAM, I\ O, etc.), data is also a kind of resource for multi-users to share. How to ensure the consistency and effectiveness of concurrent data access is a problem that must be solved in all databases. Lock conflicts are also an important factor affecting the performance of database concurrent access, so locks are especially important to the database.

1.3 shortcomings of locks

Locking consumes resources, and various operations of the lock, including acquiring the lock, checking whether the lock has been released, releasing the lock, and so on, will increase the overhead of the system.

1.4 simple example

Now online shopping has been particularly common, such as Taobao Singles Day event, the flow of people that day is 10 million and 100 million other, but the inventory of merchants is limited. In order to ensure that the merchants' inventory is not oversold, the system will lock and control the inventory of goods. When a user is placing an order for the last item of an item, the system will immediately lock the item to prevent other users from placing repeated orders and will not release it until the payment action is completed (if the payment is successful, the inventory will be sold out immediately. If the payment fails, it will be released immediately.

Type of lock

2.1 Table lock

Kinds

Read locks (read lock), also known as shared locks (shared lock), for the same data, multiple reads can be performed at the same time without affecting each other (select) write locks (write lock), also called exclusive locks (exclusive lock) block other read and write operations (update, insert, delete) before the current operation is completed

Storage engine default lock

MyISAM

Characteristics

1. Lock the whole table by 2. The cost is 3. 5% less. Add the lock quickly 4. No deadlock 5. The lock granularity is large, the probability of lock conflict is high, and the concurrency is low. The read lock blocks the write operation, not the read operation 2. Write locks block read and write operations. It is recommended that MyISAM's read-write lock scheduling is write first, which is why MyISAM is not suitable for writing as the main table engine, because after the write lock, other threads cannot do anything, and a large number of updates make it difficult for the query to get the lock, resulting in permanent blocking.

2.2 Row Lock

Kinds

Read lock (read lock), also known as shared lock (shared lock), allows one transaction to read a row, prevents other transactions from acquiring exclusive lock write locks (write lock) of the same data set, and also called exclusive lock (exclusive lock) allows access to transaction update data of exclusive locks, prevents other transactions from acquiring shared locks of the same data set and exclusive locks intended to share locks (IS) when a transaction adds a shared lock to a data row Must first obtain table IS lock intention exclusive lock (IX) when a transaction adds an exclusive lock to a data row, it must first obtain the table's IX lock storage engine default lock InnoDB feature 1. Lock a row of data 2. The cost is 3. 5%. Lock is slow by 4. There will be a deadlock 5. The lock granularity is small, the probability of lock conflict is the lowest, and the problems caused by concurrency and high transaction concurrency are 1. Update loss resolution: make the transaction a serial operation rather than a concurrent operation, that is, start each transaction-add an exclusive lock to the read record. Dirty reading solution: the isolation level is Read uncommitted3. Unreadable solution: use the Next-Key Lock algorithm to avoid 4. Solution to phantom reading: Gap Lock 2.3.Page lock overhead, lock time and lock granularity are between table lock and row lock, deadlock will occur, and concurrent processing power is general (this lock is not described)

How to lock it?

3.1 Table lock

Implicit lock (default, automatic lock automatic release) select / / read lock insert, update, delete / / write lock explicit lock (manual) lock table tableName read;// read lock lock table tableName write;// write lock unlock (manual) unlock tables;// all lock table session01session02lock table teacher read;// read lock

Select * from teacher;// can read select * from teacher;// can read normally update teacher set name = 3 where id = 2 failure / error cannot be written due to read lock update teacher set name = 3 where id = 2 switch / blocked unlock tables;// unlocked

Update teacher set name = 3 where id = 2ramp / update operation successful session01session02lock table teacher write;// write lock

Select * from teacher;// can read select * from teacher;// is blocked update teacher set name = 3 where id = 2 unlock tables;// / normal update operation update teacher set name = 4 where id = 2 crash / blocked unlock tables;// unlock

Select * from teacher;// read successfully

Update teacher set name = 4 where id = 2ramp / update operation successful

3.2 Row lock

Implicit locking (default, automatic locking and automatic release)

Select / / does not lock insert, update, delete / / write locks explicitly lock (manual) select * from tableName lock in share mode;// read locks select * from tableName for update;// write locks unlock (manual) 1. Commit transaction (commit) 2. Rollback transaction (rollback) 3. Kill blocking process session01session02begin

Select * from teacher where id = 2 lock in share mode;// read lock

Select * from teacher where id = 2ram / can read normally update teacher set name = 3 where id = 2ram / can update operation update teacher set name = 5 where id = 2ram / blocked commit

Update teacher set name = 5 where id = 2ramp / update operation successful session01session02begin

Select * from teacher where id = 2 for update;// write lock

Select * from teacher where id = 2ram / can read normally update teacher set name = 3 where id = 2ram / can update operation update teacher set name = 5 where id = 2ram / blocked rollback

Update teacher set name = 5 where id = 2 beat / update operation succeeds Why is write locked and other transactions can be read? Because InnoDB has a MVCC mechanism (multi-version concurrency control), snapshot reads can be used without blocking.

Implementation algorithm of Row Lock

4.1 Record Lock lock

The lock on a single row record Record Lock will always lock the index record. If no index is set when the InnoDB storage engine table is established, the InnoDB storage engine will use the implicit primary key to lock it.

4.2 Gap Lock lock

When we retrieve data using range conditions instead of equality conditions and request shared or exclusive locks, InnoDB locks the index of eligible existing data records for records whose key values are within the range of conditions but do not exist. Advantage: it solves the problem of phantom reading of transaction concurrency: because if query searches through a range during execution, it will lock all the index key values in the contention range, even if the key value does not exist. Gap lock has a fatal weakness, that is, when a range key value is locked, even some non-existent key values will be innocently locked, so that no data in the lock key range can be inserted when locked. In some scenarios, this can cause great harm to performance.

4.3 Next-key Lock lock

At the same time lock data + gap lock under the Repeatable Read isolation level, Next-key Lock algorithm is the default row record locking algorithm.

4.4 Note of Row Lock

1. InnoDB uses row-level locks only when retrieving data through index conditions, otherwise table-level locks (index invalidation, row locks change table locks) 2. Even if the records of different rows are accessed, if the same index key is used, a lock conflict will occur. If the data table has multiple indexes, you can lock different rows through different indexes

How to troubleshoot locks?

5.1 Table Lock

Check the table lock condition show open tables

Table lock analysis show status like 'table%'

1. The number of times table_locks_waited waits due to table-level lock contention (the number of times the lock cannot be acquired immediately, plus 1 for each wait). This high value indicates that there is a serious table-level lock contention. 2. Table_locks_immediate generates table-level lock times, not the number of queries that can acquire locks immediately. Add 1 for each lock acquired immediately.

5.2 Line Lock

Row lock analysis

Show status like 'innodb_row_lock%'

1. Innodb_row_lock_current_waits / / the number of locks currently waiting for 2. Innodb_row_lock_time / / the total length of time from system startup to the present lock is 3. Innodb_row_lock_time_avg / / the average time spent waiting each time is 4. Innodb_row_lock_time_max / / the time spent waiting for the longest time from system startup to now is 5. Innodb_row_lock_waits / / system startup Total number of waits since then information_ schema library 1. Innodb_lock_waits table 2. Innodb_locks table 3. Innodb_ trx table optimization recommendation 1. Let all data retrieval be done through the index as much as possible to avoid upgrading from indexed row locks to table locks. Reasonable design of the index to minimize the scope of the lock 3. Search conditions as few as possible, avoid gap lock 4. Try to control the transaction size and reduce the amount of locked resources and time length by 5. 5%. The lowest possible level of transaction isolation

Deadlock

6.1 explanation

Refers to the phenomenon that two or more transactions occupy each other on the same resource and request to lock the resources occupied by the other party, thus leading to a vicious circle

6.2 conditions of production

1. Mutually exclusive condition: a resource can only be used by one process at a time. Request and retention conditions: when a process is blocked by a request for resources, it holds on to the resources it has acquired. No deprivation condition: the resources that have been obtained by the process cannot be forcibly deprived of 4. 5% until they are used up. Loop wait condition: a relationship between multiple processes that loop waiting resources for each other.

6.1 Resolution

1. View deadlock: show engine innodb status\ G2. Automatic detection mechanism, timeout automatic rollback of less expensive transactions (innodb_lock_wait_timeout default 50s) 3. Artificial resolution, kill blocking process (show processlist) 4. Wait for graph wait graph (active detection)

6.1 how to avoid

1. The locking order is the same, locking the required rows of data at once as much as possible. Try to update data 3. 0 based on primary (primary key) or unique key. The amount of data in a single operation should not be too much, involving as few tables as possible. Reduce the index on the table and reduce the locking resources 5. 5. Try to use a lower isolation level of 6. Try to use the same conditions to access data, which can avoid the effect of gap lock on concurrency insertion. Carefully design the index, try to use the index to access data 8. With the help of related tools: pt-deadlock-logger

Optimistic lock and pessimistic lock

7.1 pessimistic lock

Interpretation assumes that concurrency conflicts will occur, shielding all operations that may violate data integrity to implement mechanisms such as table locks, row locks and so on. The database itself is suitable for scenarios with large concurrency.

7.2 optimistic lock

Explain the assumption that there will be no concurrency conflicts, only check whether the version number or timestamp meets the implementation level business code concurrency when submitting updates in violation of data integrity. Thank you for reading this article carefully. I hope the article "sample Analysis of MySQL Lock Mechanism" shared by the editor will be helpful to you. At the same time, I hope you will support it. Pay attention to the industry information channel, more related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.