On 10/15/07, Syan Tan <kittylitter@people.net.au> wrote:
> thanks. I'll try it out, but sounds true enough; so there is no
> isolation level where transaction state keeps track of all the read versions
> of any item read so far by the transaction ?
Right. That would be a whole different set of semantics.
> The main question is whether there's a way of avoiding implementing
> either those patterns of offline optimistic locking (version checking)
> or offline pessimistic locking ( lock table checking) , when
> you've got interactive client applications ?
Those are the only two methods I'm aware of. If anyone else knows of
others, I'm sure they'll speak up.
> If there was an isolation level that kept track of the versions of items
> a transaction has read, and there was a "commit with continue" command, then
> you could avoid that extra work in clients - or maybe warping transactions
> for this purpose makes it more difficult to write transactions for
> the usual batch processing purposes.
Transactions are governed by the SQL standard, so changing that would
not really be an option anyway. Even so, trying to implement that
behavior (in any form) would get really complicated when you consider
situations other than single identifiable rows.
For instance, if you do "SELECT * FROM foo LIMIT 5" you essentially
get 5 arbitrary rows back. If they don't actually have a primary key,
you can't identify them individually. What would "previously read"
mean in such a situation?
Or what about rows constructed via joins from other tables? There is
more than one way to join data; if you read the same rows from the
underlying tables, but join them in a different way to get the final
result, does that count as being previously read or not? What about
aggregate results; is that one previously read row, or all of the rows
that went into the aggregate? What if all of this is happening
through a VIEW, and the application doesn't know it's coming from the
same tables underneath?
Using transactions as a unit of isolation works well because they
don't have any semantics that rely on specific rows; they cover what
data you are able to see and that's about it. An application can
understand that a row is individually identifiable because that's how
it was designed, but in general terms there's no such guarantee.
Trying to define that kind of isolation level in the general case
would be really hard.
