CODEWITHSUNDEEP
mysql
Rahul
Rahul

Reputation: 870

Is it possible to lock a table for insert of selected values

I want to lock a table so that no other thread can insert any rows with (e.g.) user_id = 5. while other thread is working on those set of rows. Though I can lock the full table but that seems too much and would reduce concurrency.

My thread does following and there are many of them running at a time. 

Start Transaction
1) insert row with user_id=val
2) if (count (*) >= 2) where user_id = val and some other conditions then do "something"
COMMIT

Now if 2 threads run in parallel then it is possible that both of them get count(*) = 1 but when both of them commits the total row count is 2 which should have done that "something"

What I thought was that I need to lock the rows with user_id=val in each transaction so that nobody can even insert for user_id = val until my transaction completes.

Is there any way to achieve this using MySQL and Hibernate.

Upvotes: 0

Views: 180

Answers (2)

Naktibalda
Naktibalda

Reputation: 14102

Use locking reads.

SELECT COUNT(*) FROM table WHERE user_id = val FOR UPDATE.

will lock the matching rows until the end of transaction.

http://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html

Upvotes: 1

Arth
Arth

Reputation: 13110

Consider simulating record locks with GET_LOCK();

Choose a name specific to the rows you want locking. e.g. 'xxx_user_id_y'. Where 'xxx' is a string specific to the procedure or to inserts on this table and 'y' is the user id.

Call SELECT GET_LOCK('xxx_user_id_y',30) to lock the name 'xxx_user_id_y'.. it will return 1 and set the lock if the name becomes available, or return 0 if the lock is not available after 30 seconds (the second parameter is the timeout).

Use SELECT RELEASE_LOCK('xxx_user_id_y') when you are finished.

Be aware; You will have to use the same names in each transaction that you want to wait and calling GET_LOCK() again in a transaction will release the previously set lock.

GET_LOCK() docs

Upvotes: 0

Related Questions