Locking Issues

MySQL can manage the contention for table contents by using Locking :

• Internal Locking can be performed in the MySQL server itself for managing the contention for table content by multiple threads. This locking is known as internal because this locking is performed entirely by the server and it does not involve any other programs.
        
• External locking is performed when server and other programs lock the table files for coordinating among themselves which program may access the tables at which time.

Internal Locking Methods

In this section we are describing you about internal locking, Internal Locking is performed in the MySQL server itself for managing the contention for table content by multiple threads. This locking is known as internal because this locking is performed entirely by the server and it does not involve any other programs.

Table – level locking is used by MySQL to MyISAM and MEMORY, row – level locking for InnoDB tables and page – level locking for BDB tables. In some cases you can guess which locking is best for an application but generally is not easy to say that a given lock is better than another lock type. Actually everything is depend on the application because different parts of application can required different lock types. For deciding this, if you are using storage engine with row – level locking then you must look at what application does and what is the combination of update and select statements it uses.

MySQL uses table level locking for storage engines. In MySQL table locking is deadlock free. Deadlock prevention can be managed by requesting all required lock at once at the beginning of query and the lock the tables in the same order.

MySQL grants the table WRITE locks are given below:

• When locks are not available on the table then put a write lock on the table.
• Else in the write lock queue put a lock request.

MySQL grants the table READ locks are given below:

• When write locks are not available on the table then put a read lock on the table.
• Else in the read lock queue put a lock request

After releasing the lock, lock is available for the threads in the write lock queue and then for the threads in read lock queue. For analyzing the contention of table lock we check the Table_locks_immediate and Table_locks_waited status variables :

mysql> show status like 'Table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 12    |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

InnoDB and BDB type tables uses row locks and page locks respectively. All locking are deadlock free except of these two because they acquire the lock between the processing of SQL statements rather than the beginning of transaction.

Row level locking advantages are given below:

• In row level locking we can lock a single row for a long time.
• We can do fewer changes to rollback also.
• And fewer lock conflicts when accessing different rows in many threads.

Row level locking disadvantages are given below:

• This type of locking needs more memory than table or page level locking.
• When we used this type of locking on a large part of the table then its slower than table or page level locking because we have to acquire many more locks.

Table locks are more useful rather than page or row level locks in the following cases:

• When most of the statements for the tables are reads.
• When statements to a table are a combination of reads and writes and writes are deletes or updates to a single row, which can be fetched with one key read.
• When SELECT statement mixed with INSERT and few DELETE or UPDATE statements

In large tables table locks is much useful than row locks but there are some pitfalls:

• If we are using table locking then many threads can read from a table at the same time but if any threads wish to write in a table then it have to get the exclusive access. And during the update if other threads wish to access this table then they have to wait until the update is complete.
• Table updates have the higher priority than the table retrievals.
• Table locks faces some problem in some cases such as when any thread is in waiting condition because the disk is full and its needs some free space then it can be proceed. In this situation, all threads, which want to access the problem table, are also in waiting condition until they get the free space.

Disadvantages of Table locking is given below:

• A client fires a SELECT Statement, which takes a long time to run and at the same time another client fires an UPDATE statement on the same table. Then this client has to wait until the SELECT statement is finished.
• If another client fires another SELECT statement on the same table then this SELECT has to wait until the UPDATE statement is finished because UPDATE has higher priority than SELECT.

In the following section we are describing you some ways for avoiding the contention caused by table locking:

• Start mysql with low priority updates. This statement is used to give, all statement which update the table , lower priority than SELECT statement.
• By using the SET LOW_PRIORITY_UPDATES=1 statement, we can define the all updates that fired in a defined connection can be done with low priority.
• We can give a specific DML Statements (INSERT, DELETE, UPDATE) lower priority with LOW_PRIORITY attribute. And we can also give the higher priority to SELECT statement with the HIGH_PRIORITY attribute.
• If you are facing problem with combination of INSERT and SELECT statement then you can switch to MyISAM tables because that supports the concurrent SELECT and INSERT statements.
• If you are using the combination of INSERT and DELETES statement on the same table then INSERT DELAYED can be helpful to you.
• If you are facing problem with combination of SELECT and DELETE statement then LIMIT option to DELETE can be help to you.
• Using SQL_BUFFER_RESULT with SELECT statements may be helpful to make the shorter duration of table locks

External Locking

External locking is the used of filesystem locking for managing the contention to databases tables by multiple processes. External locking is performed when server and other programs lock the table files for coordinating among themselves which program may access the tables at which time.

This type of locking affects the performance of the server because many times before accessing the tables server must wait for other processes. There is no need of External locking if you are running a single server for accessing a given database directory and other programs are not available like myisamchk require to modify the tables while the server is running. External locking is also not required when you only read the tables with other programs.

For mysqld, the system variable skip_external_locking value is used to control the external locking. If this system variable is enabled then external locking is disabled and vice versa. We can also control the use of external locking by –external-locking or –skip-external-locking option at server startup.