снова multithreading

[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-ом.

Сабина