снова multithreading

[vc]

I thought you'd had a neater trick up your sleeve If one uses the true SERIALIZABLE, one should also be aware that the entire table will be locked, as the result of 'insert select max(id)+1', both for writes *and* reads. Here, SERIALIZABLE becomes SERIAL, nothing to be proud of, really.

No, that's no true. If there is a unique key (othewise select max(id) would be painful enough by istelf for large tables) on id and the DBMS supports key-range locking plus the optimizer is smart enough you can get away with only a couple of key-ranges being locked.

Yes, of course, with a unique index which is used as the max argument you'll avoid locking the entire table. In any other arrangement (non-unique indexes/no indexes), the entire table will be locked. Even with unique indexes, the locked subset of the table will be unavailable for reads.

VC

[vc]

I thought you'd had a neater trick up your sleeve ;) If one uses the true SERIALIZABLE, one should also be aware that the entire table will be locked, as the result of 'insert select max(id)+1', both for writes *and* reads. Here, SERIALIZABLE becomes SERIAL, nothing to be proud of, really.

No, that's no true. If there is a unique key (othewise select max(id) would be painful enough by istelf for large tables) on id and the DBMS supports key-range locking plus the optimizer is smart enough you can get away with only a couple of key-ranges being locked.

I've thought a bit about this 'true' SERIALIZABLE thingy and concluded that running 'insert select max()+1 from table' concurrrently is prone to dead-locks, indexes or no indexes. Here's an example:

Code: Select all

-- Session 1

1> use test
2> go
Changed database context to 'test'.
1> drop table t1
2> go
1> create table t1(x int, y int)
2> go
1> insert into t1 values(1,1)
2> go
1> create unique index t1_idx on t1(x)
2> go
1> use test
2> go
Changed database context to 'test'.
1> set nocount on
2> set transaction isolation level serializable
3> declare @i int
4> set @i = 1
5> while @i <= 10000 begin
6>   begin tran
7>     insert into t1 select max(x)+1, 99 from t1
8>   commit
9>   set @i = @i + 1
10> end
11> go

-- Session 2

1> use test
2> go
Changed database context to 'test'.
1> set nocount on
2> set transaction isolation level serializable
3> declare @i int
4> set @i = 1
5> while @i <= 10000 begin
6>   begin tran
7>     insert into t1 select max(x)+1, 99 from t1
8>   commit
9>   set @i = @i + 1
10> end
11> go
Msg 1205, Level 13, State 178, Server TEST_1, Line 7
Microsoft OLE DB Provider for SQL Server, Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
1>

So the single-statement-serializable-mode solution is not quite good. Could you suggest a correct ;) alternative ?

VC

[tengiz]

I've thought a bit about this 'true' SERIALIZABLE thingy and concluded that running 'insert select max()+1 from table' concurrrently is prone to dead-locks, indexes or no indexes.

Sure, that's why in SQL Server you should use updlock hint if you want to avoid deadlocks once they annoy you more than serialization conflicts. But again, provided that the optimizer is smart enough and the transaction processing subsystem supports key-range locks, it's much better than just entire table/b-tree locks. Sequence generators / identities are the best solution in most practical scenarios, but not always. Like in those cases we discussed long time ago: <insert into ... where not exist...>. No matter how you code this, you can't avoid using either manual locking or serializable isolation level and automatic key-range/predicate locking.

[Sabina]

Имелся в виду read uncommitted?

А вот здесь придирки по мелочам приветствуются

читаем:
READ UNCOMMITTED (not supported by Oracle8i)--Dirty reads, non-repeatable reads, and phantom reads are all allowed.

READ COMMITTED--Dirty reads are prevented; non-repeatable reads and phantom reads are allowed. If the transaction contains DML statements that require row locks held by other transactions, then any of the statements will block until the row lock it needs is released by the other transaction. (See below for definitions of the italicized terms.)

Я думаю что Оракл не рассматривает subquery как отдельный стейтмент, потому что тогда мой изначальный стейтмент вообще бы никогда не выполнялся. А он работал нормально при default settings, то есть READ COMMITTED.

Сабина

[SBolgov]

Я думаю что Оракл не рассматривает subquery как отдельный стейтмент, потому что тогда мой изначальный стейтмент вообще бы никогда не выполнялся. А он работал нормально при default settings, то есть READ COMMITTED.

А как Вы тестировали?

Разумеется, такой код будет работать хорошо и (казалось бы) правильно до тех пор, пока однажды вдруг не вылезут грабли в другом месте.

[Sabina]

А как Вы тестировали?
Разумеется, такой код будет работать хорошо и (казалось бы) правильно до тех пор, пока однажды вдруг не вылезут грабли в другом месте.

Я не тестировала специально. Просто факт того, что он проходит без нагрузки уже говорит о том, что Оркал никаких локов для subquery по отношению к основному query не ставит.

Сабина

[vc]

Hints like UPDLOCK/TABLOCK and such are considered cheating ;)

So you do agree that the 'true' SERIALIZABLE, by itself, is not adequate in this situation since it leads to a dead-lock almost immediatetely and therefore is not 'true' SERIALIZABLE' ?

VC

[Blake]

If the transaction contains DML statements that require row locks held by other transactions, then any of the statements will block until the row lock it needs is released by the other transaction.

Проблема в том, что select statement не требует никаких locks.
Теперь представь, что Oracle обрабатывает параллельно в двух сессиях

INSERT INTO users (userid,username)
VALUES((SELECT (MAX(userid)+1) FROM users),'name');

Вполне вероятно, что в первую очередь Oracle может выполнить в каждой сессии

SELECT (MAX(userid)+1) FROM users

и получить одинаковое значение userid для каждой сессии.

[tengiz]

So you do agree that the 'true' SERIALIZABLE, by itself, is not adequate in this situation since it leads to a dead-lock almost immediatetely and therefore is not 'true' SERIALIZABLE' ?

No, I don't. Proper transaction isolation prevents anomalies. Sometimes even by means of aborting transactions. Update locking hint in our case, in turn, prevents deadlocks literally serializing the execution (that's why you'd better put code like this at the very end of the transaction). So, updlock here is really just an optimization and does not affect the correctness of the program – and you don't have to put it in there: with or without the locking hint you'll never get inconsistent results.

[vc]

So you do agree that the 'true' SERIALIZABLE, by itself, is not adequate in this situation since it leads to a dead-lock almost immediatetely and therefore is not 'true' SERIALIZABLE' ?

No, I don't. Proper transaction isolation prevents anomalies.

I thought we'd agreed a long while ago to use this informal definition:

"A schedule is serializable if it is equivalent to some serial schedule."

... and not talk in terms of phenomena/anomalies.

If we use the above definition instead of anomalies and such, then the 'true' SERIALIZABLE is not really SERIALIZABLE since some legal schedules can fail.

Update locking hint in our case, in turn, prevents deadlocks literally serializing the execution (that's why you'd better put code like this at the very end of the transaction).

Let's be more precise with our words. The updlock hint does not serialize the transactions it makes them literally *serial* (non-concurrent).

So, updlock here is really just an optimization and does not affect the correctness of the program – and you don't have to put it in there: with or without the locking hint you'll never get inconsistent results.

I would not call it optimization but rather a crutch that helps the invalid ('true' SERIALIZABLE) walk. Without the hint, in my simple example, the second session managed to run only 2-3 transactions before the dead-lock occured. Yeah, I did not get inconsistent results, I got hardly any.

Still waiting for a single-sql-no-hints solution ;)

VC

[tengiz]

vc, it is obvious to me that we have another misunderstanding here. There is no contradiction with either formal or informal definition of ACID transaction. The schedule that subsequently fails is not serializable - that's precisely why one of the transactions has to die - simply because serializable isolation level makes sure that invalid schedules never materialize. And they don't. Exactly as promised. But let's be polite and if you want to discuss this further let's continue in a dedicated topic if you'd be so kind to open one.

[Sabina]

Проблема в том, что select statement не требует никаких locks.
Теперь представь, что Oracle обрабатывает параллельно в двух сессиях

INSERT INTO users (userid,username)
VALUES((SELECT (MAX(userid)+1) FROM users),'name');

Вполне вероятно, что в первую очередь Oracle может выполнить в каждой сессии

SELECT (MAX(userid)+1) FROM users

и получить одинаковое значение userid для каждой сессии.

Понятно. А я проинтерпретировала это по-другому. В частности что INSERT не доберется до таблицы, пока она не освободиться, а select, как subquery, будет выполнятся только после помещения лока на таблицу этим данным конкретным INSERT-ом.

Сабина