mysql 改表名锁表_详解mysql 锁表 for update (引擎/事务)

本节内容：

探讨下mysql适应for update锁表的方法。

由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。

例子:

假设有个表单products ，里面有id跟name二个栏位，id是主键。

例1: (明确指定主键，并且有此笔资料，row lock)

复制代码 代码示例:

SELECT * FROM products WHERE id='3' FOR UPDATE;

例2: (明确指定主键，若查无此笔资料，无lock)

复制代码 代码示例:

SELECT * FROM products WHERE id='-1' FOR UPDATE;

例3: (无主键，table lock)

复制代码 代码示例:

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例4: (主键不明确，table lock)

复制代码 代码示例:

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。

注2: 要测试锁定的状况，可以利用MySQL的Command Mode ，开二个视窗来做测试。

由上面的InnoDB 已经交易区块引出两个问题。

1.InnoDB

mysql中有多种引擎可以供选择。

如果赛车手能根据不同的路况，地形随手更换与之最适宜的引擎，那么他们将创造奇迹。

然而目前他们还做不到那样便捷的更换引擎，但是我们却可以！

MySQL提供的几种引擎。

一般来说，MySQL有以下几种引擎：ISAM、MyISAM、HEAP、InnoDB和Berkley(BDB)。

注意：不同的版本支持的引擎是有差异的。

进一步：

如何查看MySQL的当前存储引擎？

一般情况下，mysql会默认提供多种存储引擎,可以通过下面的查看:

看的mysql现在已提供什么存储引擎:

复制代码 代码示例:

mysql> show engines;

mysql默认使用的InnoDB引擎，并且支持MyISAM，memory，archive，Mrg_myisam。

后面还跟着解释，InnoDB 的解释是：支持事务，行级别锁定，外键。

看的mysql当前默认的存储引擎:

复制代码 代码示例:

mysql> show variables like '%storage_engine%';

要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):

复制代码 代码示例:

mysql> show create table 表名;

其实这是一定的，因为我的mysql引擎就是InnoDB，默认情况下创建的表当前也是以InnoDB为引擎的喽

2. 交易区块

事务处理在各种管理系统中都有着广泛的应用，比如人员管理系统，很多同步数据库操作大都需要用到事务处理。

比如说，在人员管理系统中，删除一个人员，即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

删除的SQL语句

复制代码 代码示例:

delete from userinfo where ~~~

delete from mail where ~~

delete from article where~~

如果没有事务处理，在删除的过程中，假设出错了，只执行了第一句，那么其后果是难以想象的！

但用事务处理。如果删除出错，只要rollback就可以取消删除操作(其实是只要没有commit就没有确实的执行该删除操作)   一般来说，在商务级的应用中，都必须考虑事务处理的！

MYSQL数据库从4.1就开始支持事务功能，据说5.0将引入存储过程。

事务是DBMS得执行单位。它由有限得数据库操作序列组成得。但不是任意得数据库操作序列都能成为事务。一般来说，事务是必须满足4个条件(ACID)

1，原子性(Autmic)：事务在执行性，要做到“要么不做，要么全做！”，就是说不允许事务部分得执行。即使因为故障而使事务不能完成，在rollback时也要消除对数据库得影响！

2，一致性(Consistency)：事务得操作应该使使数据库从一个一致状态转变倒另一个一致得状态！就拿网上购物来说吧，只有即让商品出库，又让商品进入顾客得购物篮才能构成事务！

3，隔离性(Isolation)：如果多个事务并发执行，应象各个事务独立执行一样！

4，持久性(Durability)：一个成功执行得事务对数据库得作用是持久得，即使数据库应故障出错，也应该能够恢复！

MYSQL的事务处理主要有两种方法。

1、用begin,rollback,commit来实现

begin 开始一个事务

rollback 事务回滚

commit  事务确认

2、直接用set来改变mysql的自动提交模式

MYSQL默认是自动提交的，也就是提交一个QUERY，它就直接执行！可以通过

set autocommit=0   禁止自动提交

set autocommit=1 开启自动提交

注意：用 set autocommit=0 时，以后所有的SQL都将做为事务处理，直到用commit确认或rollback结束，注意当结束这个事务的同时也开启了个新的事务！

按第一种方法只将当前的作为一个事务！个人推荐使用第一种方法！

MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理！其他的类型是不支持的！

MYSQL5.0 WINXP下测试通过：

复制代码 代码示例:

mysql> use test;

Database changed

mysql> CREATE TABLE `dbtest`(

-> id int(4)

-> ) TYPE=INNODB;

Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> select * from dbtest

-> ;

Empty set (0.01 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into dbtest value(5);

Query OK, 1 row affected (0.00 sec)

mysql> insert into dbtest value(6);

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from dbtest;

+------+

| id    |

+------+

|     5 |

|     6 |

+------+

2 rows in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into dbtest values(7);

Query OK, 1 row affected (0.00 sec)

mysql> rollback;   //这里回滚了

Query OK, 0 rows affected (0.00 sec)

mysql> select * from dbtest;

+------+

| id    |

+------+

|     5 |

|     6 |

+------+

2 rows in set (0.00 sec)

用for update来锁定行，以上例中student表为例：

1，使用begin开始一个事务

2，利用select * for update 锁定行，

3，在新窗口中验证非选中行是否被锁定 ---未被锁定

4，在新窗口中验证选中行是否被锁定  ---锁定，update语句在等待了一段时间后失败。