LOCk TABLE 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有的锁为止。
UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个LOCK TABLES时，或当与服务器的连接被关闭时，所有当前线程锁定的表被隐含的解锁，具体语法如下:
LOCK TABLES tbl_name [as alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} [,tbl_name] [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ... UNLOCK TABLES如下是一个获得表锁和释放表锁的简单例子。
创建测试表 create table lock_demo( id int auto_increment, meg varchar(20), primary key(id) )engine=MYISAM; -- Query OK, 0 rows affected (0.09 sec) 插入数据 insert into lock_demo values(0,'scffgdsfg'),(0,'57777'),(0,'treg'); --Query OK, 3 rows affected (0.00 sec) --Records: 3 Duplicates: 0 Warnings: 0 锁定表 lock table lock_demo read; --Query OK, 0 rows affected (0.00 sec) 查询表中的数据 select * from lock_demo; --+----+-----------+ --| id | meg | --+----+-----------+ --| 1 | scffgdsfg | --| 2 | 57777 | --| 3 | treg | --+----+-----------+ --3 rows in set (0.00 sec) 重新开启一个窗口，新建一个 session这里标注为session2，而原来的session标注为session1 mysql -uroot -proot use test; 通过新建的session2查询数据 select * from lock_demo; --+----+-----------+ --| id | meg | --+----+-----------+ --| 1 | scffgdsfg | --| 2 | 57777 | --| 3 | treg | --+----+-----------+ --3 rows in set (0.01 sec)可以正常查询数据
使用新建session2更新表 update lock_demo set msg='update message' where id = 1; 等待更新操作一直处于等待状态。
session1释放锁 UNLOCK TABLES; --Query OK, 0 rows affected (0.00 sec) --session2获得锁更新成功 update lock_demo set meg='update message' where id = 1; --Query OK, 1 row affected (0.00 sec) --Rows matched: 1 Changed: 1 Warnings: 0MySQL通过SET AUTOCOMMIT、START TRANSACTION、ＣＯＭＭＩＴ和ＲＯＬＬＢＡＣＫ等语句支持本地事务，具体与法如下：
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来提交和回滚事务,那就需要明确的事务控制命令来控制事务.如果应用是从Oracle数据迁移到Mysql数据库,则需要确保应用中是否对事务进行了明确的管理.
START TRANSACTION或BEGIN语句可以开启一项新的事务.COMMIT和ROLLBACK用来提交和回滚事务.CHAIN和RELEASE字句分别来定义事务在提交前或回滚之后的操作,CHAIN会立即启动一个新事务,并且和刚才的事务具有相同的隔离级别,RELEASE则会断开和客户端的连接.SET AUTOCOMMIT可以修改当前连接的提交方式,如果设置了SET AUTOCOMMIT=0,则设置之后的所有事务都需要明确的命令提交或者回滚.如果只对某些语句进行事务控制的时候,可以使用START TRANSACTION命令来开启一个事务比较方便,这样事务结束后就可以回到自动提交事务的方式 如果希望事务都不是自动提交的,那么通过修改AUTOCOMMIT比较方便,这样就不用在每个事务开始的时候在执行START TRABSATION语句.
下面是一个通过START TRANSACTION来开启事务的示例
新建表tran_demo create table tran_demo( id int auto_increment, context varchar(50), primary key (id) ); --Query OK, 0 rows affected (0.25 sec) 在session1中查询表中数据 select * from tran_demo; --Empty set (0.01 sec) 在session2中查询表中数据 select * from tran_demo; --Empty set (0.00 sec) 在session中通过START TRANSACTION来启动一个事务,项表中插入一条数据没有提交 START TRANSACTION; --Query OK, 0 rows affected (0.00 sec) insert into tran_demo values(0,'this is a transaction demo'); --Query OK, 1 row affected (0.00 sec) 在session2 中查询表tran_demo,表中数据任然为空 select * from tran_demo; --Empty set (0.00 sec) 在session1中提交事务 commit; --Query OK, 0 rows affected (0.06 sec) 再次在session2中查询表数据,可以查询到结果 select * from tran_demo; --+----+----------------------------+ --| id | context | --+----+----------------------------+ --| 1 | this is a transaction demo | --+----+----------------------------+ --1 row in set (0.00 sec) 通过session1再次向表中插入数据 insert into tran_demo values(0,'after commit'); Query OK, 1 row affected (0.07 sec) 通过session2查询表中数据,可以查询到刚才插入的数据 select * from tran_demo; --+----+----------------------------+ --| id | context | --+----+----------------------------+ --| 1 | this is a transaction demo | --| 2 | after commit | --+----+----------------------------+ --2 rows in set (0.00 sec) 在session1中重新启动一个事务 START TRANSACTION; --Query OK, 0 rows affected (0.00 sec) 向表tran_demo中插入一条数据 insert into tran_demo values(0,'this is chain demo'); 使用commit and chain 提交事务 commit and chain; --Query OK, 0 rows affected (0.04 sec)此时自动开启了一个新的事务
insert into tran_demo values(0,'this is new transaction'); 在session2中查询表中数据 select * from tran_demo; --+----+----------------------------+ --| id | context | --+----+----------------------------+ --| 1 | this is a transaction demo | --| 2 | after commit | --| 3 | this is chain demo | --+----+----------------------------+ --3 rows in set (0.00 sec)查询不到刚才session1中插入的数据
在session1中提交事务 commit; --Query OK, 0 rows affected (0.04 sec) 再次在session2中查询表tran_demo中数据 select * from tran_demo; --+----+----------------------------+ --| id | context | --+----+----------------------------+ --| 1 | this is a transaction demo | --| 2 | after commit | --| 3 | this is chain demo | --| 4 | this is new transaction | --+----+----------------------------+ --4 rows in set (0.00 sec)如果在锁表期间,用START TRANSATION来启动一个新的事务,会造成一个隐含的unlock table被执行,如下示例
从session1和session2中分别查询表tran_demo表中id为10的记录为空 --session1 select * from tran_demo where id= 10; --Empty set (0.00 sec) --session2 select * from tran_demo where id= 10; --Empty set (0.00 sec) 在session1中对表tran_demo加表锁 lock table tran_demo write; --Query OK, 0 rows affected (0.02 sec) 在session2中查询表tran_demo的数据,读操作被阻塞 select * from tran_demo; --等待 在session1中通过 STARTTRANSACTION命令重新开启一个事务 START TRANSACTION; --Query OK, 0 rows affected (0.00 sec) --表锁被释放,session2中查询进程查询到数据 --+----+----------------------------+ --| id | context | --+----+----------------------------+ --| 1 | this is a transaction demo | --| 2 | after commit | --| 3 | this is chain demo | --| 4 | this is new transaction | --+----+----------------------------+ --4 rows in set (1 min 39.97 sec)因此在同一个事务中最好不要使用不同存储引擎的表,否则ROLLBACK时需要对非事务类型的表进行特别的处理,因为COMMIT、ROLLBACK只能对事务类型的表进行提交和回滚。
通常情况下，只对提交的事务记录到二进制的日志中，但是如果一个事务中包含非事务类型的表，南无回滚操作也会被记录到二进制日志中，以确保非事务类型的表的更新可以被复制从（Slave）数据库中。
和二Oracle的事务管理相同，所有的DDL语句是不能回滚的，并且部分的DDL语句会造成隐式的提交。
在事务中可以定义SAVEPOINT,指定回滚事务的一部分,但是不能指定提交事务的一部分.对应复杂的应用,可以定义多个复杂的SAVEPOINT,满足不同的有条件时,回滚不同的SAVEPOINT.需要注意的是,如归定义了相同名字的SAVEPOINT,则后面定义的SAVEPOINT会覆盖前面的定义。对于不再使用的SAVEPOINT,可以通过RELEASE SAVEPOINT命令删除SAVEPOINT ,删除后的SAVEPOINT不再执行ROLLBACK TO SAVEPOINT命令．
如下是一个通过SAVEPOINT来指定需要回滚的事务的案例
模拟回滚事务
从session1中查找表tran_demo中context='SAVEPOINT’的记录,结果为空 select * from tran_demo where context='SAVEPOINT'; --Empty set (0.05 sec) 从session2中查询表tran_demo中context='SAVEPOINT’的记录,结果为空 select * from tran_demo where context='SAVEPOINT'; --Empty set (0.05 sec) 在session1中启动一个事务,并在表tran_demo中插入一条数据 start transaction; --Query OK, 0 rows affected (0.00 sec) insert into tran_demo values(0,'SAVEPOINT') --Query OK, 1 row affected (0.00 sec) 在session1中可以查询到刚才插入的数据 select * from tran_demo where context = 'SAVEPOINT'; --+----+-----------+ --| id | context | --+----+-----------+ --| 5 | SAVEPOINT | --+----+-----------+ --1 row in set (0.00 sec) 在session2中无法查询到刚才插入的数据 select * from tran_demo where context = 'SAVEPOINT'; --Empty set (0.00 sec) 在session1中定义savepoint,命名为test savepoint test; --Query OK, 0 rows affected (0.00 sec) 在session1中继续插入一条数据 insert into tran_demo values(0,'savepoint test'); --Query OK, 1 row affected (0.00 sec) session1中可以查询到两条数据 select * from tran_demo where context like 'savepoint%'; --+----+----------------+ --| id | context | --+----+----------------+ --| 5 | SAVEPOINT | --| 6 | savepoint test | --+----+----------------+ --2 rows in set (0.01 sec) 在session2中查询数据,结果不存在 select * from tran_demo where context like 'savepoint%'; --Empty set (0.00 sec) 在session1中回滚到刚才定义的savepoint rollback to savepoint test; --Query OK, 0 rows affected (0.01 sec) --查询数据只能查询到一条记录,因为刚才的数据被回滚 select * from tran_demo where context like 'savepoint%'; --+----+-----------+ --| id | context | --+----+-----------+ --| 5 | SAVEPOINT | --+----+-----------+ --1 row in set (0.00 sec) 在session2中任然无法查询到数据 select * from tran_demo where context like 'savepoint%'; --Empty set (0.00 sec) session1中通过commit命令提交事务 commit; --Query OK, 0 rows affected (0.07 sec) --只能从表中查询到一条数据 select * from tran_demo where context like 'savepoint%'; --+----+-----------+ --| id | context | --+----+-----------+ --| 5 | SAVEPOINT | --+----+-----------+ --1 row in set (0.00 sec) 通过session2只能查询到一条数据 select * from tran_demo where context like 'savepoint%'; --+----+-----------+ --| id | context | --+----+-----------+ --| 5 | SAVEPOINT | --+----+-----------+ --1 row in set (0.00 sec)