The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Using READ_COMMITED transaction isolation


I've been trying to use ORMs with READ_COMMITTED isolation level for over a year now and I'm getting *very* frustrated with the bugs resulting from my lack of experience. I would appreciate it if you guys could give me a head's up as to what I am missing here.

Pretty much all application logic I implement cannot be expressed in terms of a single SQL query. That is, I almost always end up having to walk the object graph, fire multiple queries or both. Inevitably I run into inconsistent state from within the same transaction. For example, imagine you have a linked list of objects stored in the database and you want to detect a cycle in the loop (described in greater detail below). I can't think of any way to do this under READ_COMMITTED because the outbound references could be updated from underneath my feet as I walk the graph. I can only see READ_COMMITTED working is for queries which can be represented using a single SQL statement.

Here are some specific questions I'd like answered:

1) Is there some sort of special programming technique or guideline that one must follow when developing against READ_COMMITTED to guarantee consistency?

2) Does everyone really use READ_COMMITTED (it is the default, after all) or do you guys use SERIALIZABLE to ensure consistent behavior?

3) It's also not clear to me what sort of performance hit I can expect when running under READ_COMMITTED vs SERIALIZABLE. I know this is database specific but I'd love to hear some sort of ballpark figure.

Here is the loop-detection problem I promised. Initially you have this linked list:

A -> B -> C -> A

1) Thread 1 reads: A -> B
2) Context switch to thread 2
2) Thread 2 replaces the sequence by: D -> B -> C -> D
3) Context switch to thread 1
4) Thread 1 reads: C -> D
5) Thread 1 detects no loop although one should have been detected. From Thread 1's point of view it read: A -> B -> C -> D

Optimistic locking will not protect you from this because Thread 1 does not modify any objects, even though their value is vital to its decision.

Thank you,
Gili Send private email
Friday, October 19, 2007
Even though a single rowset will not meet your needs, you can still use a single SP to return multiple rowsets (at least in SQL Server).  This does tie your SP closer to your object model, since the SP will be providing the multiple recordsets needed to construct a (partial) object hierarchy.

This is common in Rocky's CSLA framework.  Benefits include being able to reduce the number of trips to the database and the amount of data going across the wire.

Whether you can enforce all the rowsets within an SP to be built from a contemporaneous snapshot, is still a potential issue.
Cade Roux Send private email
Friday, October 19, 2007

What does "SP" refer to in the context of your post?
Gili Send private email
Friday, October 19, 2007
On second glance, I am guessing "SP" refers to Stored Procedure :)

Yes, I guess that is an approach but I was hoping to avoid using stored procedures for starters, mainly for the sake of flexibility. I don't want to have to a new stored procedure every time I come up with another application use-case. I believe portability is also a concern (but less so).
Gili Send private email
Friday, October 19, 2007

Maybe you can approach this from a different angle.  Instead of expecting the DB to be consistent, force that consistency to be there from your application.  Use the MUTEX Thread locking mechanism to ensure the consistency that you need.

A -> B -> C -> A

1) Thread 1 gets the Mutex Lock
2) Thread 1 reads: A -> B
3) Context switch to thread 2
4) Thread 2 attempts to get the Mutex Lock and fails and is put to sleep
5) Context switch to thread 1
6) Thread 1 reads: C -> A and detects the loop
7) Thread 1 releases the Mutex Lock
8) Context switch to thread 2
9) Thread 2 gets the Mutex Lock
10) Context switch to Thread 1 (again)
11) Thread 1 attempts to get the Mutex Lock and fails and is put to sleep
10) Context switch to Thread 2
12) Thread 2 replaces the sequence by: D -> B -> C -> D
13) etc,...

Hope this helps!!!

Paul A. Fortin Send private email
Saturday, October 20, 2007
Hi Paul,

  Hibernate's documentation specifically recommends against this approach. They mention a bunch of reasons but the one that stuck in my mind is that different processes/computers wouldn't be able to see your locks. That is, if you use database clustering or multiple processes access the database on the same machine, they won't see each other's locks.


Sunday, October 21, 2007
Hmm, no more replies...

Am I to assume that no one has a good answer to this? Any ideas on who would or where I should repost this question?
Gili Send private email
Thursday, October 25, 2007
Try this place:

They're SQL Server-specific, but transaction management ideas should be transferrable.
ain't tellin' Send private email
Friday, October 26, 2007
if you want to make 100% sure that you get a snapshot from the start of the transaction, just use serializable. Else, use read_commited. most probably any workarounds or complex dances you try to do yourself will end up costing more than asking the database to do the work for you, and even more.. you already paid the database people to implement and test it!
antonio vargas Send private email
Tuesday, October 30, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz