Locking Issues

MySQL manages contention for table contents using locking:

• Internal locking is performed within the MySQL server itself to manage contention for table contents by multiple threads. This type of locking is internal because it is performed entirely by the server and involves no other programs.
• External locking occurs when the server and other programs lock MyISAM table files to coordinate among themselves which program can access the tables at which time.

  18.1. Internal Locking Methods: This section discusses internal locking; that is, locking performed within the MySQL server itself to manage contention for table contents by multiple sessions. This type of locking is internal because it is performed entirely by the server and involves no other programs. External locking occurs when the server and other programs lock MyISAM table files to coordinate among themselves which program can access the tables at which time. MySQL uses row-level locking for InnoDB tables, and table-level locking for MyISAM, MEMORY, and MERGE tables. Which lock type works better for your application depends on the application and its workload, especially whether the data is modified frequently and how many concurrent sessions need to read or write the same tables. Different parts of an application may require different lock types.   Considerations for Table Locking: Table locking in MySQL is deadlock-free for storage engines that use table-level locking. Deadlock avoidance 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.   MySQL grants table write locks as follows:

1. If there are no locks on the table, put a write lock on it.
2. Otherwise, put the lock request in the write lock queue.

  MySQL grants table read locks as follows:

1. If there are no write locks on the table, put a read lock on it.
2. Otherwise, put the lock request in the read lock queue.

  Table updates are given higher priority than table retrievals. Therefore, when a lock is released, the lock is made available to the requests in the write lock queue and then to the requests in the read lock queue. This ensures that updates to a table are not “starved” even if there is heavy SELECT activity for the table. However, if you have many updates for a table, SELECT statements wait until there are no more updates. You can analyze the table lock contention on your system by checking the Table_locks_immediate and Table_locks_waited status variables, which indicate the number of times that requests for table locks could be granted immediately and the number that had to wait, respectively. The MyISAM storage engine supports concurrent inserts to reduce contention between readers and writers for a given table: If a MyISAM table has no free blocks in the middle of the data file, rows are always inserted at the end of the data file. In this case, you can freely mix concurrent INSERT and SELECT statements for a MyISAM table without locks. That is, you can insert rows into a MyISAM table at the same time other clients are reading from it. Holes can result from rows having been deleted from or updated in the middle of the table. If there are holes, concurrent inserts are disabled but are enabled again automatically when all holes have been filled with new data. This behavior is altered by the concurrent_insert system variable. InnoDB uses row locks. Deadlocks are possible for InnoDB because it automatically acquires locks during the processing of SQL statements, not at the start of the transaction.   Considerations for Row Locking:   Advantages of row-level locking:

1. Fewer lock conflicts when different sessions access different rows.
2. Possible to lock a single row for a long time.

  Disadvantages of row-level locking:

1. Requires more memory than table-level locks.
2. Slower than table-level locks when used on a large part of the table because you must acquire many more locks.
3. Slower if you often do GROUP BY operations on a large part of the data or if you must scan the entire table frequently.

  Choosing the Type of Locking: Generally, table locks are superior to row-level locks in the following cases:

1. Most statements for the table are reads.
2. Statements for the table are a mix of reads and writes, where writes are updates or deletes for a single row that can be fetched with one key read:

  mysql> UPDATE tbl_name SET column=value WHERE unique_key_col=key_value; mysql> DELETE FROM tbl_name WHERE unique_key_col=key_value;  

3. SELECT combined with concurrent INSERT statements, and very few UPDATE or DELETE statements

Many scans or GROUP BY operations on the entire table without any writers.   18.2  Table Locking Issues: To achieve a very high lock speed, MySQL uses table locking for all storage engines except InnoDB and NDBCLUSTER. For InnoDB tables, MySQL uses table locking only if you explicitly lock the table with LOCK TABLES. For this storage engine, avoid using LOCK TABLES at all, because InnoDB uses automatic row-level locking to ensure transaction isolation. For large tables, table locking is often better than row locking, but there are some disadvantages:

• Table locking enables many sessions to read from a table at the same time, but if a session wants to write to a table, it must first get exclusive access. During the update, all other sessions that want to access this particular table must wait until the update is done.
• A session issues a SELECT that takes a long time to run. Another session then issues an UPDATE on the same table. This session waits until the SELECT is finished.

The following items describe some ways to avoid or reduce contention caused by table locking:

• Try to get the SELECT statements to run faster so that they lock tables for a shorter time.
• To give a specific INSERT, UPDATE, or DELETE statement lower priority, use the LOW_PRIORITY
• To give a specific SELECT statement higher priority, use the HIGH_PRIORITY
• Start mysqld with --low-priority-updates. For storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE), this gives all statements that update (modify) a table lower priority than SELECT
• Using SQL_BUFFER_RESULT with SELECT statements can help to make the duration of table locks shorter.

  18.3  Concurrent Inserts: The MyISAM storage engine supports concurrent inserts to reduce contention between readers and writers for a given table: If a MyISAM table has no holes in the data file (deleted rows in the middle), an INSERT statement can be executed to add rows to the end of the table at the same time that SELECT statements are reading rows from the table. If there are multiple INSERT statements, they are queued and performed in sequence, concurrently with the SELECT statements. The results of a concurrent INSERT may not be visible immediately. The concurrent_insert system variable can be set to modify the concurrent-insert processing. By default, the variable is set to 1 and concurrent inserts are handled as just described. If concurrent_insert is set to 0, concurrent inserts are disabled. If the variable is set to 2, concurrent inserts at the end of the table are permitted even for tables that have deleted rows.   18.4  External Locking: External locking is used in situations where a single process such as the MySQL server cannot be assumed to be the only process that requires access to tables. Here are some examples:

• If you run multiple servers that use the same database directory (not recommended), each server must have external locking enabled.
• If you use myisamchk to perform table maintenance operations on MyISAM tables, you must either ensure that the server is not running, or that the server has external locking enabled so that it locks table files as necessary to coordinate with myisamchk for access to the tables. The same is true for use of myisampack to pack MyISAM If the server is run with external locking enabled, you can use myisamchk at any time for read operations such a checking tables. In this case, if the server tries to update a table that myisamchk is using, the server will wait for myisamchk to finish before it continues.

With external locking in effect, each process that requires access to a table acquires a file system lock for the table files before proceeding to access the table. If all necessary locks cannot be acquired, the process is blocked from accessing the table until the locks can be obtained. External locking affects server performance because the server must sometimes wait for other processes before it can access tables. For mysqld, external locking is controlled by the value of the skip_external_locking system variable. When this variable is enabled, external locking is disabled, and vice versa. Use of external locking can be controlled at server startup by using the --external-locking or --skip-external-locking option. Check Out Our Related Courses Sql Tutorials Oracle Pl SQL Tutorials Top 50 Sql Server Interview Questions SQL Server T-SQL Training In Chicago