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

Editor to share with you what is the difference between mysql table locks and row locks, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

1. Watch lock

Features: inclined to MyISAM storage engine, low overhead, fast locking; no deadlock; large locking granularity, the highest probability of lock conflict and the lowest degree of concurrency.

When we edit the table, or execute the statement to modify the table, we usually add a table lock to the table, which can avoid some out-of-sync things. There are two kinds of table locks, one is read lock and the other is write lock.

We can manually add these two locks to the table with the following statement:

Lock table table name read (write)

Release locks for all tables:

Unlock tables

View locked tables:

Show open tables

Add read lock (shared lock):

What will be the effect of adding a read lock to the watch?

1. Our read-locked process can read read-locked tables, but not other tables.

2. The read-locked process cannot update read-locked tables.

3. Other processes can read read-locked tables (because it is a shared lock), or read other tables

4. The read-locked tables of other processes update will wait for the lock until the lock is released before update succeeds.

Add write lock (exclusive lock):

1. The locking process can do any operation (CURD) on the locked table.

2. Other processes cannot query locked tables and need to wait for the lock to be released

Summary:

The read lock blocks writing, but not reading. On the other hand, write locks block both reading and writing. (pay special attention to the process)

Analysis:

Show status like 'table%'

By entering the above command, you can:

+-- +-+ | Variable_name | Value | +-+-+ | Table_locks_immediate | 105 | | Table_locks_waited | 3 | + -+-+

Table_locks_immediate: the number of times a table-level lock is generated, indicating the number of queries in which the lock can be acquired immediately, plus 1 for each immediately acquired lock value.

Table_locks_waited: the number of times you wait for a table-level lock contention (the number of times the lock cannot be acquired immediately, plus 1 for each wait). A high value indicates that there is a serious table-level lock contention.

Second, row lock

Features: inclined to InnoDB storage engine, high overhead and slow locking; deadlock will occur; locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.

Row locks support transactions, so the knowledge about transactions will be summarized in the next blog.

Behavior:

1. When we update a row but do not commit it, other processes also need to wait for updates to the row, which is called a row lock.

2. If we update one row, other processes updating other rows will not be affected.

Upgrade row lock to table lock:

When our row lock involves index failure, it touches the behavior of the publication lock.

Normally, each locks its own lines and does not affect each other, one 2000 and the other 3000

Because the index is built on the column field b, if it is not used properly, it will cause the row lock to change the table lock

For example, no single quotation marks lead to index invalidation, row lock and table lock.

Be blocked and wait. The block is released only after the Session_1 is submitted, and the update is completed.

Therefore, we still have to make good use of index queries.

Gap lock:

When we use range conditions instead of equality conditions to retrieve data and request shared or exclusive locks, InnoDB will lock the index entries of existing data records that meet the conditions; for records whose key values are within the range of conditions but do not exist, it is called "GAP", and InnoDB will also lock this "gap", which is the so-called Next-Key lock.

Because if Query passes through a range lookup during execution, he will lock all index key values in the entire 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 any data in the range of lock key values cannot be inserted when locked. In some scenarios, this can cause great harm to performance.

Optimization recommendations:

Let all data retrieval be done through indexing as much as possible to avoid upgrading from indexed row locks to table locks.

Reasonably design the index to minimize the scope of the lock.

Search conditions as few as possible to avoid gap locks

Try to control the transaction size and reduce the amount of locked resources and time length.

The lowest possible level of transaction isolation

This is all about the difference between mysql table locks and row locks. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.