mysql autocommit for update_数据库锁 行锁 表锁 autocommit for update

http://www.qttc.net/201208175.html

==========================autocommit==========================

MySQL默认操作模式就是autocommit自动提交模式。这就表示除非显式地开始一个事务，否则每个查询都被当做一个单独的事务自动执行。我们可以通过设置autocommit的值改变是否是自动提交autocommit模式。

通过以下命令可以查看当前autocommit模式

从查询结果中，我们发现Value的值是ON，表示autocommit开启。我们可以通过以下SQL语句改变这个模式

mysql>set autocommit = 0;

值0和OFF都是一样的，当然，1也就表示ON。通过以上设置autocommit=0，则用户将一直处于某个事务中，直到执行一条commit提交或rollback语句才会结束当前事务重新开始一个新的事务。

举个例子：

张三给李四转账500元。那么在数据库中应该是以下操作：

1，先查询张三的账户余额是否足够

2，张三的账户上减去500元

3，李四的账户上加上500元

以上三个步骤就可以放在一个事务中执行提交，要么全部执行要么全部不执行，如果一切都OK就commit提交永久性更改数据；如果出错则rollback回滚到更改前的状态。利用事务处理就不会出现张三的钱少了李四的账户却没有增加500元或者张三的钱没有减去李四的账户却加了500元。

MySQL默认的存储引擎是MyISAM，MyISAM存储引擎不支持事务处理，所以改变autocommit没有什么作用。但不会报错，所以要使用事务处理的童鞋一定要确定你所操作的表支持事务处理的，如InnoDB。

如果不知道表的存储引擎可以通过查看建表语句查看建表的时候有没有指定事务类型的存储引擎，如果没有指定存储引擎默认则是MyISAM不支持事务的存储引擎。

mysql> show create table ffmpeg_ins;

CREATE TABLE `ffmpeg_ins` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`agent_node_id` int(10) unsigned NOT NULL,

`status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '0 for stop, 1 for running, 2 for idle, 3 for delete, 4 for change, 5 for error',

`name` varchar(128) CHARACTER SET utf8 NOT NULL,

`inputSrc` varchar(255) CHARACTER SET utf8 NOT NULL,

`outputSrc` varchar(512) CHARACTER SET utf8 NOT NULL,

`btid` varchar(128) DEFAULT NULL,

`liveid` varchar(128) DEFAULT NULL,

`ts` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),

KEY `agent_channel_name` (`agent_node_id`,`name`) USING BTREE

)ENGINE=MyISAM AUTO_INCREMENT=23854 DEFAULT CHARSET=latin1

当然，事务处理是为了保障表数据原子性、一致性、隔离性、持久性。这些都是要消耗系统资源的，要谨慎选择。

===========================SELECT语句中的for update的用法(锁的运用)==========================

http://shenzhenchufa.blog.51cto.com/730213/153818

SELECT 语句中的 for update (以及 lock in share mode) 的用法试验。

作用:

当一方锁定后,另一方的操作(update),会等到前一方的commit后才执行.

这个语句限制在事务表的其他连接上进行UPDATE或者DELETE操作。

连接1命名为A。

连接2命名为B。

有几个先决条件：

1、当autocommit 系统变量值为off 或者为0 的时候起作用。

2、并且表的引擎是支持事务的，比如INNODB。

3、也可以不管autocommit的执，手动在事务里执行操作，这个时候可能要begin或者start transaction语句了。

4、不要在锁定事务在innodb_lock_wait_timeout规定以外的时间完成。

此时在A连接执行：

mysql> use t_girl;

Database changed

mysql> create table t (id int not null auto_increment primary key, cstr char(40) not null) engine innodb;

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;

+------------------+

| Tables_in_t_girl |

+------------------+

| t |

+------------------+

1 row in set (0.00 sec)

mysql> insert into t(cstr) values('This is huahua'),('This is not huahua'),('Huahua is a dog,not a person\'s name');

Query OK, 3 rows affected (0.02 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from t;

+----+-------------------------------------+

| id | cstr |

+----+-------------------------------------+

| 1 | This is huahua |

| 2 | This is not huahua |

| 3 | Huahua is a dog,not a person's name |

+----+-------------------------------------+

3 rows in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id = 2 for update;

+----+--------------------+

| id | cstr |

+----+--------------------+

| 2 | This is not huahua |

+----+--------------------+

1 row in set (0.00 sec)

过了一段时间后执行：

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

此时在B连接执行：

mysql> update t set cstr = 'I know huahua is a person' where id =2;

Query OK, 1 row affected, 0 warning (48.91 sec)

Rows matched: 1 Changed: 1 Warnings: 0

这个UPDATE会一直等待A连接执行commit或者rollback才会生效。

mysql> select * from t where id =2;

+----+-----------------------------------------+

| id | cstr |

+----+-----------------------------------------+

| 2 | I know huahua is a person |

+----+-----------------------------------------+

1 row in set (0.00 sec)

此时在A连接上执行：

mysql> select * from t where id = 2;

+----+-----------------------------------------+

| id | cstr |

+----+-----------------------------------------+

| 2 | I know huahua is a person |

+----+-----------------------------------------+

1 row in set (0.00 sec)

LOCK IN SHARE MODE 和 FOR UPDATE 是一样的原理。

(2)

由于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;

例2: (无主键，table lock)

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

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

SELECT * FROM products WHEREid<>'3' FOR UPDATE;

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

SELECT * FROM products WHEREid LIKE '3' FOR UPDATE;

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

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