Read/write Lock

In MySQL for a storage engine that uses table-level locking, the table locks without deadlock.

This is managed by always requesting all necessary locks at the beginning of a query and always locking the table in the same order.

The table locking method used for Write,mysql is as follows:
If there is no lock on the table, put a write lock on it.
Otherwise, the lock request is placed in the write lock queue.

The locking method used for Read,mysql is as follows:
If there is no write lock on the table, place a read lock on it.
Otherwise, the lock request is placed in the read lock queue.

When a lock is released, the lock can be obtained by the thread in the write lock queue, and then the thread in the lock queue is read.

This means that if you have many updates on a table, the SELECT statement waits until there are no more updates.
If the INSERT statement does not conflict, you can freely mix parallel insert and SELECT statements for the MyISAM table without locking.

InnoDB uses row locking, BDB uses page locking. There may be deadlocks for both of these storage engines. This is because, during the processing of SQL statements, InnoDB automatically obtains row locks, and BDB obtains page locks instead of being obtained when a transaction is started.

Advantages of row-level locking:
· There are only a few locking conflicts when accessing different rows in many threads.
· There are only a few changes when rolling back.
· You can lock a single row for a long time.
Disadvantages of row-level locking:
· Consumes more memory than page-level or table-level locking.
· When used in most of the table, it is slower than page-level or table-level locking because you have to get more locks.
· If you frequently perform GROUP by operations on most data or you must scan the entire table frequently, it is significantly slower than other locks.
· With high-level locking, you can easily adjust your application by supporting different types of locking, because its lock cost is less than row-level locking.

Here are a few easy-to-ignore configuration options:
Concurrent_insert:
Generally speaking, read and write operations are serial in the MyISAM, but when querying and inserting the same table, in order to reduce the frequency of lock contention, MyISAM Based on the settings of Concurrent_insert, MyISAM is capable of processing queries and insertions in parallel:
when concurrent_insert=0, concurrent insertions are not allowed.
When Concurrent_insert=1, allows concurrent insertions to tables with no holes, and new data at the end of the data file (default).
When concurrent_insert=2, it is allowed to insert at the end of the data file, regardless of whether the table has holes.
In this case, setting Concurrent_insert to 2 is a good deal, and as a result of the resulting file fragments, you can use Optimize table syntax optimization regularly.
Max_write_lock_count:
By default, the write operation takes precedence over the read operation, even if the read request is sent, and then the write request is sent, and the write request is processed first, and then the read request is processed. This creates a problem: once I make several write requests, it blocks all of the read requests until the write request is processed and the read request is processed. Consider using Max_write_lock_count at this point:
Max_write_lock_count=1
has such a setting that when the system processes a write operation, it pauses the write operation and gives the opportunity to read the operation.
Low-priority-updates:
We can also simply lower the priority of write operations and give higher priority to read operations.
Low-priority-updates=1
In general, concurrent_insert=2 is definitely recommended, as for Max_write_lock_count=1 and low-priority-updates= 1, depending on the situation, if you can lower the priority of the write operation, use Low-priority-updates=1, otherwise use max_write_lock_count=1.

The

table lock also has problems in the following scenario:
• A customer issues a long-running query.
• Then another customer updates the same table. The customer must wait until the select is complete.
• Another customer issues a second SELECT statement on the same table. Because the update is higher than the select priority, the SELECT statement waits for the update to complete and waits for the 1th select to complete.
The following describes some ways 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 (summary) tables to do this.
• Start mysqld with--low-priority-updates. This gives all of the statements that update (modify) a table a lower priority than the SELECT statement. In this case, the 2nd SELECT statement in the previous case will be executed before the UPDATE statement, without waiting for the 1th select to complete.
• You can use the Set_updates=1 statement to specify that all updates in a specific connection should use a low priority.
• You can use the Low_priority property to give a lower priority to a specific insert, UPDATE, or DELETE statement.
• You can use the High_priority property to give a higher priority to a particular SELECT statement.
• 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 complete. This allows a read lock to be given after a certain number of write locks.
• If you have questions about insert combined with SELECT, switch to using the new MyISAM table because they support concurrent select and insert.
• If you mix inserts and deletes on the same table, insert delayed will be of great help.
• The limit option for DELETE can be helpful if you are having problems mixing SELECT and DELETE statements with the same table.
• Using Sql_buffer_result with the SELECT statement can help 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, the write lock and read lock will have the same priority, and some applications will be helpful.

Here are some tips related to table locking in MySQL:
· If you do not mix updates with a selection that requires you to check many rows in the same table, you can do this in parallel.
· You can use the lock TABLES to increase speed because many updates in one lock are much faster than updates that are not locked. Splitting the contents of a table into several tables can also be useful.

Read/write Lock