Pessimistic locking in Rails by example

Recently my colleague Chris Lubomanski added an optimistic locking to one of our tables in the database. Before that I've never heard of it, but it was very easy to understand. The official Rails documentation explains this type of locking quite good.

Rails also allows to do a pessimistic locking on a database table, but in my opinion, the official documentation for this type of locking is bit poor and doesn't explain the topic well enough.

This is how I would explain in simple terms what pessimistic locking does: if you lock a database record in one thread(or process, it doesn't matter) and perform a database transaction on that record(for example, an update operation), you won't be able to read/update/delete that record from some other thread WHILE the transaction in the first thread is executing.

Here's an example:

I'm fetching the first record from people table, but prepending .first method with .lock method. Because of that, the SELECT query has FOR UPDATE statement at the end. It means that the fetched record is locked while the transaction is executing involving that record. Simple, right?

Have I convinced you that the record will be locked during the transaction? Probably not. Let me show an example that should convince you.

Since this transaction gets executed very fast, I can't show you that the record is indeed locked, unless I do a few little adjustments to the transaction. I'm going to make the transaction sleep for some time, and then I'll open one more Rails console and try to access the locked record. You'll see that the record is being locked indeed while the transaction is executing in the first Rails console. Take a look:

See? While the transaction in the left console was executing, in the right console I wasn't even able to fetch the locked record from the database. Only after the transaction finished, I was able to get the record.

I hope this example helped you to understand the pessimistic locking better.

Note: I was using MySQL database.