Bug #20143 Timeout while aquiring lock on table with foreign key
Submitted: 30 May 2006 10:18 Modified: 1 Jun 2006 11:02
Reporter: Krzysztof Heim Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0.21 community nt OS:Windows (Windows XP SP2)
Assigned to: Heikki Tuuri CPU Architecture:Any

[30 May 2006 10:18] Krzysztof Heim 
Description:
There are 2 tables A, B

A.bID is foreign key with restriction on delete and update to B.ID

Sequence:
1. open Connection 1
2. Connection 1: begin tran
3. Connection 1: delete record from A -> success
4. open Connection 2
5. Connection 2: delete record from B referenced by A.BId by FK -> FAILED
6. Connection 2: dispose
7. Connection 1: commit
8. Connection 1: dispose

Step 5 is failing with the error timeout while aquiring lock
I have tested it even using same connection (only Connection 1) and results are the same

I'm using .NET 2.0 with .NET connector V1.0.7
I'm not changing std. isolation level = repetable reads.
No additional indexe's on the tables except ones created by PK and FK

PS. If I remove FK problem does not exist.

How to repeat:
See in description.

Suggested fix:
Well, I don't see any reason why step 5 should not be a success.
[30 May 2006 10:34] Krzysztof Heim 
I forgot to mention that all tables are InnoDB,
[30 May 2006 15:33] Heikki Tuuri 
Krzys,

but what if connection 1 decides to roll back his transaction? You will end up with an orphan row in table A!

Foreign key checks use locking reads because of this reason, and you may end up with lock waits.

Regards,

Heikki
[31 May 2006 10:22] Krzysztof Heim 
Yes but as I said I did tryed mentioned sequence on 1 connection inside 1 transaction - result was the same ...
[31 May 2006 10:38] Heikki Tuuri 
Krzys,

with just one connection there should not be any lock waits at all!

Please show the screen capture of the mysql session where you observe that.

Regards,

Heikki
[1 Jun 2006 11:02] Krzysztof Heim 
I'm quite new in mysql - what exacly you want me to do?
I have connection list from SQL administrator with I connaction staying on this delete in status updating,

:)