ACID et al

I'm interested to see any comments the group has on something I'm (haphazardly) working on which in part has to do with guaranteeing the ACID properties without locking.

In case it's of any interest this was partly prompted by several previous threads regarding lock-free db's, in-memory db's and the TRM. Also by my feeling that the historic 'impedance mismatch' that dates back to batch days, has sort of been superceded by new kinds of impedance. Two big mismatches that I can think of are the stateless nature of the www versus conventional persistent db programs and the lack of appreciation in the www protocols such as html for the set theory underpinnings of the RM.

When I think about this, there are plenty of issues to deal with, but one that worries a friend of mine and doesn't worry me at all, has to do with the transaction ACID properties (which is often thought of in an OLTP context, even though I don't see that context as the only important one).

So here's my immediate question:

1. Assume an engine that keeps an in-memory database. By this I mean the whole database insofar as some application is concerned, not some fragment in a cache.
2. Assume that a 'redo' log is written and 'synced' before confirmation of a successful update is returned to a user or client. If the log write fails, the engine terminates and alarm bells go off.
3. Assume the engine is single-threaded. Two users' messages can't interleave their actions between each other, ie., messages are
   'single-file'.
   
4. Assume that a message to the engine includes all the db actions for some 'business' transaction. These actions might include re-iterated reads of rows that were issued in an earlier 'query' message, as well as possibly the values that were returned earlier, as well as update actions.
5. Don't assume that the actions in a message are necessarily executed in any particular order, eg. if a message contains a write action that succeeds followed by a read action that fails, there is a delta or intent mechanism or equivalent that guarantees the write action won't persist after the user has been told that the whole transaction 'failed'.
6. Assume all the queries/actions in such a message must succeed for the message to succeed If any one of them fails, the whole message fails.

I think that Durability is ensured by 2), Isolation by 3), Atomicity by

     5), 6) and Consistency by 4).

For example (for the moment without depending on any particular syntax for the contents of a message), the usual bank debit example would need two messages (note that if teller and branch rows were involved, they could be returned to the user in the reply to the first message along with customer and or balance rows). The second updating message (which might occur many minutes or hours after the first) might reiterate the original queries in some form, perhaps including certain data such as
'balance = $500' which would be asserted to still be true before the
debit is made persistent.

There are more complicated scenarios that people talk about such as the travel booking problem as well as the whole question of 'nested' transaction which I also don't see problems with, but here I've just given a simple example in order to see if people think there's a basic flaw in the assumptions above.

Basically, I'm talking about an engine that is good at checking constraints and thus can be stateless as far as conventional locks are concerned, ie., it would have no 'lock manager' component per se.

Grateful for any comments,

cheers,
paul c. Received on Mon Dec 05 2005 - 09:25:17 CST