为达到最高锁定速度，除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表来提高性能。 

1. read lock
   

    *************************** 1. row *************************** 
      Table: test_lock_tab     Create Table: CREATE TABLE `test_lock_tab` (       `id` smallint(5) unsigned zerofill NOT NULL AUTO_INCREMENT,       `name` char(36) NOT NULL,       PRIMARY KEY (`id`)     ) ENGINE=    MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 
        
   
test $~ select * from test_lock_tab; +-------+------------+ | id    |   name     | +-------+------------+ | 00001 |  河蟹XXXX  |  +-------+------------+

当前session0： test_lock_tab   + read lock test $~ insert into test_lock_tab set name='默默QQQQ';  ERROR 1099 (HY000): Table 'test_lock_tab' was locked with a READ lock and can't be updated

test $~ select * from a; ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES test $~ select * from test_lock_tab;  正常,包括不用缓存的读操作。

sessionX: 对 test_lock_tab 的写操作不能执行，等待read锁释放；所有session read 操作正常。

当 session0 再去占有其他表如的锁时，对 test_lock_tab 锁自动释放。

然而，此时，sessionA 对 test_lock_tab 表进行更新(UPDATE) or 删除(DELETE)。该客户sessionA 必须等待直到SELECT完成。sessionB 再对 test_lock_tab 表上发出了另一个SELECT语句。因为 UPDATE 比 SELECT 优先级高（默认），该SELECT语句等待 UPDATE(sessionA) 完成， 并且等待第1个 SELECT（session0） 完成。

除非 select 从 query_cache 读取【query_cache的重要性】。 或者給 SELECT 授予 HIGH_PRIORITY 属性。再或者~~~

在然，此时，sessionA 对 test_lock_tab 表进行插入(INSERT)。该客户sessionA 必须等待直到SELECT完成。sessionB 再对 test_lock_tab 表上发出了另一个SELECT语句不需等待 sessionA 的 INSERT 操作完成。

                                     priority[praiˈɔriti]  

值得注意的一个参数：

query_cache_wlock_invalidate !!! 一般情况，当客户端对MyISAM表进行WRITE锁定时，如果查询结果位于查询缓存中，则其它客户端未被锁定，可以对该表进行查询。将该变量设置为1，则可以对表进行WRITE锁定，使查询缓存内所有对该表进行的查询变得非法。这样当锁定生效时，可以强制其它试图访问表的客户端来等待。

-- 就算有缓存也不让用... ⊙﹏⊙b汗

(有同学把这个设置为 on 了,不知其用意...不知道这东西在哪个应用应该on,还未发现...)

(未完...待续)

 

InnoDB 中各 SQL 语句的锁定设置

  • SELECT ... FROM ... : 这是一个 consistent read，不以锁定方式读取数据库的快照，除非事务的隔离级被设置为 SERIALIZABLE，在这种情况下将在它所读取的记录索引上设置共享的 next-key locks。
  • SELECT ... FROM ... LOCK IN SHARE MODE : 在所读取的所有记录索引上设置同享的锁定。
  • SELECT ... FROM ... FOR UPDATE : 在所读取的所胡记录索引上设置独占地(exclusive)锁定。
  • INSERT INTO ... VALUES (...) : 在插入的记录行上设置一个独占地锁定；注意这个锁定并不是一个 next-key lock ，并不会阻止其它用户在所插入行之前的间隙(gap)中插入新记录。如果产生一个重复键值错误， 在重复索引记录上设置一个共享的锁定。
  • 如果在一个表中定义了一个 AUTO_INCREMENT 列，InnoDB 在初始化自增计数器时将在与自增列最后一个记录相对应的索引上设置一个独占的锁定。在访问自增计数器时， InnoDB 将设置一个特殊的表锁定模式 AUTO-INC ，这个锁定只持续到该 SQL 语句的结束而不是整个事务的结束。
  • INSERT INTO T SELECT ... FROM S WHERE ... 在已插入到表 T 中的每个记录上设置一个独占的(无 next-key)锁定。以一个 consistent read 搜索表 S ，但是如果 MySQL 打开了日志开关将在表 S 上设置一个共享的锁定。 在从备份中进行前滚(roll-forward)修复时，每个 SQL 语句必须严格按照原先所执行的顺序运行，所以 InnoDB 不得不设置锁定。
  • CREATE TABLE ... SELECT ... 与上项相似，以 consistent read 或锁定方式完成 SELECT 。
  • REPLACE 如果没有一个 unique key 冲突，它的执行与 insert 一致。否则将在它所要更新的记录上设置一个独占的锁定。
  • UPDATE ... SET ... WHERE ... : 在搜索时所遭遇到的记录上设置一个独占的锁定。
  • DELETE FROM ... WHERE ... : 在搜索时所遭遇到的每一个记录上设置一个独占的锁定。
  • 如果一个表上有 FOREIGN KEY 约束，所有需要检查约束条件的 insert, update, 或 delete 将在它所要检查约束的记录上设置记录共享级的锁定。同样在约束失败时，InnoDB 也设置这个锁定。
  • LOCK TABLES ... : 设置表锁定。在 MySQL 的代码层(layer of code)设置这些锁定。InnoDB 的自动死锁检测无法检测出有关下列情形的表锁定：查看下面的一个章节。同时查看第 14 章节 'InnoDB 限制与不足' 有关下列内容： 自从 MySQL 提供行锁以来，将有可能发生当其他用户设置了行级锁定时你又对该表设置了锁定。But that does not put transaction integerity into danger.
  • 在 3.23.50 版本以前， SHOW TABLE STATUS 应用于一个自增表时将在自增列的最大记录索引上设置一个独占的行级锁定。 这就意味着 SHOW TABLE STATUS 可能会引起一个事务的死锁，这可能是我们所意想不到的。从 3.23.50 开始，在读取自增列值时将不再设置任何锁定，除非在某些情况下，比如在数据库启动后没有任何记录。

*************************** 1. row ***************************        Table: t1 Create Table: CREATE TABLE `t1` (   `a` int(11) NOT NULL,   `b` char(36) DEFAULT NULL,   PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)  
session1:
mysql>  select  *  from t1 where a>500 and a < 1000 for update; +-----+------+ | a   | b    | +-----+------+ | 888 | 88   | | 909 | 90   | | 999 | 99   | +-----+------+ 3 rows in set (0.00 sec)
session2:
mysql> update t1 set a=177 where a=55;    Query OK, 1 row affected (7.13 sec) Rows matched: 1  Changed: 1  Warnings: 0
mysql> update t1 set a=199 where a=53;   Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0
!!!!!!!!! ???/???      mysql> update t1 set a=299 where a=23; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

 

 

转载于:https://blog.51cto.com/tanzj/174968