Mysql read/write locks and deadlocks

In MySQL, table locks are not deadlocked when table-level locking is used for storage engines. This means that all necessary locks are requested immediately at the beginning of a query and the tables are always locked in the same order.

In MySQL, table locks are not deadlocked when table-level locking is used for storage engines. This means that all necessary locks are requested immediately at the beginning of a query and the tables are always locked in the same order.

The table locking method for WRITE and MySQL is as follows:

◆ If there is no lock on the table, put a write lock on it.
◆ Otherwise, put the lock request in the write lock queue.

The locking method for READ and MySQL is as follows:

◆ If there is no write lock on the table, place a read lock on it.
◆ Otherwise, put the lock request in the read lock queue.

When a lock is released, the lock can be obtained by the threads in the write lock queue, followed by the threads in the read lock queue.

This means that if you have many updates on a table, the SELECT statement will wait until there are no more updates.

If the INSERT statement does not conflict with each other, you can use a hybrid parallel INSERT and SELECT statement for the MyISAM table without locking.

InnoDB uses row locking and BDB uses page locking. Both storage engines may have deadlocks. This is because InnoDB automatically acquires row locks during SQL statement processing, while BDB acquires page locks instead of at transaction startup.

Advantages of Row-level locking:

· There are only a few lock conflicts when different rows are accessed in many threads.
· Only a small number of changes are allowed during rollback.
· A single row can be locked for a long time.

Disadvantages of Row-level locking:

· More memory is occupied than page-level or table-level locking.
· When used in most tables, it is slower than page-level or table-level locking because you must obtain more locks.
· If you often perform group by operations on most data or scan the entire table frequently, it is much slower than other locks.
· With high-level locking, you can easily adjust applications by supporting different types of locking because the lock cost is lower than Row-level locking.
The following describes several configuration options that are easy to ignore in MyISAM:

Concurrent_insert:

In general, read/write operations in MyISAM are serial, but when querying and inserting the same table, to reduce the frequency of lock competition, according to the settings of concurrent_insert, myISAM can process queries and inserts in parallel:

When concurrent_insert = 0, concurrent insertion is not allowed.

When concurrent_insert = 1, concurrent insertion is allowed for tables without holes. The new data is at the end of the data file (default ).

When concurrent_insert = 2, concurrent insertion at the end of the data file is allowed regardless of whether the table has holes.

In this case, it is very cost-effective to set concurrent_insert to 2. As for the resulting file fragments, You can regularly use the optimize table syntax to OPTIMIZE them.

Max_write_lock_count:

By default, the write operation has a higher priority than the read operation. Even if the read request is sent first and then the write request is sent, the write request is processed first, then process the Read Request. This causes a problem: Once I send several write requests, all read requests will be blocked until all write requests are processed. Max_write_lock_count:

Max_write_lock_count = 1

With this setting, when the system processes a write operation, the write operation will be suspended to give the read operation a chance.

Low-priority-updates:

We can also simply reduce the write operation Priority and give the read operation a higher priority.

Low-priority-updates = 1

In summary, concurrent_insert = 2 is absolutely recommended. As for max_write_lock_count = 1 and low-priority-updates = 1, it depends on the situation. If you can reduce the priority of write operations, use low-priority-updates = 1; otherwise, use max_write_lock_count = 1.

The table is locked in the following situations:

· A customer sends a long-running query.
· Then, another customer updates the same table. The customer must wait until the SELECT operation is complete.
· Another customer sends another SELECT statement to the same table. Because UPDATE has a higher priority than SELECT, the SELECT statement waits for UPDATE to complete and waits for 1st SELECT statements to complete.

The following describes some methods to avoid or reduce the competition caused by table locking:

· Try to make the SELECT statement run faster. You may have to create some summary tables to do this.
· Start d with -- low-priority-updates. This gives all statements that update (modify) a table with a lower priority than the SELECT statement. In this case, the 2nd SELECT statements in the previous case will be executed before the UPDATE statement, without waiting for 1st SELECT statements to complete.
· You can use the SET_UPDATES = 1 Statement to specify that all updates in a specific connection should have a low priority.
· The LOW_PRIORITY attribute can be used to give a specific INSERT, UPDATE, or DELETE statement with a lower priority.
· The HIGH_PRIORITY attribute can be used to give a specific SELECT statement with a higher priority.
· Specify a low value for the max_write_lock_count system variable to start mysqld to force MySQL to temporarily increase the priority of all SELECT statements waiting for a table after a specific number of inserts are completed. In this way, the READ lock is allowed after a certain number of WRITE locks.
· If you have questions about INSERT and SELECT, switch to the new MyISAM table because they support concurrent SELECT and INSERT.
· If you INSERT or delete a table in a hybrid manner, insert delayed will be of great help.
· If you have a problem using the SELECT and DELETE statements in combination with the same table, the delete limit option can be helpful.
· SQL _BUFFER_RESULT can be used for SELECT statements to shorten the table lock time.
· You can change the lock code in mysys/thr_lock.c to use a single queue. In this case, write locks and read locks have the same priority and will be helpful to some applications.

Here are some techniques related to table locking in MySQL:

· You can perform parallel operations if you do not need to check the selection of many rows in the same table and do not need to perform hybrid updates.
· You can use lock tables to increase the speed, because many updates in a LOCK are much faster than those that are not locked. It is also helpful to split the table content into several tables.