锁定事宜

7.3. Locking Issues

7.3.1. Locking Methods

MySQL uses table-level locking for MyISAM and MEMORY tables, page-level locking forBDB tables, and row-level locking for InnoDB tables.

In many cases, you can make an educated guess about which locking type is best for an application, but generally it is difficult to say that a given lock type is better than another. Everything depends on the application and different parts of an application may require different lock types.

To decide whether you want to use a storage engine with row-level locking, 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, relatively few deletes, updates based mainly on key values, and inserts into a few specific tables. The base MySQLMyISAM setup is very well tuned for this.

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.

The table-locking method MySQL uses for WRITE locks works as follows:

• 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 table-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 and then to the threads in the read lock queue. This means that 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 theTable_locks_waited and Table_locks_immediate status variables:

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited    | 15324   |
+-----------------------+---------+

If a MyISAM table contains no free blocks in the middle, rows always are inserted at the end of the data file.(中间有deleted的insert先往改行插入) In this case, you can freely mix concurrentINSERT and SELECT statements for aMyISAM table without locks. That is, you can insert rows into aMyISAM 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 re-enabled automatically when all holes have been filled with new data.)

If you want to perform many INSERT andSELECT operations on a table when concurrent inserts are not possible, you can insert rows in a temporary table and update the real table with the rows from the temporary table once in a while. 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;

InnoDB uses row locks andBDB uses page locks. For these two storage engines, deadlocks are possible because they automatically acquire locks during the processing of SQL statements, not at the start of the transaction.

Advantages of row-level locking:

• Fewer lock conflicts when accessing different rows in many threads.

• Fewer changes for rollbacks.

• Possible to lock a single row for a long time.

Disadvantages of row-level locking:

• Requires more memory than page-level or table-level locks.

• Slower than page-level or table-level locks when used on a large part of the table because you must acquire many more locks.

• Definitely much slower than other locks if you often do GROUP BY operations on a large part of the data or if you must scan the entire table frequently.

Table locks are superior to page-level or row-level locks in the following cases:

• Most statements for the table are reads.

• A mix of reads and writes, where writes are updates or deletes for a single row that can be fetched with one key read:

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

  //by me 1个id就能够定位说明可以用索引，能用索引就会很快，那么就能把锁表时间降到最低

• 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.

  //by me 这么说来group by对行级锁的开销非常大

With higher-level locks, you can more easily tune applications by supporting locks of different types, because the lock overhead is less than for row-level locks.

Options other than row-level or page-level locking:

• Versioning (such as that used in MySQL for concurrent inserts) where it is possible to have one writer at the same time as many readers. This means that the database or table supports different views for the data depending on when access begins. Other common terms for this are “time travel,” “copy on write,” or “copy on demand.”

• Copy on demand is in many cases superior to page-level or row-level locking. However, in the worst case, it can use much more memory than using normal locks.

• Instead of using row-level locks, you can employ application-level locks, such asGET_LOCK() and RELEASE_LOCK() in MySQL. These are advisory locks, so they work only in well-behaved applications. (SeeSection 12.10.4, “Miscellaneous Functions”.) 

7.3.2. Table Locking Issues

To achieve a very high lock speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines exceptInnoDB and BDB.

For InnoDB and BDB tables, MySQL uses only table locking if you explicitly lock the table withLOCK TABLES. For these storage engines, we recommend that you not useLOCK TABLES at all, because InnoDB uses automatic row-level locking andBDB uses page-level locking to ensure transaction isolation.

For large tables, table locking is much better than row locking for most applications, but there are some pitfalls:

• 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 must wait until the update is done.

• Table updates normally are considered to be more important than table retrievals, so they are given higher priority. This should ensure that updates to a table are not “starved” even if there is heavySELECT activity for the table.

• Table locking causes problems in cases such as when a thread is waiting because the disk is full and free space needs to become available before the thread can proceed. In this case, all threads that want to access the problem table are also put in a waiting state until more disk space is made available.

Table locking is also disadvantageous under the following scenario:

• A client issues a SELECT that takes a long time to run.

• Another client then issues an UPDATE on the same table. This client waits until theSELECT is finished.

• Another client issues another SELECT statement on the same table. BecauseUPDATE has higher priority than SELECT, this SELECT waits for the UPDATE to finish, and for the firstSELECT to finish.

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. You might have to create some summary tables to do this.

• Start mysqld with--low-priority-updates. This gives all statements that update (modify) a table lower priority thanSELECT statements. In this case, the second SELECT statement in the preceding scenario would execute before the UPDATE statement, and would not need to wait for the first SELECT to finish.

• You can specify that all updates issued in a specific connection should be done with low priority by using theSET LOW_PRIORITY_UPDATES=1 statement. See Section 13.5.3, “SET Syntax”.

• You can give a specific INSERT, UPDATE, or DELETE statement lower priority with theLOW_PRIORITY attribute.

• You can give a specific SELECT statement higher priority with theHIGH_PRIORITY attribute. See Section 13.2.7, “SELECT Syntax”.

• You can start mysqld with a low value for themax_write_lock_count system variable to force MySQL to temporarily elevate the priority of allSELECT statements that are waiting for a table after a specific number of inserts to the table occur. This allowsREAD locks after a certain number of WRITE locks.

• If you have problems with INSERT combined with SELECT, you might want to consider switching to MyISAM tables, which support concurrentSELECT and INSERT statements. (SeeSection 7.3.3, “Concurrent Inserts”.)

• If you mix inserts and deletes on the same table, INSERT DELAYED may be of great help. SeeSection 13.2.4.2, “INSERT DELAYED Syntax”.

• If you have problems with mixed SELECT and DELETE statements, the LIMIT option to DELETE may help. See Section 13.2.1, “DELETE Syntax”.

• Using SQL_BUFFER_RESULT with SELECT statements can help to make the duration of table locks shorter. SeeSection 13.2.7, “SELECT Syntax”.

• You could 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.

Here are some tips concerning table locks in MySQL:

• Concurrent users are not a problem if you do not mix updates with selects that need to examine many rows in the same table.

• You can use LOCK TABLES to increase speed, because many updates within a single lock is much faster than updating without locks. Splitting table contents into separate tables may also help.

• If you encounter speed problems with table locks in MySQL, you may be able to improve performance by converting some of your tables toInnoDB or BDB tables. SeeSection 14.2, “The InnoDB Storage Engine”, and Section 14.5, “The BDB (BerkeleyDB) Storage Engine”.

7.3.3. Concurrent Inserts

For a MyISAM table, you can use concurrent inserts to add rows at the same time that SELECT statements are running if there are no deleted rows in middle of the table. (by me 在没有deleted 行的时候，并发的insert会append在表的尾部，也就是说如果这个表要想能够大量的insert它必须得没有怎么执行过delete语句)

Under circumstances where concurrent inserts can be used, there is seldom any need to use theDELAYED modifier for INSERT statements. SeeSection 13.2.4.2, “INSERT DELAYED Syntax”.

If you are using the binary log, concurrent inserts are converted to normal inserts forCREATE ... SELECT or INSERT ... SELECT statements. This is done to ensure that you can re-create an exact copy of your tables by applying the log during a backup operation.

With LOAD DATA INFILE, if you specifyCONCURRENT with a MyISAM table that satisfies the condition for concurrent inserts (that is, it contains no free blocks in the middle), other threads can retrieve data from the table whileLOAD DATA is executing. Using this option affects the performance ofLOAD DATA a bit, even if no other thread is using the table at the same time. 

//by me 看来myISAM 对update, delete, insert 进行了大量的优化和分类，对于这三种写，在互联网应用当中是insert > update > delete, 所以对于myISAM 不存在insert和select互相竞争的问题 -- 而且mysql当中默认隔离级别是repeated-read, 也就是只是可重复读，但是不保证幻读，所以myISAM这样非常高效率

MySQL 5.1支持对MyISAM和MEMORY表进行表级锁 定，对BDB表进行页级锁定，对InnoDB表进行行级锁定。

在许多情况下，可以根据培训猜测应用程序使用哪类锁定类型最好，但一般很难说出某个给出的锁类型就比另一个好。一切取决于应用程序，应用程序的不同部分可 能需要不同的锁类型。

为了确定是否想要使用行级锁定的存储引擎，应看看应用程序做什么并且混合使用什么样的选择和更新语句。例如，大多数Web应 用程序执行许多选择，而很少进行删除，只对关键字的值进行更新，并且只插入少量具体的表。基本MySQL MyISAM设置已经调节得很好。

在MySQL中对于使用表级锁定的存储引擎，表锁定时不会死锁的。这通过总是在一个查询开始时立即请求所有必要的锁定并且 总是以同样的顺序锁定表来管理。

对WRITE，MySQL使 用的表锁定方法原理如下：

• 如果在表上没有锁，在它上面放一个写锁。
• 否则，把锁定请求放在写锁定队列中。

对READ，MySQL使用的锁定方法 原理如下：

• 如果在表上没有写锁定，把一个读锁定放在它上面。
• 否则，把锁请求放在读锁定队列中。

当一个锁定被释放时，锁定可被写锁定队列中的线程得到，然后是读锁定队列中的线程。

这意味着，如果你在一个表上有许多更新，SELECT语句将等待直到没有更多的更新。

可以通过检查table_locks_waited和table_locks_immediate状 态变量来分析系统上的表锁定争夺：

mysql> SHOW STATUS LIKE 'Table%';

+-----------------------+---------+

| Variable_name         | Value   |

+-----------------------+---------+

| Table_locks_immediate | 1151552 |

| Table_locks_waited    | 15324   |

+-----------------------+---------+

如果INSERT语句不冲突，可以自由为MyISAM表混合并行的INSERT和SELECT语 句而不需要锁定。也就是说，你可以在其它客户正读取MyISAM表的时候插入行。如果数据文件中间不包含空闲块，不会发生冲 突，因为在这种情况下，记录总是插入在数据文件的尾部。(从表的中部删除或更新的行可能导致空洞）。如 果有空洞，当所有空洞填入新的数据时，并行的插入能够重新自动启用。

如果不能同时插入，为了在一个表中进行多次INSERT和SELECT操作，可以在临时表中插 入行并且立即用临时表中的记录更新真正的表。

这可用下列代码做到：

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;

InnoDB使用行锁定，BDB使用页锁定。对于这两种存储引擎，都可能存在死锁。这是因为，在SQL语 句处理期间，InnoDB自动获得行锁定和BDB获得页锁定，而不是在事务启动时获得。

行级锁定的优点：

·         当在许多线程中访问不同的行时只存在少量锁定冲突。

·         回滚时只有少量的更改。

·         可以长时间锁定单一的行。

行级锁定的缺点：

·         比页级或表级锁定占用更多的内存。

·         当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。

·         如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。

·         用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。

在以下情况下，表锁定优先于页级或行级锁定：

·         表的大部分语句用于读取。

·         对严格的关键字进行读取和更新，你可以更新或删除可以用单一的读取的关键字来提取的一行：

·                UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;

·                DELETE FROM tbl_name WHERE unique_key_col=key_value;

·         SELECT 结合并行的INSERT语句，并且只有很少的UPDATE或DELETE语 句。

·         在整个表上有许多扫描或GROUP BY操作，没有任何写操作。

不同于行级或页级锁定的选项：

·         版本(例如，为并行的插入在MySQL中使用的技术)， 其中可以一个写操作，同时有许多读取操作。这说明数据库或表支持数据依赖的不同视图，取决于访问何时开始。其它共同的术语是“时间跟踪”、“写复制”或者“按需复制”。

·         按需复制在许多情况下优先于页级或行级锁定。然而，在最坏的情况下，它可能比使用常规锁定使用更多的内存。

·         除了行级锁定外，你可以使用应用程序级锁定，例如在MySQL中使用GET_LOCK()和RELEASE_LOCK()。 这些是建议性锁定，它们只能在运行良好的应用程序中工作。

7.3.2. 表锁定事宜

为达到最高锁定速度，除InnoDB和BDB之外，对所有存储引擎，MySQL使 用表锁定(而不是页、行或者列锁定)。

对于InnoDB和BDB表，如果你用LOCK TABLES显式锁定表，MySQL只使用表锁定。对于这些表类型，我们建议你根本不要使用LOCK TABLES，因为InnoDB使用自动行级锁定而BDB使用页级锁定来保证事 务隔离。

对于大表，对于大多数应用程序，表锁定比行锁定更好，但存在部分缺陷。

表锁定使许多线程同时从一个表中进行读取操作，但如果一个线程想要对表进行写操作，它必须首先获得独占访问。更新期间，所有其它想要访问该表的线程必须等 待直到更新完成。

表更新通常情况认为比表检索更重要，因此给予它们更高的优先级。这应确保更新一个表的活动不能“饿死”， 即使该表上有很繁重的SELECT活动。

表锁定在这种情况下会造成问题，例如当线程正等待，因为硬盘已满并且在线程可以处理之前必须有空闲空间。在这种情况下，所有想要访问出现问题的表的线程也 被设置成等待状态，直到有更多的硬盘空间可用。

表锁定在下面的情况下也存在问题：

·         一个客户发出长时间运行的查询。

·         然后，另一个客户对同一个表进行更新。该客户必须等待直到SELECT完成。

·         另一个客户对同一个表上发出了另一个SELECT语句。因为UPDATE比SELECT优 先级高，该SELECT语句等待UPDATE完成，并且等待第1个SELECT完 成。

下面描述了一些方法来避免或减少表锁定造成的竞争：

·         试图使SELECT语句运行得更快。你可能必须创建一些摘要(summary)表 做到这点。

·         用--low-priority-updates启动mysqld。 这将给所有更新(修改)一个表的语句以比SELECT语句低的优先级。 在这种情况下，在先前情形的第2个SELECT语句将在UPDATE语 句前执行，而不需要等候第1个SELECT完成。

·         可以使用SET LOW_PRIORITY_UPDATES=1语句指定具体连接中的所有更 新应使用低优先级。参见13.5.3节，“SET语法”

·         可以用LOW_PRIORITY属性给与一个特定的INSERT、UPDATE或DELETE语 句较低优先级。

·         可以用HIGH_PRIORITY属性给与一个特定的SELECT语 句较高优先级。参见13.2.7节，“SELECT语法”。

·         为max_write_lock_count系 统变量指定一个低值来启动mysqld来强制MySQL在 具体数量的插入完成后临时提高所有等待一个表的SELECT语句的优先级。这样允许在一定数量的WRITE锁 定后给出READ锁定。

·         如果你有关于INSERT结合SELECT的问题，切换到使用新 的MyISAM表，因为它们支持并发的SELECT和INSERT。

·         如果你对同一个表混合插入和删除，INSERT DELAYED将会有很大的帮助。参见13.2.4.2节，“INSERT DELAYED语法”。

·         如果你对同一个表混合使用SELECT和DELETE语句出现问 题，DELETE的LIMIT选项可以有所帮助。参见13.2.1节，“DELETE语法”。

·         对SELECT语句使用SQL_BUFFER_RESULT可以 帮助使表锁定时间变短。参见13.2.7节，“SELECT语法”。

·         可以更改mysys/thr_lock.c中的锁代码以使用单一的队列。在这种情况下，写锁 定和读锁定将具有相同的优先级，对一些应用程序会有帮助。

这里是一些MySQL中表锁定相关的技巧：

·         如果不混合更新与需要在同一个表中检查许多行的选择，可以进行并行操作。

·         可以使用LOCK TABLES来提高速度，因为在一个锁定中进行许多更新比没有锁定的更新要快得多。将表中的内容切分为几个表也可以有所帮助。

·         如果在MySQL中表锁定时遇到速度问题，可以将你的表转换为InnoDB或BDB表 来提高性能。参见15.2节，“InnoDB存储引擎”和15.5节，“BDB (BerkeleyDB)存储引擎”。

http://my.oschina.net/captaintheron/blog/2789