Re: computational model of transactions

Marshall wrote:
> I've been thinking about concurrency and transactions. It occurs to me
> that much of the difficulty lies with the fact that multiple concurrent
> transactions may operate on the same data. I begin to recall some
> conversations from years past about "multiple assignment".

That is the concurrency problem, two cooks in the kitchen. I believe the term 'transaction' was coined to give a program context for its updates, ie. as I think Gray put it, "a transaction maps a db from one consistent state to another".

> It seems to me that much or maybe all of the difficulty with multiple
> concurrent transactions operating on the same data would be
> eliminated if it wasn't possible for a transaction to read back
> the results of its own writes.

2PL (two-phase lock protocol, Gray?) says that a set of locks is acquired over time but relinguished instantaneously. One way to look at this is that once the locks are released, the transaction is finished by definition. I presume this was what SQL's COMMIT intended but  

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc.sqlref/bjnrmstr383.htm

seems to allow muddy water, at least in DB2. I wonder if DB2 is confusing nested transactions with its behaviour.

It's not just a transaction's "writes", if it sees the writes of other transactions, then we can get overlapping, inconsistent states. I believe this is what SQL intended with the "SERIALIZABLE" isolation mode.

> In other words, consider the model in which transactions can only
> observe the database state that was current at the time the
> transaction started.

As above, that was intended to be the SERIALIZABLE mode.

> So, how much of a burden, at the *logical* level, would this be?

The burden is that user 2 may get part-way through his work (transaction), only to find that user 1 has pre-empted him. The other isolation modes try to mollify this, but at the risk of both users producing wrong states.

> Clearly it is not the same as with SQL transactions. Does it
> matter? Is there a use case I'm not thinking of that makes this
> problematic? I will admit there have been times where I've
> opened up an interactive SQL session, started a transaction,
> and typed a whole series of DML, and observed the results
> along the way, but I don't think I've ever written *code* that
> does anything like that.
>
> Your experiences and thoughts are appreciated.

You may be tired of me harping on about this, but locks (logical ones, not physical ones) in the first place were merely an implementation device to give the effect of the single-threaded user work. In the context of many tables with many columns they can end up being quite a crude, heavy-handed effect of that.

(I know you aren't talking only of RT but as far as that goes, I don't see that it is in any way lacking by not mentioning concurrency - not that you said anything like that. More than one way to skin a cat, eg.,   SERIALIZABLE doesn't mean that program executions are serialized, it means that the result will appear as if they had been. One could equally have a lock-free implementation that did in fact serialize the program executions which would give a result that was the real thing. I admit that this might not be much fun with a disk-based db.)

p Received on Tue Aug 01 2006 - 20:12:14 CEST