Subject Re: [firebird-support] What is the best transaction property settings for an interactive application?
Author Doug Chamberlin
In my experience the transaction isolation type you pick matters far,
far less than other aspects of the application design such as how long
your transactions last and what your application does when an update
conflict occurs. In other words, I think you may be focusing on a choice
that may not matter in the end.

For example, let's says your application starts a transaction, reads
data, commits the transaction, and then displays the data to the user.
If so, it does not matter so much what transaction isolation is used
because you captured some data and presented it. Do you think the user
cares if the data was consistent as of the time of transaction start or
if it changed during the transaction lifetime? Usually they do not
because from the application point of view the data was captured at one
moment.

Now in some cases the integrity of the whole set of data that was
captured DOES matter. In that case you had better not use read
committed. But in my experience it is simply not that critical.

Now, moving on to the really fun stuff: Let's say the user is now
looking at some collection of data retrieved by the above query. The
user edits some of the data and then posts it to the database. At that
point the application starts a transaction, writes the updated data, and
commits. The far more important issue (for an interactive application
such as this) is how an update conflict error gets handled at this
point. You have to tell the user their changes did not get saved. Do you
let them try again? Do you tell them to go find the person who changed
the data they were working on and argue with them about who should be
doing this work? Do you refresh your local data and throw away the
user's changes? Do you just tell them to start over again?

None of the answers to these questions depend on transaction isolation
choice because no matter what choice you make at some point a conflict
will be possible and will happen. So how will you handle it?