1、设置非自动提交 set autocommit=0; 这时候 for update才会起作用
2、一般用法 set autocommit=0; for update(加锁) ; commit/rollback; set autocommit=1;
首先看一下,set autocommit=0 后,执行哪些语句会自动加锁,加的是什么锁?
测试环境:5.6.16 innnoDB引擎 非自动提交方式(即 set autocommit=0;)
测试过程:执行 select * from w_help ;这个语句,并不会加锁 其他命令窗口一样可以增删改查;测试代码如下
DELIMITER $$ DROP PROCEDURE IF EXISTS `test_release`.`test_Lock`$$ CREATE DEFINER=`encysys48`@`%` PROCEDURE `test_Lock`(IN v_id VARCHAR(20),IN v_flag VARCHAR(10)) BEGIN set autocommit=0; select t.flag from test_lock t where t.hf_serial = v_id for update; /*update test_lock t set t.flag = v_flag where t.hf_serial = v_id;*/ -- insert into w_help(char_content) values ('aaaaaaaaaaaaaaaaaaaa') for update; -- delete from w_help where w_help.id = '451'; select * from w_help ; select sleep(v_flag); commit; set autocommit=1; END$$ DELIMITER ;
call test_Lock(1,10) -- 调用存储过程
在释放锁之前,下面的语句都是可以执行的,说明表并没有加锁
select * from w_help; insert w_help(char_content) values("abcd"); update w_help t set t.char_content = '12345' where t.id = "458"; delete from w_help -- where w_help.id = '458'
执行 insert into w_help(char_content) values ('aaaaaaaaaaaaaaaaaaaa') ;这个语句,只会给一行加锁,(值得一提的是 这个语句后面不能跟 for update 否则会报错,不知道是我写的语法问题,还是根本就不能加) insert into w_help(char_content) values ('aaaaaaaaaaaaaaaaaaaa') for update; 就会报错;
其他事务语句执行情况如下:
select * from w_help; -- 可以执行 insert w_help(char_content) values("abcd"); -- 可以执行 update w_help t set t.char_content = '12345' where t.id = "464"; -- 可以执行 delete from w_help where w_help.id = '464'; -- 可以执行 delete from w_help -- 不能执行会等待锁释放后执行
执行 update w_help t
set t.char_content = v_flag
where t.id ="476"; 这个语句,只会给一行加锁,(前提是指明了主键)
其他事务语句执行情况如下:
select * from w_help; -- 可以执行 insert w_help(char_content) values("abcd"); -- 可以执行 update w_help t set t.char_content = '12345' where t.id = "479"; -- 可以执行 delete from w_help where w_help.id = '478'; -- 可以执行 delete from w_help -- 不能执行
如果不指明主键 则会锁住整个表,其他事务语句执行情况如下:
select * from w_help; -- 可以执行 insert w_help(char_content) values("abcd"); -- 可以执行并且不会被update update w_help t set t.char_content = '12345' where t.id = "489"; -- 不能执行 等待锁被释放后继续执行 delete from w_help where w_help.id = '489'; -- 不能执行 等待锁被释放后继续执行 delete from w_help -- 不能执行 等待锁被释放后继续执行
执行 delete from w_help where w_help.id = '490';这个语句 产生行级锁,
其他事务语句执行情况如下:
select * from w_help; -- 可以执行 insert w_help(char_content) values("abcd"); -- 可以执行 update w_help t set t.char_content = '12345' where t.id = "490"; -- 被锁定的唯一一行 不能执行 delete from w_help where w_help.id = '488'; -- 不是被锁定的那一行,可以执行 delete from w_help -- 不能执行
还有一个奇葩的问题 select * from w_help for update; 并不能限制其他线程读取数据;可以限制其他线程加锁(即其他线程便不能加锁了)
这样的的话怎么防止读脏数据就是个问题了;不过我是在两个连接中测试的,即一个连接执行此语句,另一个连接仍然可以读数据,但是不可以修改!
所以,如果修改数据,select 时一定要 for update,否则就会有致命错误!
最后附上测试用的乱七八糟的代码,不值得看,只是做个备份而已,说不定哪时会用到;
DELIMITER $$ DROP PROCEDURE IF EXISTS `test_release`.`test_Lock`$$ CREATE DEFINER=`encysys48`@`%` PROCEDURE `test_Lock`(IN v_id VARCHAR(20),IN v_flag VARCHAR(10)) BEGIN set autocommit=0; select t.flag from test_lock t where t.hf_serial = v_id for update; /*update w_help t set t.char_content = v_flag ; -- where t.id ="476"; -- insert into w_help(char_content) values ('aaaaaaaaaaaaaaaaaaaa') ;*/ delete from w_help where w_help.id = '490'; -- select * from w_help ; select sleep(v_flag); commit; set autocommit=1; END$$ DELIMITER ; -- select * from w_help; -- 可以执行 insert w_help(char_content) values("abcd"); -- 可以执行 update w_help t set t.char_content = '12345' where t.id = "490"; -- 被锁定的唯一一行 不能执行 delete from w_help where w_help.id = '488'; -- 不是同一行,可以执行 delete from w_help -- 不能执行 update w_help t set t.char_content = "666" where t.id = "489";