MySQL Forums
Forum List  »  MyISAM

LOCK TABLES causing deadlock
Posted by: golharam
Date: September 15, 2006 01:50PM

I'm using mySQL 5 on RHEL 4 AS. I have about 20 nodes doing a lock table write followed by select and possible insert, then a unlock table. If they aren't doing the select insert, then they are doing an UPDATE. If 1 thread is doing the select insert and another thread is doing an update, I get a deadlock. Heres my code:

1. SELECT gi_id from t;

2. For each gi_id in resultset:
LOCK TABLES inprogress WRITE
SELECT * from inprogress where gi=gi_id

If no rows are returned,
INSERT INTO inprogress (gi, status) values (gi_id, 0)
UNLOCK TABLES

If rows are returned,
UNLOCK TABLES and continue with the next gi_id.

3. Do some processing regarding gi_id then
UPDATE inprogress set status=1 where gi=gi_id

If 1 thread is at step 3 and another thread is at step 1, I am getting a deadlock. I tried putting a LOCK TABLES/UNLOCK TABLES on Step 3, but that didn't seem to help. How can I fix this? I thought if a thread had a LOCK, then the others would block until it was released.

Options: ReplyQuote


Subject
Views
Written By
Posted
LOCK TABLES causing deadlock
2806
September 15, 2006 01:50PM
1557
September 16, 2006 10:13AM


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.