766
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)
最後更新：2017-04-02 16:48:10