Shulou(Shulou.com)06/01 Report--

This article mainly introduces the "MySQL table lock, row lock, exclusive lock and sharing lock how to use" related knowledge, editor through the actual case to show you the operation process, the method of operation is simple and fast, practical, hope that this "MySQL table lock, row lock, exclusive lock and sharing lock how to use" the article can help you solve the problem.

I. the choice of transaction isolation mechanism

If we completely ignore it and use the transaction isolation mechanism of uncommitted reads and allow these threads to operate the database concurrently, there will be some problems, such as dirty reading (reading data that is not commit), non-repeatable reading (two query values are different), phantom reading (two queries with different data volume), and so on. The security of the data is the lowest, the advantage is that the concurrency efficiency is very high, and generally will not be used.

If we serialize (implemented by locks) and sort all transactions through locks, although the security of the data is improved, the efficiency of concurrency is too low and generally will not be used.

So we generally use the two isolation levels of committed read and repeatable read, which balances the security, consistency and concurrency efficiency of data, and is realized by MVCC multi-version concurrency control (MVCC is the principle of committed read and repeatable read, lock is the principle of serialization)

Table-level lock & row-level lock

Table-level lock: lock the entire table. The overhead is small (because you don't have to find the record of a row of the table to lock, you need to modify the table and apply to add the lock directly), the locking is fast, and there is no deadlock; the lock granularity is large, the probability of lock conflict is high, and the concurrency is low.

Row-level lock: locks a row record. High overhead (need to find the corresponding records in the table, there is the process of searching the table and index), slow locking, deadlock will occur; lock granularity is the smallest, the probability of lock conflict is the lowest, and the degree of concurrency is high.

InnoDB storage engine supports transaction processing, tables support row-level locking, and better concurrency

InnoDB row locking is achieved by locking index items, not table row records, which means that InnoDB uses row-level locks only if data is retrieved through index conditions, otherwise InnoDB will use table locks

Because the row lock implementation of InnoDB is a lock added for index fields, not for row records, although different rows of the following table of the InnoDB engine are accessed, lock conflicts will still occur if the same index fields are used as filtering conditions, and can only be carried out serially, not concurrently.

Even if indexes are used in SQL, after MySQL's optimizer, if a full table scan is considered to be more efficient than using indexes, the use of indexes will be abandoned at this time, so it will not

Use row locks instead of table locks. For example, for very small tables, MySQL will not use indexes.

Third, exclusive lock (Exclusive) and shared lock (Shared)

Exclusive lock, also known as X lock, write lock

Shared lock, also known as S lock, read lock

SS is compatible, but read and write (SX, SX) and XX are mutually exclusive.

There are the following relationships between adding X and S locks to a transaction:

A transaction adds an S lock to the data object A, which can read A but not update. During the lock, other transactions can add S lock to A but not X lock.

If a transaction adds an X lock to the data object A, it can read and update A, and no other transactions can lock A during the lock period.

Show Lock: select … Lock in share mode forces the acquisition of shared locks, select... For update acquires exclusive lock

1. Test the compatibility of exclusive locks and shared locks between different transactions

Let's check the SQL and contents of the table first.

View the isolation level:

First open a transaction to add an exclusive lock to the id=7 data

Opening a transaction with another client

We use the service thread of another transaction to add an exclusive lock to id=7 's data, blocking the

We tried to add a shared lock to id=7 's data, but it was blocked.

Conclusion: for data locks between different transactions, only SS locks can coexist, and XX, SX and XS cannot co-exist.

two。 Test row locks are added to index items

In fact, the row lock is added to the index tree.

Use the unindexed field of the table as the filter condition

Transaction 2 now also wants to acquire the exclusive lock of this record, which conceivably fails; now transaction 2 acquires the exclusive lock of chenwei's record to see if it can succeed.

InnoDB supports row locks, and transaction 1 and transaction 2 can successfully acquire locks for different rows when the primary key id is used as the filter condition. However, now we find that we can't get an exclusive lock with name as chenwei. Why? Let's explain:

Row locking in InnoDB is achieved by locking index items, not by locking table row records.

When we use name as a filter condition without using indexes, we naturally do not use row locks, but instead use table locks. This means that InnoDB uses row-level locks only if the data is retrieved through the index, otherwise InnoDB will use table locks!

We index the name field

We find that after indexing name, two transactions can acquire different row locks (for update), which once again proves that InnoDB's row lock is added to the index item.

Because now name takes the index, the id of finding its row record on the secondary index tree through zhangsan is 7, and then go to the primary key index tree to obtain the exclusive lock of the corresponding row record (personal guess is that the corresponding records of both the secondary index tree and the primary key index tree are locked)

IV. Serial isolation level testing

(all transactions use exclusive or shared locks, which do not require manual locking by the user.)

Set serialization isolation level

Two transactions can acquire shared locks at the same time (SS coexistence)

Now let transaction 2 insert data

At this point, because insert needs to add an exclusive lock, but because transaction 1 has added a shared lock to the entire table, transaction 2 can no longer lock the table successfully (SX does not coexist)

Rollback for a moment

Because we have indexed name, the above select is equivalent to adding a row sharing lock to the data in which name is zhangsan

Transaction 2 update

Transaction 2 cannot update because the entire table is already locked by the shared lock of transaction 1.

Transaction 2 looks for zhangsan on the secondary index tree, finds the corresponding primary key value, and then goes to the primary key index tree to find the corresponding record, but finds that this row of records has been locked by the shared lock. Transaction 2 can acquire the shared lock, but not the exclusive lock.

Let's use the primary key index id to see if we can update.

It is still blocked, although the fields behind our where now use id instead of name, but name also finds the corresponding primary key through the secondary index tree, and then looks for the corresponding records on the primary key index tree, while the records on the primary key index tree are locked (personal guess is that the data corresponding to both the secondary index tree and the primary key index tree are locked)

Our update id=8 data, it worked. Because when we select, we only add row locks to the id=7 data, so we can certainly succeed in manipulating the id=8 data.

If there is an index, a row lock is used; without an index, a table lock is used.

Table-level lock or row-level lock talk about the granularity of lock, shared lock and exclusive lock talk about the nature of lock, whether table lock or row lock, there is a distinction between shared lock and exclusive lock.

That's all for "how to use MySQL table locks, row locks, exclusive locks and shared locks". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.