How are locking mechanisms (Pessimistic/Optimistic) related to database transaction isolation levels?

Question

I am writing a web application where two different users can update a list of things, to do list, for example. I have come to realize that, optimistic locking mechanism works best since I don't expect high contention.

I was looking at transaction isolation levels and now I am a little confused. Looks like different transaction isolation levels also solve similar problems.

How are these two different concepts related to each other? If possible, with a simple example.

Tyrontyrone

Answer 1

Both of these things are related to data consistency and concurrent access, but they are two different mechanisms.

Locking prevents concurrent access to some object. For example when you attempt to update a todo list item, with pessimistic locking database places a row lock on the record until you either commit or rollback the transaction, so that no other transaction is allowed to update the same record. Optimistic locking is application-side check whether the timestamp/version of a record has changed between fetching and attempting to update it. This is regardless of transaction isolation level.

Transaction isolation is about read consistency.

• Read uncommitted level allows session to see other session's uncommitted changes
• Read committed level allows session to see other session's committed changes only
• Serializable level allows session to see only changes committed before the transaction began

Take a look at below example, I indicated the query results that differ between transaction isolation levels.

SESSION 1                                  SESSION 2
--------------------------------           --------------------------------------
SELECT count(*) FROM test;
=> 10
                                           INSERT INTO test VALUES ('x');

SELECT count(*) FROM test;
=> 10 with read committed/serializable
=> 11 with read uncommited (dirty read)
                                           COMMIT;

SELECT count(*) FROM test;
=> 10 with serializable
=> 11 with read uncommitted/read committed

There are four ANSI specified transaction isolation levels (one not mentioned in the example above is "repeatable read"), all of them except serializable are subjects to some anomalies. Note it has nothing to do with locking.

You can take a look at Oracle documentation on this here, the concepts are quite universal.

Finally, your approach to use optimistic locking seems sensible for a web application. Most probably you fetch a list item and update it in two different HTTP requests. It is impossible (or unwise at least) to keep transaction open with explicit lock on the record after the fetch (how do you know whether the second request will arrive at all?) Optimistic locking handles this gracefully.

Rhombohedron

ORMs which provide OCC do so by adding another column (version, timestamp). An exception is thrown if the row has been updated (by another transaction) since the time it was fetched by the first transaction (for example, if the version does not match the value it was fetched with). This has got nothing to do with the transaction isolation levels (except that the isolation level should be at least READ COMMITTED). Am I right? – Tyrontyrone

I have a follow up question. Do any of the transaction isolation levels stop or make a session ("session 2") wait from inserting values (to the table)/ or update the same data (rows) that that is being read by "session 1"? – Tyrontyrone

With some engines (DB2?) maybe - I'm not sure. – Emeliaemelin

One special case to be aware of: if serializable transaction attempts to update rows that were modified and committed after it began, the transaction fails - doesn't wait, just raises an exception and rollbacks if uncaught. – Emeliaemelin

Answer 2

Locking mechanisms are usually used to implement transaction isolation levels. So transaction isolation levels define contract how your transactions have to behave in concurrent execution. Locking mechanisms are implementation details.

From application writing perspective you should focus on setting appropriate transaction isolation level. Of course setting specific isolation level implicates locking, but as long as you don't have your application under heavy load, you don't need to take care of it much.

Import thing is that locking mechanisms differ between database engines. If you write application for one database and after some time you would change db engine, your application may behave differently or some part of it may require rewriting.

My advice from fifteen years of business application development is not to rely on explicit locking.

Herrenvolk

I see that ORMs like Hibernate, Squeryl provide Optimistic concurrency control. Does it mean the ORMs will set the transaction isolation levels for you and notify you whenever problem occurs? Also, are isolation levels set at database level (same for all transactions) or I can set any isolation I want for each transaction through my application? – Tyrontyrone

This is so wrong. Locking and transaction isolation are different mechanisms and serve different purposes. One does not imply the other in any way. – Emeliaemelin

I don't think @Jaroslaw is completely wrong. Seems like, from what I have been reading, databases do take a "pessimistic" or "optimistic" approach to obtain and hold locks to implement transaction isolation levels. But that was not what I was referring to in the questions. I guess I should have put "Optimistic Concurrency Control" which probably would have made things a little bit clearer. – Tyrontyrone

As I explained, transaction isolation is about read consistency. Most databases (MySQL, PostgreSQL, Oracle) don't use locks to implement this. Ok, could be the case for some dbs... I'm not sure, but I think DB2 can place implicit locks during read to maintain consistency, it could also depend on isolation level, but that's an uncommon approach. – Emeliaemelin