Mysql 事务与锁机制

1. 在一个事务获取表t的某行的S锁之前, 他必须获取表t的一个IS锁或更强的锁

2. 在一个事务获取表t某行的X锁之前, 他必须获取一个t的IX锁

3. 这些规则可以总结为如下锁类型兼容矩阵：
 	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

4. 一个锁如果和已经存在的锁兼容, 就可以授权给请求他的事务, 但如果和已存在的锁不兼容则不行

 一个事务必须等待直到冲突的锁被释放.如果一个锁请求和一个已经存在的锁冲突， 并且一直不能被授权， 就会造成死锁。一旦死锁发生，InnoDB会选择其中一个报错并释放其持有的锁，直至解除死锁。意向锁并不会阻塞任何事情，除非是对全表的请求(例如, LOCK TABLES ... WRITE)， IX和IS锁的主要目的是表示有人正在或者准备锁定一行

四. 行锁与表锁优劣对比

　　行级锁定的优点：

• 当在许多线程中访问不同的行时只存在少量锁定冲突
• 回滚时只有少量的更改
• 可以长时间锁定单一的行

　　行级锁定的缺点：

• 比页级或表级锁定占用更多的内存
• 当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁
• 如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多
• 用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定　　

　　MySQL表锁定机制：当一个锁定被释放时，锁定可被写锁定队列中的线程得到，然后是读锁定队列中的线程。这意味着，如果你在一个表上有许多更新，SELECT语句将等待直到没有更多的更新。表更新通常情况认为比表检索更重要，因此给予它们更高的优先级，但这应确保更新一个表的活动不能“饿死”，即使该表上有很繁重的SELECT活动

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

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

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

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

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

• 表的大部分语句用于读取
• 对严格的unique_key进行读取和更新，你可以更新或删除可以用单一的读取的关键字来提取的一行：

  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操作，没有任何写操作

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

　　表锁定的注意事项：

• 使用SET LOW_PRIORITY_UPDATES=1语句可指定具体连接中的所有更新应使用低优先级；
  或用LOW_PRIORITY属性给与一个特定的INSERT、UPDATE或DELETE语句较低优先级；
  或用HIGH_PRIORITY属性给与一个特定的SELECT语句较高优先级；
  或为max_write_lock_count系统变量指定一个低值来启动mysqld来强制MySQL在具体数量的插入完成后临时提高所有等待一个表的SELECT语句的优先级；
  或用--low-priority-updates启动mysqld，这将给所有更新(修改)一个表的语句以比SELECT语句低的优先级

• 如果你有关于INSERT结合SELECT的问题，切换到使用新的MyISAM表，因为它们支持并发的SELECT和INSERT；
  如果你对同一个表混合插入和删除，INSERT DELAYED将会有很大的帮助；
  如果你对同一个表混合使用SELECT和DELETE语句出现问题，DELETE的LIMIT选项可以有所帮助；
  对SELECT语句使用SQL_BUFFER_RESULT可以帮助使表锁定时间变短；
  可以更改mysys/thr_lock.c中的锁代码以使用单一的队列，在这种情况下，写锁定和读锁定将具有相同的优先级；
  如果不混合更新与需要在同一个表中检查许多行的选择，可以进行并行操作；
  可以使用LOCK TABLES来提高速度，因为在一个锁定中进行许多更新比没有锁定的更新要快得多，另外将表中的内容切分为几个表也可以有所帮助

五. 选择MyISAM

　　一般而言，使用行级锁定的存储引擎，应看看应用程序做什么并且混合使用什么样的select和update。例如，大多数Web应用程序执行许多select，而很少进行delete，只对key的值进行更新，并且只插入少量行。在MySQL中对于使用表级锁定的存储引擎，表锁定时不会死锁的，这通过总是在一个查询开始时立即请求所有必要的锁定并且总是以同样的顺序锁定表来管理。

　　可以通过检查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

　　InnoDB使用行锁定，可能存在死锁。这是因为，在SQL语句处理期间，InnoDB自动获得行锁定，而不是在事务启动时获得。对于InnoDB和BDB(BerkeleyDB)表，如果你用LOCK TABLES显式锁定表，MySQL只使用表锁定，建议不要使用LOCK TABLES，因为InnoDB使用自动行级锁定而BDB使用页级锁定来保证事务隔离。

　　InnoDB支持外键约束。

　　一般说来，以多读为主也附带少量写首选MyISAM，否则选择InnoDB或其他引擎会更佳。

七. 悲观锁与乐观锁

　　悲观锁和乐观锁不是数据库中的标准概念，而只是一种通俗说法。

• 悲观锁：悲观锁指对数据被意外修改持保守态度，依赖数据库原生支持的锁机制来保证当前事务处理的安全性，防止其他并发事务对目标数据的破坏或破坏其他并发事务数据，将在事务开始执行前或执行中申请锁定，执行完后再释放锁定。这对于长事务来讲，可能会严重影响系统的并发处理能力

  LOCK TABLES a WRITE;
  INSERT INTO a VALUES (1,23),(2,34),(4,33);
  INSERT INTO a VALUES (8,26),(6,29);
  UNLOCK TABLES;

  锁定表可以加速用多个语句执行的INSERT操作，因为索引缓存区仅在所有INSERT语句完成后刷新到磁盘上一次。一般有多少INSERT语句即有多少索引缓存区刷新，如果能用一个语句插入所有的行，就不需要锁定；对于事务表，应使用BEGIN和COMMIT代替LOCK TABLES来加快插入

• 乐观锁：乐观锁相对悲观锁而言，先假想数据不会被并发操作修改，没有数据冲突，只在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则宣告失败，否则更新数据。这就要求避免使用长事务和锁机制，以免导致系统并发处理能力降低，保障系统生产效率。下面将说明使用乐观锁时的大致业务处理流程

  首 步：执行一次查询 select some_column as old_value from some_table where id = id_value (假设该值在当前业务处理过程中不会被其他并发事务修改)
  ...
  第n步：old_value参与中间业务处理，比如old_value被自己修改 new_value = f(old_value)。这期间可能耗时很长，但不会为持有 some_column 而申请所在的行或表锁定，因此其他并发事务可以获得该锁
  ...
  尾 步：执行条件更新 update some_table set some_column = new_value where id = id_value and some_column = old_value (条件更新中检查old_value是否被修改)

  

Mysql 事务与锁机制

标签：脏读   需要   ble   支持   sam   隔离   show   alt   mic