Pessimistic locking locks the entire table?

rubyonrails-talk
1

Hi guys,

I must be missing something obvious with pessimistic locking. Here is what I do (Rails 2.3, mySQL):

In my Foo model:

def test   Foo.transaction do     lock = UpdateLockForFoo.find_by_foo_id(self.id, :lock => true)     sleep 30   end end

For each Foo model, I have an UpdateLockForFoo. This "lock" needs to be acquired before doing any changes to Foo.

I believe it works, except that my entire UpdateLockForFoo table is blocked during the sleep. If I open two terminals and do:

1st terminal: f = Foo.find(1) f.test

2nd termincal: f = Foo.find(2) f.test

The 2nd terminal waits 30s before doing what it has to do. My understanding was that :lock => true only locks a specific record, but it seems it is not the case and it locks the entire table.

Do you know?

Please note: it might not look like a good idea to have this separate UpdateLockForFoo model but I am just simplifying the whole thing here (I need this second model).

Thanks!

2

That depends on what kind of locking the underlying database offers. Which with MySQL depends on the storage manager you're using.

3

Hi Scott,

Thanks. In case it helps others, it is explained here:

Setting transaction to Read Committed did not work for me though, but creating an index on UpdateLockForFoo did the trick.

Thanks!

4

InnoDB's row level locks are actually index-range locks - if there is no index for the column you are using to select the records to lock then this will result in locking the whole table.

Fred

5

wam r. wrote in post #1054714:

Hi guys,

I must be missing something obvious with pessimistic locking. Here is what I do (Rails 2.3, mySQL):

So is there a reasonable use case for pessimistic locking on a web application? That seems insane to me.

6

I don't know if using pessimistic locking is the best way to do it, but here is why I used this:

- every X hours a demon runs and updates records - thing is, this demon "action" can last Y with Y > X

So there is a risk that two instances of the demon try to update the same record. So each demon needs to acquire the lock before they can do anything on a given record.

I guess an alternative could be to try and make sure that a demon only starts if the previous one has finished but this was not an option in my case.

Adding an index was actually fairly easy.

7

wam r. wrote in post #1054916:

I don't know if using pessimistic locking is the best way to do it, but here is why I used this:

- every X hours a demon runs and updates records - thing is, this demon "action" can last Y with Y > X

So there is a risk that two instances of the demon try to update the same record. So each demon needs to acquire the lock before they can do anything on a given record.

I guess an alternative could be to try and make sure that a demon only starts if the previous one has finished but this was not an option in my case.

Adding an index was actually fairly easy.

Thanks for the clarification. I hope I didn't sound condescending in my previous reply. I was asking because I was interested to know of a case where pessimistic locking might be useful in a web environment.

This does make some sense in that case. Have you attempted to calculate the performance effect on user driven queries while the daemon is performing this pessimistic locking batch update? It would be interesting to know whether the overhead of acquiring the locks would be significant compared to using optimistic locking and handing the likely few unresolvable conflicts that might arise.

I'd imagine that most optimistic locking issues could be resolved by catching the optimistic failure, merging the changes and rewriting. Of course that leaves the possibility of a conflict on changes to the same field, where another strategy might be need. Something like a "last write wins," or "user changes override daemon changes (or vice versa ). In a worst case scenario unresolvable conflicts might just have to be recorded and skipped over by the daemon process.

This might sound "bad" on the surface, but when compared with conflicting changes between two actual users it's likely not as big a deal as it seems. In the end it might actually turn out to be safer than introducing the possibility of deadlocking the database due to a pessimistic locking scheme.

Of course this all depends on the specific nature of the app (i.e. whether user changes are fairly isolated, or multiple users often manipulate the same data)?

In the end though it all comes down to metrics. It's far too easy to spend too much time optimizing only to find out later that time spent gained you almost nothing.

8

This is similar to a application I am working on. In my case, all updates to the database, with one exception, can be done atomically. For examples, use SQL UPDATE to increment a field rather than read with pure Rails/ActiveRecord, increment model instance, and write. The one exception is adding new instances of model with several assocations.

My solution is that all problematic actions are done with ONE Resque worker that runs forever. The cron jobs are Ruby (not Rails) programs that enqueue a task to the Resque worker. This has worked very well. I was pleasantly surprised how easy it went together. The one con is that Resque workers are not good about reporting exceptions and other problems and in the development environment they reload properly after most, but not all changes. So in the development environment if there are problems or I am making big and deep changes, I will stop the Resque worker and run the problematic code with script/runner or whatever the Rails 3 equivalent is.

HTH,   Jeffrey

Quoting PierreW <wamrewam@googlemail.com>:

9

I don't know if using pessimistic locking is the best way to do it, but here is why I used this:

- every X hours a demon runs and updates records - thing is, this demon "action" can last Y with Y > X

So there is a risk that two instances of the demon try to update the same record. So each demon needs to acquire the lock before they can do anything on a given record.

I guess an alternative could be to try and make sure that a demon only starts if the previous one has finished but this was not an option in my case.

You might want to look at separating the concept of the SQL lock (which do have costs, can land you with deadlocks etc) from the application level lock.

You could have a locked column (better a locked_since column) that indicates that the row is locked. Use optimistic or pessimistic locking to update the locked_since column, and make your update stuff ignore records with a non null locked_since

Fred

10

What happens when a 2nd process tries to write to a record/table that is locked? Does it stall until the lock is released or

does it throw an exception?

11

What happens when a 2nd process tries to write to a record/table that is locked? Does it stall until the lock is released or does it throw an exception?

It will block until the lock is released. If the lock is never released, depending on your database settings, it will give up at some point and throw an exception

Fred