Hi,

i'm nearly ready for my first release of my new concurrent, multi-
threaded, ACID transactional, logical idempotent logging, lock-free
database engine (key/value storage).

OK, enough buzzwords for today

Some of you were kind enough to help me a couple of times with my
questions. This one is about the behaviour of a read-only transaction
in the SERIALIZE isolation level.

TXN A:
BEGIN
INSERT (a, b)

TXN B:
BEGIN
FIND (a)

Does the lookup of an un-committed item return KEY_NOT_FOUND or does
it create a transaction conflict?

Some background: In my Database all transaction conflicts are
discovered in memory, without accessing the buffers. My Transactions
do not lock each other or keep locks during their life time.
Currently, if there's a conflict, my insert/erase functions return an
error (TXN_CONFLICT).

I did some research about the SERIALIZE isolation level but found
documentation which mentions that a lookup would cause a wait (http://
http://www.redhat.com/docs/manuals/d...alizable.html).
But i think that it's annoying when most range queries fail because of
conflicts. I think it's better if the uncommitted key is ignored (only
for lookups).

Thanks
Christoph

PS: you see that i'm wearing a developer hat, i'm not a DB admin or
SQL guru...

Christoph Rupp wrote:

The key shouldn't be found in transaction B, and to maintain
serializable isolation it should never be found even by a future query
in the same transaction. I have no idea what a "transaction conflict"
is but it sounds like an error. The point of serializable isolation is
to create a realistic illusion that you have the database for your
sole use. Random errors caused by the actions of other users would
spoil that illusion. If you don't want to wait, you'll have to keep
track of key values that are added while transaction B is in progress so
you can make sure you exclude them from the results of that transaction.

--
Roy

Roy,

thanks for your reply.

On 17 Jun., 15:28, Roy Hann <specia... (AT) processed (DOT) almost.meat> wrote:
When i read that, i realize that i actually implemented READ_COMMITTED
instead of SERIALIZABLE

BEGIN TXN A
INSERT (a, b)
BEGIN TXN B
FIND (a) -> error/block
COMMIT A
FIND (a) -> found!
COMMIT B

Now i have two choices: either use READ_COMMITTED as my default
isolation level or fix the SERIALIZABLE isolation level. A quick
search shows that MSSQL uses READ_COMMITTED as the default level, and
others (Oracle, SAPDB) as well. So i guess i do that, too.

Regards
Christoph

"Roy Hann" <specially (AT) processed (DOT) almost.meat> wrote


Going back some 15 to 25 years, Rdb/VMS implemented an interesting variation
on the traditional locking scheme.

In the default configuration, transactions that began ad READ-ONLY
transactions were turned into SNAPSHOT transactions. Snaphot transactions
could read everything that had been commited prior to the start of the
snapshot transaction, but nothing else. That means that inserts committed
after the snapshot are invisible to the snapshot, deletes commited after
the snapshot are visible, and updates committed after the transaction have
to deliver the before image of the data updated. Before images were stored
in what I guess is multiversioning, in a special storage area for snapshot
data.

In the snapshot scheme as implemented by Rdb/VMS Txn B is treated as BEFORE
Txn A, even though Txn A started first. It therefore will not see the insert
doen by Txn A before Txn A committs, and even after Txn A commits. If
snapshotting has been disbled by the DBA or if the transaction is started as
read write, even though it's not going to write anything,
Txn B will block when it tried to read data altered by Txn A, and wait for
A to complete.

The Rdb/VMS implementation may not be of any interest to you in terms of the
engine you are building, but it's interesting to note that this question
has been asked and anaswered before. Hopefully, some of the literature
concerning building database systems has captured whatever insights of
value, if any, were captured by the Rdb engineers way back when.

"Roy Hann" <specially (AT) processed (DOT) almost.meat> wrote

This is not what is intended by the serializable transaction isolation
level. A transaction executing under the serializable transaction isolation
level requires that every update by /other transactions/ be blocked if it
might affect query results. Updates that occur within a transaction
/should/ be seen by subsequent queries within the same transaction.

Brian Selzer wrote:

I think you misintepret what I meant by "same transaction". I meant
"the same" transaction B. My apologies for permitting the misreading.

Of course; but I didn't say otherwise.

--
Roy

On 17 Jun., 17:15, "Walter Mitty" <wami... (AT) verizon (DOT) net> wrote:
Hi Walter,

thanks for the reply.

The SNAPSHOT is interesting for two reasons:

- it's very easy for me to be implement

- i read about it this morning when reading a paper about future OLTP
development trends. A paragraph caught my attention, it writes about
"relaxed" transactional models (ACI without D etc):
"""For example, the widespread adoption of snapshot isolation (which
is non-transactional) suggests that many users are willing to trade
transactional semantics for performance (in this case, due to the
elimination of read locks)."""

In my case i don't hold any locks, so i don't have performance
penalties. But after reading your reply i understand that SNAPSHOT
isolation improved the locking in more traditional DBMS engines.

Anyway - i decided yesterday to pick READ_COMMITTED as my default
isolation level. That's the only level i implement (for now) and i
really want to have my first release this month, that's why i'm going
for the cheapest solution (other isolation levels will follow
later, they don't have such a high priority for me).

But before i do that, i'll look for good literature about this topic.
Recommendations are welcome.

Regards
Christoph

As I remember it from my ANSI X3H2 days, everyone else got "kicked out
of the DB" when there was a session with serializable transaction
isolation level. The intent was not to allow any other concurrent
transactions. This was how you could do physical file maintaining
tasks on the WHOLE database. But we aleays added "effectively" to
the definitions.