mysql 开发基础系列20 事务控制和锁定语句(上)

一.概述

　　在mysql 里不同存储引擎有不同的锁，默认情况下，表锁和行锁都是自动获得的，不需要额外的命令， 有的情况下，用户需要明确地进行锁表或者进行事务的控制，以便确保整个事务的完整性。这样就需要使用事务控制和锁定语句来完成。

特点	myisam	innodb	memory	merge	ndb
事务安全		支持
锁机制	表锁	表锁 行锁(默认)	表锁	表锁	行锁 页锁(默认)

1.  lock table 和nolock tables

　　lock table是锁定当前线程的表。如果当前表被锁定，其它线程想获取该表的锁时，会一直等待，直到获取到该表锁为止。
　　下面演示一个获得表锁和释放表锁的简单例子，演示会话1 获取city表的read锁后，会话2更新该表记录时 会等待，当会话1 city表释放后， 会话2就会更新完成。

　　--   步骤1 会话1获取city 表锁

　    　

　　--- 步骤2  会话2更新city表一直在等待

　　

　　-- 步骤3  会话1 city表释放表锁

　 　

　　---步骤4   会话2 city表更新成功

　 　

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.  start transaction  和 commit and chain 

　　　　使用START TRANSACTION 开始的事务在commit提交后， 会自动回到 autocommit 自动提交的方式；如果在事务提交的时候使用COMMIT AND CHAIN，那么会在事务提交后立即开始一个新的事务。

会话1	会话2
查询country 表 SELECT * FROM city WHERE country ='法国'; 结果为空	查询country 表 SELECT * FROM city WHERE country ='法国'; 结果为空
-- 启动一个事务 START TRANSACTION; INSERT INTO country(country, last_update) VALUES('法国',NOW());
	-- 查询 仍然为空 SELECT * FROM city WHERE country ='法国';
-- 提交事务 COMMIT;
	再次查询country 表 SELECT * FROM city WHERE country ='法国'; country_id        country    last_update   4          法国         2018-07-12 14:16:04
-- 重新启动一个事务 START TRANSACTION; INSERT INTO country(country, last_update) VALUES('德国',NOW()); COMMIT AND CHAIN; -- chain 自动开始一个新事务 INSERT INTO country(country, last_update) VALUES('日本',NOW());
	查询country 表 country_id        country    last_update 2       中国         2018-07-03 18:06:45 3       美国         2018-07-12 14:15:02 4       法国         2018-07-12 14:16:04 5       德国         2018-07-12 14:21:20
-- 提交事务 COMMIT;
	country_id        country    last_update 2       中国         2018-07-03 18:06:45 3       美国         2018-07-12 14:15:02 4       法国         2018-07-12 14:16:04 5       德国         2018-07-12 14:21:20 6       日本         2018-07-12 14:21:20