mysql select lock_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)