For some reason, don't use the concurrency, but by the mysql database table instead of queue,

Specific statements is

To execute SQL='SELECT * FROM resource_trading_center_yeji where is_deleted is null and project_source="' + center_name + 'for update'
After executing SQL='update resource_trading_center_yeji set is_deleted=1 where url=% s. '
No commit

Lock waiting timeout for 1 second

Scene: a bunch of instance traversal plugins, plugs have their own center_name, traversal process may be repeated

Project_source and is_deleted index, arguably, will only add a row lock and even perform 'update resource_trading_center_yeji set is_deleted=1 where url=% s. 'when adding a table lock (url no index), but more than one session did not cross waiting for locks,

Don't know why deadlock, it should wait for the lock timeout,
Thank you for your answer

CodePudding user response:

I think a scene:
A:
A session in the execution, had not performed, so is the table locks;
B at the same time, the session also launched A SQL, due to the session is A table lock, so for the session B for update statement is blocked, the second SQL execution;
At the same time, the session also launched A SQL, C because the session is A table lock, so for the session C for update statement is blocked, the second SQL execution;

Suppose center_name different session B and C;

B:
Session A performed, release all locks;
At the same time:
The corresponding row lock session B obtained center_name1;
Session C to obtain the corresponding row locking center_name2;

When session B want to perform the update, need access to table locks, but blocked by session C row locks, waiting for the session C release row locks;
When the session C want to perform the update, need access to table locks, but blocked by session B row locks, waiting for the session B release row locks;

Wait for each other, more than 1 second, deadlock rollback,

CodePudding user response:

reference 1st floor by the stars of a cold response:

I thought of a scene:
A:
A session in the execution, had not performed, so is the table locks;
B at the same time, the session also launched A SQL, due to the session is A table lock, so for the session B for update statement is blocked, the second SQL execution;
At the same time, the session also launched A SQL, C because the session is A table lock, so for the session C for update statement is blocked, the second SQL execution;

Suppose center_name different session B and C;

B:
Session A performed, release all locks;
At the same time:
The corresponding row lock session B obtained center_name1;
Session C to obtain the corresponding row locking center_name2;

When session B want to perform the update, need access to table locks, but blocked by session C row locks, waiting for the session C release row locks;
When the session C want to perform the update, need access to table locks, but blocked by session B row locks, waiting for the session B release row locks;

Wait for each other, more than 1 second, deadlock rollback,

Thank you, this possibility is, but I only opened two session test, really don't know how a deadlock

CodePudding user response:

refer to the second floor weixin_45069059 response:

Quote: refer to 1st floor by the stars of a cold response:

I think a scene:
A:
A session in the execution, had not performed, so is the table locks;
B at the same time, the session also launched A SQL, due to the session is A table lock, so for the session B for update statement is blocked, the second SQL execution;
At the same time, the session also launched A SQL, C because the session is A table lock, so for the session C for update statement is blocked, the second SQL execution;

Suppose center_name different session B and C;

B:
Session A performed, release all locks;
At the same time:
The corresponding row lock session B obtained center_name1;
Session C to obtain the corresponding row locking center_name2;

When session B want to perform the update, need access to table locks, but blocked by session C row locks, waiting for the session C release row locks;
When the session C want to perform the update, need access to table locks, but blocked by session B row locks, waiting for the session B release row locks;

Wait for each other, more than 1 second, deadlock rollback,

Thank you, this possibility is, but I only opened two session test, really don't know how a deadlock

Two sessions test, that is, two sessions won the row locks at the same time, each table lock block each other

CodePudding user response:

Log display one session should request the primary index, lock, how to solve the


The following is a log:
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
LATEST DETECTED DEADLOCK
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
The 2019-07-17 13:47:07 0 x404
* * * (1) the TRANSACTION:
TRANSACTION 1139555, ACTIVE 0 SEC fetching rows
Mysql tables in the use of 3, 3 locked
The LOCK WAIT 357 LOCK struct (s), the heap size of 41168, 3 row LOCK (s)
MySQL thread id 79, OS thread handle 15284, query id localhost: 118858:1 root Sending data
SELECT * FROM resource_trading_center_yeji where is_deleted is null and project_source="Mosaic" for update
* * * (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD the 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 the lock, heap no 144 PHYSICAL Record: n_fields 2; Compact format. The info 32 bits
Zero: SQL NULL;
1: len 8; Hex 800000000001 d52d; Asc -;;

* * * (2) the TRANSACTION:
TRANSACTION 1139552, ACTIVE 13 SEC fetching rows, thread declared inside the InnoDB 3032
Mysql tables in use 1, locked 1
2923 the lock struct (s), the heap size of 286928, 3003 row lock (s), the undo log entries 53
MySQL thread id 80, OS thread handle 1028, query id localhost: 118859:1 root updating
Update resource_trading_center_yeji set is_deleted=1 where url='http://ggzy.yn.gov.cn/jyxx/jsgcZbjggsDetail? Guid=f33 b87b9fcb - 1-46 db - b003-542792366 d4b & amp; IsOther=false '
Doesn * * * (2) THE LOCK (S) :
RECORD the 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 the lock, heap no 3 PHYSICAL Record: n_fields 2; Compact format. The info bits (0
Zero: SQL NULL;
1: len 8; Hex 800000000001 d73c; Asc & lt;;;

Record the lock, heap no 670 PHYSICAL Record: n_fields 2; Compact format. The info bits (0
Zero: SQL NULL;
1: len 8; Hex 800000000001 d73b; Asc;;;

* * * (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD the 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 the lock, heap no 63 PHYSICAL Record: n_fields 7; Compact format. The info bits (0
Zero: len 8; Hex 8000000000012 d08; Asc -;;
1: len (6); Hex 00000010 b676; Asc v;;
2: len 7; Hex f4000001cc0110; nullnullnullnullnullnullnullnullnullnullnullnull