MySQL Forums
Forum List  »  InnoDB

Concurrent INSERT/DELETE
Posted by: Jean-Philippe Signorino
Date: April 24, 2013 03:56AM

Hi,

I'm trying to solve concurrent locking on my application. I identified a scenario and I don't know how t solve it.

I have a single table T with a column 'id'.
Transaction are in READ COMMITED mode.

First, I start a transaction where I execute:
INSERT INTO T (`id`) VALUES (1);

Then I start a second transaction where i execute :
delete from T where id in (3,4,5,6);


The last query is blocked par locks . If I execute "delete from T where id=3" the query is not locked.


SHOW ENGINE INNODB STATUS give me :

------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3776 n bits 72 index `PRIMARY` of table T trx id 171E2D lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0


Is there a way to prevent query to be locked ?



Edited 1 time(s). Last edit at 04/24/2013 03:57AM by Jean-Philippe Signorino.

Options: ReplyQuote


Subject
Views
Written By
Posted
Concurrent INSERT/DELETE
3811
April 24, 2013 03:56AM
1258
April 25, 2013 09:09AM
1195
April 30, 2013 07:57AM
1009
May 03, 2013 01:21PM


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.