Re: Question about SERIALIZE transaction isolation

Roy,

thanks for your reply.

On 17 Jun., 15:28, Roy Hann <specia...@xxxxxxxxxxxxxxxxxxxxx> 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.

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
.

Relevant Pages

  • 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: 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: ISOLATION levels and when to use SERIALIZEABLE
    ... Setting an isolation level tells SQL Server ... satisfy the where clause of a query in the transaction. ... acquiring KEY RANGE locks to protect a range of data in an index. ... select * into orders2 from orders ...
    (microsoft.public.sqlserver.programming)