MySQL Forums
Forum List  »  InnoDB

why delete will block by select?
Posted by: h h
Date: December 15, 2014 07:53PM

hi ,all:
i am very confused about this deadlock,here is the detail info,why the delete will be blocked by select ?


*** (1) TRANSACTION:
TRANSACTION 657EBD98, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1248, 12 row lock(s)
MySQL thread id 1563104, OS thread handle 0x4d8bb940, query id 304472500 192.168.2.169 opr updating
delete from phoenix_adspace_execution_byhour_oftime where createdTime >= str_to_date('2014-12-15 16', '%Y-%m-%d %H:%i:%s') and createdTime < str_to_date('2014-12-15 16','%Y-%m-%d %H:%i:%s') and ( adspaceId = 0 or adspaceId = 9681 or adspaceId = 9887......

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21133 page no 14 n bits 192 index "PRIMARY" of table "adxdb"."phoenix_adspace_execution_byhour_oftime" trx id 657EBD98 lock_mode X waiting
Record lock, heap no 84 PHYSICAL RECORD: n_fields 19; compact format; info bits 0


*** (2) TRANSACTION:
TRANSACTION 657EBD81, ACTIVE 0 sec starting index read, thread declared inside InnoDB 94
mysql tables in use 2, locked 2
8 lock struct(s), heap size 1248, 215 row lock(s)
MySQL thread id 1563101, OS thread handle 0x4e4eb940, query id 304472477 192.168.2.169 opr preparing
delete from phoenix_adspace_execution_byhour_ofregion where adspace_time_id in (select id from phoenix_adspace_execution_byhour_oftime where createdTime >= str_to_date('2014-12-15 16', '%Y-%m-%d %H:%i:%s') and createdTime < str_to_date('2014-12-15 16', '%Y-%m-%d %H:%i:%s') and ( adspaceId = 0 or adspaceId = 6841 or adspaceId = 8133
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 21133 page no 14 n bits 192 index "PRIMARY" of table "adxdb"."phoenix_adspace_execution_byhour_oftime" trx id 657EBD81 lock mode S locks rec but not gap
Record lock, heap no 84 PHYSICAL RECORD: n_fields 19; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21133 page no 14 n bits 192 index "PRIMARY" of table "adxdb"."phoenix_adspace_execution_byhour_oftime" trx id 657EBD81 lock mode S locks rec but not gap waiting
Record lock, heap no 72 PHYSICAL RECORD: n_fields 19; compact format; info bits 0

Options: ReplyQuote


Subject
Views
Written By
Posted
why delete will block by select?
2288
h h
December 15, 2014 07:53PM
1142
December 16, 2014 04:32PM
1169
h h
December 16, 2014 09:37PM
1005
December 17, 2014 06:38PM
1023
h h
December 17, 2014 09:29PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.