1. 创建表 schedual , 用于登记在某段时间内某个某用户预约信息, 中具有 start, end 列.用户如需进行预约, 则需登记 开始与结束时间, 另外, 为避免时间段上的重复使用, 我创建存储过程.create table schedual (
用户如需进行预约, 则需登记 开始与结束时间, 另外, 为避免时间段上的重复使用, 我创建存储过程.
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)
声明： 此文观点不代表本站立场；转载须要保留原文链接；版权疑问请联系我们。
