使用select .. for update 解决.MySQL read lock问题

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)

声明： 此文观点不代表本站立场；转载须要保留原文链接；版权疑问请联系我们。