MySql 加锁问题

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 ;

存储过程加锁后SLEEP

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";

乱七八糟