MYSQL数据库事务处理和锁机制(转载)

一、数据库事务的百科
数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作。
事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。
一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。
杂谈：
    1）全部的表类型都可以使用锁，但是只有InnoDB和BDB才有内置的事务功能。 InnoDB是什么，自己百科
    2）使用begin开始事务，使用commit结束事务，中间可以使用rollback回滚事务。
    3）在默认情况下，InnoDB表支持一致读。
 

二、为何引入事务？
    1>.数据完整性    2>.数据安全性    3>.充分利用系统资源，提高系统并发处理的能力

 

1. 事务的特征：原子性（Atomiocity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），这四个特性简称ACID特性。
    原子性：事务是数据库的逻辑工作单位，事务中包括的所有操作要么都做，要么都不做。
    一致性：事务执行的结果必须是使数据库从一个一致性的状态变到另外一个一致性状态。
    隔离性：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对其他事务是隔离的，并发执行的各个事务之间互相不干扰。
    持久性：一个事务一旦成功提交，对数据库中数据的修改就是持久性的。接下来其他的其他操作或故障不应该对其执行结果有任何影响。

 

2. MySQL通过SET AUTOCOMMIT, START TRANSACTION, COMMIT和ROLLBACK等语句支持本地事务。语法：
    START TRANSACTION | BEGIN [WORK]
     COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
     ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
     SET AUTOCOMMIT = {0 | 1}
     默认情况下，mysql是autocommit的，如果需要通过明确的commit和rollback来提交和回滚事务，那么需要通过明确的事务控制命令来开始事务。

    ①START TRANSACTION或BEGIN语句可以开始一项新的事务。
    ②COMMIT和ROLLBACK用来提交或者回滚事务。
    ③CHAIN和RELEASE子句分别用来定义在事务提交或者回滚之后的操作，chain会立即启动一个新事物，并且和刚才的事务具有相同的隔离级别，release则会断开和客户端的连接。
    ④SET AUTOCOMMIT可以修改当前连接的提交方式，如果设置了SET AUTOCOMMIT=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚。

 

3. MySQL的InnoDB引擎中事物与锁
    1) SELECT …… LOCK IN SHARE MODE
  会话事务中查找的数据，加上一个共享锁。若会话事务中查找的数据已经被其他会话事务加上独占锁的话，共享锁会等待其结束再加，若等待时间过长就会显示事务需要的锁等待超时。

     2) SELECT ….. FOR UPDATE
     会话事务中查找的数据，加上一个读更新琐，其他会话事务将无法再加其他锁，必须等待其结束。

     3) INSERT、UPDATE、DELETE
     会话事务会对DML语句操作的数据加上一个独占锁，其他会话的事务都将会等待其释放独占锁。

     4) gap and next key lock（间隙锁）
    InnoDB引擎会自动给会话事务中的共享锁、更新琐以及独占锁，需要加到一个区间值域的时候，再加上个间隙锁（或称范围锁），对不存在的数据也锁住，防止出现幻写。

 

4. 四种事务隔离模式
    1) READ UNCOMMITED
     SELECT的时候允许脏读，即SELECT会读取其他事务修改而还没有提交的数据。

 

    2)READ COMMITED
     SELECT的时候无法重复读，即同一个事务中两次执行同样的查询语句，若在第一次与第二次查询之间时间段，其他事务又刚好修改了其查询的数据且提交了，则两次读到的数据不一致。

 

    3) REPEATABLE READ
     SELECT的时候可以重复读，即同一个事务中两次执行同样的查询语句，得到的数据始终都是一致的。实现的原理是，在一个事务对数据行执行读取或写入操作时锁定了这些数据行。
    但是这种方式又引发了幻想读的问题。因为只能锁定读取或写入的行，不能阻止另一个事务插入数据，后期执行同样的查询会产生更多的结果。

 

    4)SERIALIZABLE
     与可重复读的唯一区别是，默认把普通的SELECT语句改成SELECT …. LOCK IN SHARE MODE。即为查询语句涉及到的数据加上共享琐，阻塞其他事务修改真实数据。
    serializable模式中，事务被强制为依次执行。这是SQL标准建议的默认行为。

 

    查看InnoDB系统级别的事务隔离级别：mysql> SELECT @@global.tx_isolation;
     查看InnoDB会话级别的事务隔离级别：mysql> SELECT @@tx_isolation;
     修改InnoDB系统级别的事务隔离级别：mysql> set global transaction isolation level read committed;
     修改InnoDB会话级别的事务隔离级别：mysql> set session transaction isolation level read committed;

 

三、锁机制
    1) 共享锁：由读表操作加上的锁，加锁后其他用户只能获取该表或行的共享锁，不能获取排它锁，也就是说只能读不能写
    2) 排它锁：由写表操作加上的锁，加锁后其他用户不能获取该表或行的任何锁，典型是mysql事务中的

    锁的范围:
     行锁: 对某行记录加上锁
    表锁: 对整个表加上锁
  
     这样组合起来就有,行级共享锁,表级共享锁,行级排他锁,表级排他锁

1. 加锁与解锁
    LOCK TABLES tablename WRITE;
     LOCK TABLES tablename READ;
     Insert INTO assignment VALUES (1,7513,'0000-00-00',5),(1,7513,'2003-01-20',8.5);
     UNLOCK TABLES;

 

2. 不同锁的优缺点及选择
行级锁的优点及选择：
    1）在很多线程请求不同记录时减少冲突锁。
    2）事务回滚时减少改变数据。
    3）使长时间对单独的一行记录加锁成为可能。

行级锁的缺点：
    1）比页级锁和表级锁消耗更多的内存。
    2）当在大量表中使用时，比页级锁和表级锁更慢，因为他需要请求更多的所资源。
    3）当需要频繁对大部分数据做 GROUP BY 操作或者需要频繁扫描整个表时，就明显的比其它锁更糟糕。
    4）使用更高层的锁的话，就能更方便的支持各种不同的类型应用程序，因为这种锁的开销比行级锁小多了。
    5）可以用应用程序级锁来代替行级锁，例如MySQL中的 GET_LOCK() 和 RELEASE_LOCK()。但它们是劝告锁（原文：These are advisory locks），因此只能用于安全可信的应用程序中。
    6）对于 InnoDB 和 BDB 表，MySQL只有在指定用 LOCK TABLES 锁表时才使用表级锁。在这两种表中，建议最好不要使用 LOCK TABLES，因为 InnoDB 自动采用行级锁，BDB 用页级锁来保证事务的隔离。

 

表锁的优点及选择：
    1）很多操作都是读表。
    2）在严格条件的索引上读取和更新，当更新或者删除可以用单独的索引来读取得到时：UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;DELETE FROM tbl_name WHERE unique_key_col=key_value;
     3）SELECT 和 INSERT 语句并发的执行，但是只有很少的 UPDATE 和 DELETE 语句。
    4）很多的扫描表和对全表的 GROUP BY 操作，但是没有任何写表。

 

表锁的缺点：
    1）一个客户端提交了一个需要长时间运行的 SELECT 操作。
    2）其他客户端对同一个表提交了 UPDATE 操作，这个客户端就要等到 SELECT 完成了才能开始执行。
    3）其他客户端也对同一个表提交了 SELECT 请求。由于 UPDATE的优先级高于 SELECT，所以 SELECT 就会先等到 UPDATE 完成了之后才开始执行，它也在等待第一个 SELECT操作