Re: Question about SERIALIZE transaction isolation


"Roy Hann" <specially@xxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ef6dncCufqMTcqXXnZ2dnUVZ8k6dnZ2d@xxxxxxxxxxxx
Christoph Rupp wrote:

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?

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.

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.

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



.

Relevant Pages

  • Re: Question about SERIALIZE transaction isolation
    ... serializable isolation it should never be found even by a future query ... in the same transaction. ... BEGIN TXN B ... isolation level or fix the SERIALIZABLE isolation level. ...
    (comp.databases.theory)
  • RE: Guarantee Transaction Isolation
    ... What you have will work with SERIALIZABLE isolation, but will not with the default READ COMMITED isolation. ... you can use either TABLOCKX or HOLDLOCK locking hints. ... > I would use a TABLOCK lock hint in the select, and ensure that I kept my transaction as short as possible. ... >> ShowNumber SMALLINT NOT NULL, ...
    (microsoft.public.sqlserver.programming)
  • Re: Question about SERIALIZE transaction isolation
    ... database engine. ... This one is about the behaviour of a read-only transaction ... serializable isolation it should never be found even by a future query ... to create a realistic illusion that you have the database for your ...
    (comp.databases.theory)
  • Re: MySQL ResultSet - count rows?
    ... SELECT COUNTapproach will only be guaranteed to work if you execute ... both SELECTs in a single transaction AND use SERIALIZABLE isolation ...
    (comp.lang.java.databases)
  • Re: Pervasive doesnt allow any more incoming connections
    ... I can shed some light on some of the log entries but do not believe ... So the database engine logs that another process has the file locked. ... This would be the transaction durabitlity log file that is corrupt. ... >Pervasive connections, while the existing connections can still remain ...
    (comp.databases.btrieve)