Select for update query: Lock wait timeout exceeded error

Question

To avoid race conditions, I need to use select for update functionality while querying database so that it locks the row until the end of the transaction. Since select_for_update query is not present in Django 1.3, I have done a workaround it by using a class method which returns query sets by doing a raw sql query.

#models.py class AccountDetails(models.Model): user = models.OneToOneField(User) amount = models.IntegerField(max_length=15,null=True,blank=True) @classmethod def get_locked_for_update(cls,userid): accounts = cls.objects.raw("SELECT * FROM b2b_accountdetails WHERE user_id ="+str(userid)+" FOR UPDATE") return accounts[0]

This is how it's used in views.

account = AccountDetails.get_locked_for_update(userid) account.amount = account.amount - fare account.save()

On the last line I'm getting this error: OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')

In dbshell, after running the save() line:

mysql> SHOW FULL PROCESSLIST; +-----+------+-----------+-----------+---------+------+----------+-----------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+------+-----------+-----------+---------+------+----------+-------------------------- ---------------------------------------------------------------------------+ | 51 | root | localhost | dbname | Query | 0 | NULL | SHOW FULL PROCESSLIST | | 767 | root | localhost | dbname | Sleep | 59 | | NULL | | 768 | root | localhost | dbname | Query | 49 | Updating | UPDATE `b2b_accountdetails` SET `user_id` = 1, `amount` = 68906 WHERE `appname_accountdetails`.`id` = 1 | +-----+------+-----------+-----------+---------+------+----------+-------------------------- ---------------------------------------------------------------------------+

According to my understanding the lock should be released on the first data-altering query like update, delete etc.

But the save() statement is getting blocked and is kept on wait. Any idea why this is happening? What I think is when I'm calling account.save() it's not picking up the previous transaction started by select for update query.

Am I missing something obvious? Kindly help.

Are you using TransactionMiddleware? Does this problem occur in the context of an HTTP request? — Antonis Christofides
– Antonis Christofides, Commented Jul 18, 2013 at 9:48
@AntonisChristofides No I'm not using TransactionMiddleware. Yes it occurs in the context of an HTTP request, but not only in the context of HTTP request. The same error comes when I run the code from django shell. — Sudipta
– Sudipta, Commented Jul 18, 2013 at 9:55

Antonis Christofides · Accepted Answer · 2013-07-19 09:41:45Z

1

Leaving Django to its default autocommit-like behaviour for such an operation can easily lead to several kinds of errors (not locking the database at all could easily be another outcome); the details probably depend on the RDBMS and/or Django database driver for that particular RDBMS. It would be better to use @commit_on_success or @commit_manually or TransactionMiddleware.

edited Jul 19, 2013 at 9:41

answered Jul 18, 2013 at 10:31

Antonis Christofides

7,0483 gold badges49 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sudipta Over a year ago

I tried both methods, but it's not working. Please see my updated question. When I do account.save() a new thread is created. Hence it is blocked, as the previous thread has already locked the row.

Antonis Christofides Over a year ago

Just to make certain: You tried @commit_manually to decorate the function that contains account.save()?

Sudipta Over a year ago

Yes I moved the part from select query to save to another function and applied the decorator on that function only, not the view.

user710907 · Accepted Answer · 2013-07-18 10:50:40Z

0

I think some other thread is holding a record lock on some record for too long, and your thread is being timed out this a problem specific to MYSQL which does not support nowait.

You can set higher value for innodb_lock_wait_timeout and restart mysql

answered Jul 18, 2013 at 10:50

user710907

7725 silver badges12 bronze badges

1 Comment

Sudipta Over a year ago

No I think I can safely rule out the possibility of any other thread holding a record. Because even when I kill all threads shown in SHOW FULL PROCESSLIST; this problem occurs. However, this debugging confirmed my initial guess: My select query and update query are running on two different threads. I will update this finding in my question.

Collectives™ on Stack Overflow

Select for update query: Lock wait timeout exceeded error

2 Answers 2

3 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Linked

Related