All locking in MySQL is deadlock free. This is managed by always requesting all needed locks at once at query start and always locking the tables in the same order.
The locking method MySQL uses for WRITE lock works as follows:
If there is no locks on the table, put a write lock on it, else put the lock in the write lock queue.
The locking method MySQL uses for READ locks works as follows:
If there is no write locks on the table, put a read lock on it else put the lock in the read lock queue.
When a lock is released first use give the lock to the threads in the write lock queue and after this to the threads in the read lock queue.
This means that if you have many updates on the same table, select statements will be waiting until there is no more updates.
To fix this in the case where you do many inserts and many selects on the same table you could insert rows in another table and once in a while update the other table with all records from the temporary table.
This can be done with the following code:
LOCK TABLES real_table WRITE, insert_table WRITE insert into real_table select * from insert_table delete from insert_table UNLOCK TABLES
One could also change the locking code in mysys/thr_lock.c to use only one queue. In this case write locks would have the same priority that read locks and this could help some applications.
Go to the first, previous, next, last section, table of contents.