mysql 锁表锁行语句分享(MySQL事务处理)【分分快三

作者:分分快三全天计划网站

一.概述

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

 特点

myisam

innodb

memory

merge

ndb

事务安全

 

支持

 

 

 

锁机制

表锁

表锁

行锁(默认)

表锁

表锁

行锁

页锁(默认)

1.  lock table 和nolock table

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

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

      分分快三全天计划网站 1

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

  分分快三全天计划网站 2

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

   分分快三全天计划网站 3

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

   分分快三全天计划网站 4

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

 

 

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

代码如下: mysql_query("set autocommit=0"); $list_one = $db-fetch_first("select * from prizes where id = ".$id." FOR UPDATE"); $db-query("DELETE from prizes WHERE i...

START TRANSACTION或BEGIN语句可以开始一项新的事务。COMMIT可以提交当前事务,是变更成为永久变更。ROLLBACK可以 回滚当前事务,取消其变更。SET AUTOCOMMIT语句可以禁用或启用默认的autocommit模式,用于当前连接。
自选的WORK关键词被支持,用于COMMIT和RELEASE,与CHAIN和RELEASE子句。CHAIN和RELEASE可以被用于对事务完成进行附加控制。Completion_type系统变量的值决定了默认完成的性质。
AND CHAIN子句会在当前事务结束时,立刻启动一个新事务,并且新事务与刚结束的事务有相同的隔离等级。RELEASE子句在终止了当前事务后,会让服务器断开与当前客户端的连接。包含NO关键词可以抑制CHAIN或RELEASE完成。如果completion_type系统变量被设置为一定的值,使连锁或释放完成可以默认进行,此时NO关键词有用。
默认情况下,MySQL采用autocommit模式运行。这意味着,当您执行一个用于更新(修改)表的语句之后,MySQL立刻把更新存储到磁盘中。
如果您正在使用一个事务安全型的存储引擎(如InnoDB, BDB或NDB簇),则您可以使用以下语句禁用autocommit模式:
SET AUTOCOMMIT=0;
通过把AUTOCOMMIT变量设置为零,禁用autocommit模式之后,您必须使用COMMIT把变更存储到磁盘中,或着如果您想要忽略从事务开始进行以来做出的变更,使用ROLLBACK。
如果您想要对于一个单一系列的语句禁用autocommit模式,则您可以使用START TRANSACTION语句:

START TRANSACTION或BEGIN语句可以开始一项新的事务。COMMIT可以提交当前事务,是变更成为永久变更。ROLLBACK可以 回滚当前事务,取消其变更。SET AUTOCOMMIT语句可以禁用或启用默认的autocommit模式,用于当前连接。
自选的WORK关键词被支持,用于COMMIT和RELEASE,与CHAIN和RELEASE子句。CHAIN和RELEASE可以被用于对事务完成进行附加控制。Completion_type系统变量的值决定了默认完成的性质。
AND CHAIN子句会在当前事务结束时,立刻启动一个新事务,并且新事务与刚结束的事务有相同的隔离等级。RELEASE子句在终止了当前事务后,会让服务器断开与当前客户端的连接。包含NO关键词可以抑制CHAIN或RELEASE完成。如果completion_type系统变量被设置为一定的值,使连锁或释放完成可以默认进行,此时NO关键词有用。
默认情况下,MySQL采用autocommit模式运行。这意味着,当您执行一个用于更新(修改)表的语句之后,MySQL立刻把更新存储到磁盘中。
如果您正在使用一个事务安全型的存储引擎(如InnoDB, BDB或NDB簇),则您可以使用以下语句禁用autocommit模式:
SET AUTOCOMMIT=0;
通过把AUTOCOMMIT变量设置为零,禁用autocommit模式之后,您必须使用COMMIT把变更存储到磁盘中,或着如果您想要忽略从事务开始进行以来做出的变更,使用ROLLBACK。
如果您想要对于一个单一系列的语句禁用autocommit模式,则您可以使用START TRANSACTION语句:

mysql_query("set autocommit=0");
$list_one = $db->fetch_first("select * from prizes where id = ".$id." FOR UPDATE");
$db->query("DELETE from prizes WHERE id =".$list_one['id']);
mysql_query("commit");

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

使用START TRANSACTION,autocommit仍然被禁用,直到您使用COMMIT或ROLLBACK结束事务为止。然后autocommit模式恢复到原来的状态。
BEGIN和BEGIN WORK被作为START TRANSACTION的别名受到支持,用于对事务进行初始化。START TRANSACTION是标准的SQL语法,并且是启动一个ad-hoc事务的推荐方法。BEGIN语句与BEGIN关键词的使用不同。BEGIN关键词可以启动一个BEGIN...END复合语句。后者不会开始一项事务。
您也可以按照如下方法开始一项事务:
START TRANSACTION WITH CONSISTENT SNAPSHOT;
WITH CONSISTENT SNAPSHOT子句用于启动一个一致的读取,用于具有此类功能的存储引擎。目前,该子句只适用于InnoDB。该子句的效果与发布一个START TRANSACTION,后面跟一个来自任何InnoDB表的SELECT的效果一样。请参见15.2.10.4节,“一致的非锁定读”。
开始一项事务会造成一个隐含的UNLOCK TABLES被执行。
为了获得最好的结果,事务应只使用由单一事务存储引擎管理的表执行。否则,会出现以下问题:
如果您使用的表来自多个事务安全型存储引擎(例如InnoDB和BDB),并且事务隔离等级不是SERIALIZABLE,则有可能当一个事务提交时,其它正在进行中的、使用同样的表的事务将只会发生由第一个事务产生的变更。也就是,用混合引擎不能保证事务的原子性,并会造成不一致。(如果混合引擎事务不经常有,则您可以根据需要使用SET TRANSACTION ISOLATION LEVEL把隔离等级设置到SERIALIZABLE。)
如果您在事务中使用非事务安全型表,则对这些表的任何变更被立刻存储,不论autocommit模式的状态如何。
如果您在更新了事务中一个事务表之后,发布一个ROLLBACK语句,则会出现一个ER_WARNING_NOT_COMPLETE_ROLLBACK警告。对事务安全型表的变更被 回滚,但是对非事务安全型表没有变更。
每个事务被存储在一个组块中的二进制日志中,在COMMIT之上。被回滚的事务不被计入日志。(例外情况:对非事务表的更改不会被 回滚。如果一个被回滚的事务包括对非事务表的更改,则整个事务使用一个在末端的ROLLBACK语句计入日志,以确保对这些表的更改进行复制。)
您可以使用SET TRANSACTION ISOLATION LEVEL更改事务的隔离等级。
回滚可以慢速运行。在用户没有明确要求时,也可以进行回滚(例如,当错误发生时)。因此,在明确地和隐含的(ROLLBACK SQL命令)回滚时,SHOW PROCESSLIST会在Stage列中显示Rolling back,用于连接。

复制代码 代码如下:

复制代码 代码如下:

您可能感兴趣的文章:

  • Mysql数据库锁定机制详细介绍
  • mysql锁表和解锁语句分享
  • MySQL行级锁、表级锁、页级锁详细介绍
  • MySQL Innodb表导致死锁日志情况分析与归纳
  • MYSQL锁表问题的解决方法
  • mysql 数据库死锁原因及解决办法
  • 一次Mysql死锁排查过程的全纪录
  • Mysql(MyISAM)的读写互斥锁问题的解决方法
  • mysql锁定单个表的方法
  • 查找MySQL线程中死锁的ID的方法
  • Mysql 数据库死锁过程分析(select for update)
  • MySQL锁机制与用法分析

mysql_query("set autocommit=0");
$list_one = $db->fetch_first("select * from prizes where id = ".$id." FOR UPDATE");
$db->query("DELETE from prizes WHERE id =".$list_one['id']);
mysql_query("commit");

START TRANSACTION, COMMIT和ROLLBACK语法

START TRANSACTION, COMMIT和ROLLBACK语法

复制代码 代码如下:

复制代码 代码如下:

使用START TRANSACTION,autocommit仍然被禁用,直到您使用COMMIT或ROLLBACK结束事务为止。然后autocommit模式恢复到原来的状态。
BEGIN和BEGIN WORK被作为START TRANSACTION的别名受到支持,用于对事务进行初始化。START TRANSACTION是标准的SQL语法,并且是启动一个ad-hoc事务的推荐方法。BEGIN语句与BEGIN关键词的使用不同。BEGIN关键词可以启动一个BEGIN...END复合语句。后者不会开始一项事务。
您也可以按照如下方法开始一项事务:
START TRANSACTION WITH CONSISTENT SNAPSHOT;
WITH CONSISTENT SNAPSHOT子句用于启动一个一致的读取,用于具有此类功能的存储引擎。目前,该子句只适用于InnoDB。该子句的效果与发布一个START TRANSACTION,后面跟一个来自任何InnoDB表的SELECT的效果一样。请参见15.2.10.4节,“一致的非锁定读”。
开始一项事务会造成一个隐含的UNLOCK TABLES被执行。
为了获得最好的结果,事务应只使用由单一事务存储引擎管理的表执行。否则,会出现以下问题:
如果您使用的表来自多个事务安全型存储引擎(例如InnoDB和BDB),并且事务隔离等级不是SERIALIZABLE,则有可能当一个事务提交时,其它正在进行中的、使用同样的表的事务将只会发生由第一个事务产生的变更。也就是,用混合引擎不能保证事务的原子性,并会造成不一致。(如果混合引擎事务不经常有,则您可以根据需要使用SET TRANSACTION ISOLATION LEVEL把隔离等级设置到SERIALIZABLE。)
如果您在事务中使用非事务安全型表,则对这些表的任何变更被立刻存储,不论autocommit模式的状态如何。
如果您在更新了事务中一个事务表之后,发布一个ROLLBACK语句,则会出现一个ER_WARNING_NOT_COMPLETE_ROLLBACK警告。对事务安全型表的变更被 回滚,但是对非事务安全型表没有变更。
每个事务被存储在一个组块中的二进制日志中,在COMMIT之上。被回滚的事务不被计入日志。(例外情况:对非事务表的更改不会被 回滚。如果一个被回滚的事务包括对非事务表的更改,则整个事务使用一个在末端的ROLLBACK语句计入日志,以确保对这些表的更改进行复制。)
您可以使用SET TRANSACTION ISOLATION LEVEL更改事务的隔离等级。
回滚可以慢速运行。在用户没有明确要求时,也可以进行回滚(例如,当错误发生时)。因此,在明确地和隐含的(ROLLBACK SQL命令)回滚时,SHOW PROCESSLIST会在Stage列中显示Rolling back,用于连接。

复制代码 代码如下:

复制代码 代码如下:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET [email protected] WHERE type=1;
COMMIT;

本文由分分快三计划发布,转载请注明来源

关键词: 分分快三计划