MySQL can manage the contention for table contents by using Locking :
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:
MySQL grants the table READ locks are given below:
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:
Row level locking disadvantages are given below:
Table locks are more useful rather than page or row level locks in the following cases:
In large tables table locks is much useful than row locks but there are some pitfalls:
Disadvantages of Table locking is given below:
In the following section we are describing you some ways for avoiding the contention caused by table 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.