MySQL 5.1 supports table-level locking for MyISAM and MEMORY tables, page-level locking for BDB tables, and InnoDB tables. Row level locking. In many cases, it is possible to guess based on training which lock type is best for an application, but it is generally difficult to tell whether a given lock type is better than another. Everything depends on the application, different parts of the application may require different lock types. To determine whether you want to use a row-level locking storage engine, you should look at what your application does and what mix of select and update statements it uses. For example, most web applications perform many selects and few deletes, only updates to key values, and only a few specific table inserts. The basic MySQL MyISAM setup is well tuned.
In MySQL, for storage engines that use table-level locking, there will be no deadlock when the table is locked. This is managed by always requesting all necessary locks immediately at the beginning of a query and always locking the table in the same order.
For WRITE, the table locking method used by MySQL works as follows:
◆ If there is no lock on the table, put a write lock on it.
◆ Otherwise, put the lock request in the write lock queue.
For READ, the locking method used by MySQL works as follows:
◆If there is no write lock 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 can be acquired by threads in the write lock queue, and then by threads in the read lock queue.
This means that if you have many updates on a table, the SELECT statement will wait until there are no more updates.
If INSERT statements do not conflict, you can freely mix parallel INSERT and SELECT statements for a MyISAM table without locking.
InnoDB uses row locking and BDB uses page locking. With both storage engines, deadlocks are possible. This is because InnoDB automatically obtains row locks and BDB obtains page locks during SQL statement processing, rather than when the transaction is started.
Advantages of row-level locking:
· There are only a few locking conflicts when different rows are accessed in many threads.
· Roll back with only a small number of changes.
· A single row can be locked for a long time.
Disadvantages of row-level locking:
· Takes up more memory than page-level or table-level locking.
· When used on large portions of a table, slower than page-level or table-level locking because you must acquire more locks.
· If you frequently perform GROUP BY operations on most of your data or must frequently scan the entire table, it will be significantly slower than other locks.
· With high-level locking, you can also easily scale your application by supporting different types of locking because the lock cost is less than row-level locking.
Table locks take precedence over page-level or row-level locks when:
· Most of the statements on the table are used for reads.
· Read and update with strict keys, you can update or delete a row that can be extracted with a single read key:
• UPDATE tbl_name SET column = value WHERE unique_key_col = key_value;
• DELETE FROM tbl_name WHERE unique_key_col = key_value;
· SELECT combined with parallel INSERT statements and very few UPDATE or DELETE statements.
· There are many scans or GROUP BY operations on the entire table without any write operations.
Options that differ from row-level or page-level locking:
· Versioning (for example, the technique used in MySQL for parallel inserts), where there can be one write operation and many read operations at the same time. This means that the database or table supports different views of the data, depending on when access starts. Other common terms are "time tracking", "copy on write" or "copy on demand".
· On-demand replication takes precedence over page-level or row-level locking in many cases. However, in the worst case, it may use more memory than using regular locking.
· In addition to row-level locking, you can use application-level locking, such as GET_LOCK() and RELEASE_LOCK() in MySQL. These are advisory locks, and they will only work in applications that are running well.
To achieve maximum locking speed, MySQL uses table locking (rather than page, row, or column locking) for all storage engines except InnoDB and BDB. For InnoDB and BDB tables, MySQL only uses table locking if you explicitly lock the table with LOCK TABLES; if you do not use LOCK TABLES, because InnoDB uses automatic row-level locking and BDB uses page-level locking to ensure transaction isolation.
But for large tables, table locking is better than row locking for most applications, but has some drawbacks. Table locking enables many threads to read from a table simultaneously, but if a thread wants to write to the table, it must first obtain exclusive access. During the update, all other threads that want to access the table must wait until the update is complete.
Table updates are generally considered more important than table retrievals, so they are given higher priority. This should ensure that activity updating a table cannot starve, even if there is heavy SELECT activity on the table.
Table locking can cause problems in situations such as when a thread is waiting because the hard disk is full and there must be free space before the thread can process. In this case, all threads that want to access the table in question are also put into a waiting state until more hard disk space becomes available.
Table locking is also problematic in the following situations:
· A customer issues a long-running query.
· Then, another client updates the same table. The client must wait until the SELECT completes.
· Another client issues another SELECT statement on the same table. Because UPDATE has a higher priority than SELECT, the SELECT statement waits for UPDATE to complete, and waits for the first SELECT to complete.
Some methods are described below to avoid or reduce contention caused by table locks:
· Try to make the SELECT statement 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 a lower priority than SELECT statements. In this case, the second SELECT statement in the previous situation will be executed before the UPDATE statement without waiting for the first SELECT to complete.
· You can use the SET LOW_PRIORITY_UPDATES=1 statement to specify that all updates in a specific connection should use low priority.
· You can use the LOW_PRIORITY attribute to give a specific INSERT, UPDATE, or DELETE statement a lower priority.
· You can use the HIGH_PRIORITY attribute to give a specific SELECT statement a higher priority.
· Start mysqld by specifying a low value for the max_write_lock_count system variable to force MySQL to temporarily increase the priority of all SELECT statements waiting for a table after a specific number of inserts have completed. This allows a READ lock to be given after a certain number of WRITE locks.
· If you have issues with INSERT combined with SELECT, switch to using the new MyISAM tables as they support concurrent SELECT and INSERT.
· If you mix inserts and deletes on the same table, INSERT DELAYED will be of great help.
· If you have problems mixing SELECT and DELETE statements on the same table, DELETE's LIMIT option can help.
· Using SQL_BUFFER_RESULT for SELECT statements can help shorten table lock times.
· The lock code in mysys/thr_lock.c can be changed to use a single queue. In this case, write locks and read locks will have the same priority, which can be helpful for some applications.
Here are some tips related to table locking in MySQL:
· If you do not mix updates with selects that require checking many rows in the same table, you can do parallel operations.
· You can use LOCK TABLES to improve speed, because many updates in one lock are much faster than updates without locks. Splitting the contents of the table into several tables can also help.
· If you encounter speed issues when locking tables in MySQL, you can convert the table to an InnoDB or BDB table to improve performance.