MySQL read lock.. 利用 select .. for update 解决.

1. 创建表 schedual , 用于登记在某段时间内某个某用户预约信息, 中具有 start, end 列.

用户如需进行预约, 则需登记 开始与结束时间, 另外, 为避免时间段上的重复使用, 我创建存储过程.

create table schedual ( id int, name varchar(10), start datetime, end daytime ) engine innodb;

 

2. 创建存储过程

delimiter //
create procedure p1( in a datetime )
begin

        declare b int;
        declare c datetime;
        select date_add(a, interval '00:30:00' hour_second) into c from dual;

        select count(*) into b from schedual where start < a and end > a;

        if b > 0
        then
                select 'abc' from dual;
        else
                insert into schedual values ( 1, 'root', a, c);
        end if;

end
//

delimiter ;

 

效果.  只能够在 start -- end 之间时间段为空才允许插入数据, 防止数据发生重复

3. 创建 shell 命令调用存储过程. 利用 call.sh 调用 a.sql 执行 sql 脚本

 

[root@station86 test]# cat call.sh
#!/bin/bash
mysql -u tt -p123  -e "source /tmp/test/a.sql"

[root@station86 test]# cat a.sql
use new;
call p1(now());

[root@station86 test]# ./call.sh
ERROR 1205 (HY000) at line 2 in file: '/tmp/test/a.sql': Lock wait timeout exceeded; try restarting transaction

mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------------------------------------------------------------------------------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info                                                                                                 |
+----+-----------------+-----------+------+---------+------+------------------------+------------------------------------------------------------------------------------------------------+
|  1 | event_scheduler | localhost | NULL | Daemon  | 1769 | Waiting on empty queue | NULL                                                                                                 |
| 21 | root            | localhost | new  | Query   |    0 | NULL                   | show processlist                                                                                     |
| 30 | root            | localhost | new  | Query   |   23 | update                 | insert into schedual values ( 1, 'root',  NAME_CONST('a',_latin1'2012-08-15 00:04:17' COLLATE 'latin |
+----+-----------------+-----------+------+---------+------+------------------------+------------------------------------------------------------------------------------------------------+

执行过程中出现死锁, 无法在外部 shell 中执行, 而当前 存储过程能够在 MYSQL 中直接运行.

如 mysql> source /tmp/test/a.sql ;  返回成功结果

 

分析:

重新执行存储过程, 发现数据能够插入数据库.
证明当前 mysql 存储读锁, 以防止同一时间内大量用户同时进行数据更新. 因此这个场景中只能够使用 select .. for update.

 

 

尝试修改存储过程
delimiter //
create procedure p1( in a datetime )
begin

  declare b int;
  declare c datetime;
  select date_add(a, interval '00:30:00' hour_second) into c from dual;

  select count(*) into b from schedual where start < a and end > a for update;

        set autocommit=0;
  if b > 0
  then
    select 'abc' from dual;
  else
    insert into schedual values ( 1, 'root', a, c);
  end if;

        commit;
end
//

delimiter ;

 

重新在外部调用shell 脚本, 数据能够成功插入数据库, 问题解决.

mysql> select * from new.schedual;
+------+------+---------------------+---------------------+
| id   | name | start               | end                 |
+------+------+---------------------+---------------------+
|    1 | root | 2012-08-15 00:22:41 | 2012-08-15 00:52:41 |
+------+------+---------------------+---------------------+
1 row in set (0.00 sec)