Technical Interview Questions
Oracle Interview Questions
MsSql Interview Questions
My SQL Interview Questions and Answers
How MySQL Locks Tables ?
You can find a discussion about different locking
methods in the appendix.
All locking in MySQL is deadlock-free. This is managed
by always requesting all needed locks at once at the
beginning of a query and always locking the tables in
the same order.
The locking method MySQL uses for WRITE locks works as
If there are no locks on the table, put a write lock on
it. Otherwise, put the lock request in the write lock
queue. The locking method MySQL uses for READ locks
works as follows:
If there are no write locks on the table, put a read
lock on it. Otherwise, put the lock request in the read
lock queue. When a lock is released, the lock is made
available to the threads in the write lock queue, then
to the threads in the read lock queue.
This means that if you have many updates on a table,
SELECT statements will wait until there are no more
To work around this for the case where you want to do
many INSERT and SELECT operations on a table, you can
insert rows in a temporary table and update the real
table with the records from the temporary table once in
This can be done with the following code:
mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> insert into real_table select * from insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;
You can use the LOW_PRIORITY options with INSERT if you
want to prioritize retrieval in some specific cases.
You could also change the locking code in `mysys/thr_lock.c'
to use a single queue. In this case, write locks and
read locks would have the same priority, which might
help some applications.
MySQL - Table Locking Issues
The table locking code in MySQL is deadlock free.
MySQL uses table locking (instead of row locking or
column locking) on all table types, except BDB tables,
to achieve a very high lock speed. For large tables,
table locking is MUCH better than row locking for most
applications, but there are, of course, some pitfalls.
For BDB tables, MySQL only uses table locking if you
explicitely lock the table with LOCK TABLES or execute a
command that will modify every row in the table, like
In MySQL Version 3.23.7 and above, you can insert rows
into MyISAM tables at the same time other threads are
reading from the table. Note that currently this only
works if there are no holes after deleted rows in the
table at the time the insert is made.
Table locking enables many threads to read from a table
at the same time, but if a thread wants to write to a
table, it must first get exclusive access. During the
update, all other threads that want to access this
particular table will wait until the update is ready.
As updates on tables normally are considered to be more
important than SELECT, all statements that update a
table have higher priority than statements that retrieve
information from a table. This should ensure that
updates are not 'starved' because one issues a lot of
heavy queries against a specific table. (You can change
this by using LOW_PRIORITY with the statement that does
the update or HIGH_PRIORITY with the SELECT statement.)
Starting from MySQL Version 3.23.7 one can use the
max_write_lock_count variable to force MySQL to
temporary give all SELECT statements, that wait for a
table, a higher priority after a specific number of
inserts on a table.
Table locking is, however, not very good under the
A client issues a SELECT that takes a long time to run.
Another client then issues an UPDATE on a used table.
This client will wait until the SELECT is finished.
Another client issues another SELECT statement on the
same table. As UPDATE has higher priority than SELECT,
this SELECT will wait for the UPDATE to finish. It will
also wait for the first SELECT to finish!
A thread is waiting for something like full disk, in
which case all threads that wants to access the problem
table will also be put in a waiting state until more
disk space is made available.
Some possible solutions to this problem are:
Try to get the SELECT statements to run faster. You may
have to create some summary tables to do this.
Start mysqld with --low-priority-updates. This will give
all statements that update (modify) a table lower
priority than a SELECT statement. In this case the last
SELECT statement in the previous scenario would execute
before the INSERT statement. You can give a specific
INSERT, UPDATE, or DELETE statement lower priority with
the LOW_PRIORITY attribute.
Start mysqld with a low value for max_write_lock_count
to give READ locks after a certain number of WRITE
You can specify that all updates from a specific thread
should be done with low priority by using the SQL
command: SET SQL_LOW_PRIORITY_UPDATES=1.
You can specify that a specific SELECT is very important
with the HIGH_PRIORITY attribute.
If you have problems with INSERT combined with SELECT,
switch to use the new MyISAM tables as these support
concurrent SELECTs and INSERTs.
If you mainly mix INSERT and SELECT statements, the
DELAYED attribute to INSERT will probably solve your
If you have problems with SELECT and DELETE, the LIMIT
option to DELETE may help.
Page Numbers : 1