由于某些原因,没有使用并发,而是通过mysql数据库的表来代替队列。具体语句是先执行sql = 'SELECT * FROM resource_trading_center_yeji where is_deleted is null and project_source = "'+center_name+'" for update'
后执行sql = 'update resource_trading_center_yeji set is_deleted=1 where url=%s;'
中间没有commit锁的等待超时设置为了1秒场景是:一堆实例遍历plugins,plugs有各自的center_name,遍历过程中可能重复project_source和is_deleted都有索引,按理说只会添加行锁,即便执行'update resource_trading_center_yeji set is_deleted=1 where url=%s;'时添加了表锁(url没有索引),但多个session并没有交叉等待锁。不清楚为什么死锁,照理说应该等锁超时才对。
谢谢解答
后执行sql = 'update resource_trading_center_yeji set is_deleted=1 where url=%s;'
中间没有commit锁的等待超时设置为了1秒场景是:一堆实例遍历plugins,plugs有各自的center_name,遍历过程中可能重复project_source和is_deleted都有索引,按理说只会添加行锁,即便执行'update resource_trading_center_yeji set is_deleted=1 where url=%s;'时添加了表锁(url没有索引),但多个session并没有交叉等待锁。不清楚为什么死锁,照理说应该等锁超时才对。
谢谢解答
解决方案 »
- 打开一个php网页, 怎么监测打开这个网页都执行了哪些sql语句以及所用的时间?
- mysql中怎样截取字符串中数值
- 求教如何找出没有使用索引的语句
- Parameter index out of range (1 > number of parameters, which is 0).求解
- 求一个MYSQL 时间操作函数
- 同一个字段的2次排序问题~
- 关于替换一个字段中的字符串的问题
- 如何让mysql结束一个对五千万条记录的查询
- MySQL数据删了一条数据,可不可以恢复啊?
- MYSQL存储过程对中文的支持,散分
- 更新数据库archives表时出错?求帮助!!!
- 提示删除成功,也查询不到,但是重新登录后,删除的数据依然存在
时间A:
会话A在执行,还没执行完,那么就是表锁;
同时,会话B也发起了SQL,由于会话A是表锁,那么对于会话B的for update语句被堵塞,第二条SQL等待执行;
同时,会话C也发起了SQL,由于会话A是表锁,那么对于会话C的for update语句被堵塞,第二条SQL等待执行;假设会话B和会话C的center_name不同;时间B:
会话A执行完,释放所有锁;
同一时间:
会话B获取了center_name1对应的行锁;
会话C获取了center_name2对应的行锁;当会话B想要执行update,需要获取表锁,但是被会话C的行锁阻塞,等待会话C释放行锁;
当会话C想要执行update,需要获取表锁,但是被会话B的行锁阻塞,等待会话B释放行锁;相互等待,超过1秒,死锁回滚。
以下是日志:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-07-17 13:47:07 0x404
*** (1) TRANSACTION:
TRANSACTION 1139555, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 357 lock struct(s), heap size 41168, 3 row lock(s)
MySQL thread id 79, OS thread handle 15284, query id 118858 localhost ::1 root Sending data
SELECT * FROM resource_trading_center_yeji where is_deleted is null and project_source = "马赛克" for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 227 page no 988 n bits 1192 index index4 of table `zhixing`.`resource_trading_center_yeji` trx id 1139555 lock_mode X locks rec but not gap waiting
Record lock, heap no 144 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: SQL NULL;
1: len 8; hex 800000000001d52d; asc -;;*** (2) TRANSACTION:
TRANSACTION 1139552, ACTIVE 13 sec fetching rows, thread declared inside InnoDB 3032
mysql tables in use 1, locked 1
2923 lock struct(s), heap size 286928, 3003 row lock(s), undo log entries 53
MySQL thread id 80, OS thread handle 1028, query id 118859 localhost ::1 root updating
update resource_trading_center_yeji set is_deleted=1 where url='http://ggzy.yn.gov.cn/jyxx/jsgcZbjggsDetail?guid=b87b9fcb-1f33-46db-b003-542792366d4b&isOther=false'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 227 page no 988 n bits 1192 index index4 of table `zhixing`.`resource_trading_center_yeji` trx id 1139552 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: SQL NULL;
1: len 8; hex 800000000001d73c; asc <;;Record lock, heap no 670 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: SQL NULL;
1: len 8; hex 800000000001d73b; asc ;;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 227 page no 2630 n bits 136 index PRIMARY of table `zhixing`.`resource_trading_center_yeji` trx id 1139552 lock_mode X locks rec but not gap waiting
Record lock, heap no 63 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 8; hex 8000000000012d08; asc - ;;
1: len 6; hex 00000010b676; asc v;;
2: len 7; hex f4000001cc0110; asc ;;
3: len 30; hex e4ba91e58d97e79c81e585ace585b1e8b584e6ba90e4baa4e69893e4b8ad; asc ; (total 33 bytes);
4: len 30; hex e7baa2e6b2b3e5b79ee4b8aae697a7e5b88232303139e5b9b4e59f8ee995; asc 2019 ; (total 116 bytes);
5: len 30; hex 687474703a2f2f67677a792e796e2e676f762e636e2f6a7978782f6a7367; asc http://ggzy.yn.gov.cn/jyxx/jsg; (total 99 bytes);
6: len 1; hex 81; asc ;;*** WE ROLL BACK TRANSACTION (1)