снова multithreading

[Palych]

Интересно, Oracle позволяет сделать так:
INSERT INTO users (userid,username)
VALUES((SELECT (MAX(userid)+1) FROM users FOR UPDATE),'name');
Но это я так, из любопытства. А вообще про такие трюки лучше забыть сразу, и пользоваться генераторами встроенными в базу, или UUID...

[vc]

The provided statement, by itself, wil never work correctly.
VC

Never ?

Сабина

You said it yourself in the original message. For concurrent transactions, the SQL will produce duplicates.

"..by itself, will never work" should be re-phrased as "..by itself, should *never* be used in order to guarantee unique ids". But that's nitpicking, really.

VC

[Blake]

Сабина, "set transaction isolation" действительно не поможет

Можно еще попробовать так, но это уже не совсем multithreading:

1)lock table USERS in exclusive mode;

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

3)commit; (rollback)

[SBolgov]

Можно еще попробовать так ...:

1)lock table USERS in exclusive mode;

Гм. И чего только ни придумают люди для того, чтобы всё-таки наступить на те грабли, которые разработчики предусмотрительно убрали с дороги.

[tengiz]

Как уже было сказано, для ORACLE не существует стандартного решения этой задачи кроме как использование последовательностей или блокировка таблицы целиком. Любой другой вариант либо упрётся в ограничения модели изоляции транзакций, реализованной в ORACLE, либо потребует более, чем одного оператора SQL и обязательного наличия уникального индекса по userid (хотя такой индекс в любом случае полезен, но необязателен в случае других СУБД).

[vc]

Как уже было сказано, для ORACLE не существует стандартного решения этой задачи кроме как использование последовательностей или блокировка таблицы целиком. Любой другой вариант либо упрётся в ограничения модели изоляции транзакций, реализованной в ORACLE, либо потребует более, чем одного оператора SQL и обязательного наличия уникального индекса по userid (хотя такой индекс в любом случае полезен, но необязателен в случае других СУБД).

Hello,

Long time, no hear, have you had a nice vacation ?

1. The sequence approach is a *standard* solution (see SQL'99's SQL/Foundation part), the alternative standard being the identity column. Oracle implements the sequence generator, SQL Server does the identity column, and DB2 has both.


2. From sheer curiosity, how one would implement a unique id generation in a blocking scheduler with a *single* SQL. Hints like UPDLOCK/TABLOCK and such are considered cheating ;)

VC

[geek7]

Как тут гарантировать уникальность выборки id?

Установить нужный transaction isolation level, например "read committed"

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

[tengiz]

Long time, no hear, have you had a nice vacation ?

Hope will have soon

The sequence approach is a *standard* solution

Так ведь я и сказал, что "не существует стандартного решения этой задачи кроме как использование последовательностей..."

From sheer curiosity, how one would implement a unique id generation in a blocking scheduler with a *single* SQL.

Использовать настоящий SERIALIZABLE (а не то, что в оракле так называется) с оригинальным запросом Сабины и никаких аномалий не будет.

[vc]

...

The sequence approach is a *standard* solution

Так ведь я и сказал, что "не существует стандартного решения этой задачи кроме как использование последовательностей..."

Ah, sorry my fault I mistakenly thought you'd meant that the sequence is a non-standard solution.

Regarding an Oracle solution without a sequence, true, *two* statements are required but *no* index is needed.

From sheer curiosity, how one would implement a unique id generation in a blocking scheduler with a *single* SQL.

Использовать настоящий SERIALIZABLE (а не то, что в оракле так называется) с оригинальным запросом Сабины и никаких аномалий не будет.

Oh, you meant this. 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. Essentially, the behaviour will be the same as in the 'solution' someone suggested for Oracle:

1. Lock the whole table
2. Do your stuff

.. no, the concurrency will be even worse, because in Oracle you can still read the locked table.

Also, it might be interesting to remind (I believe I mentioned the fact during one of our long discussions) that in version 7.x.x Oracle used to have the 'true' SERIALIZABLE whereby a table being selected from was automatically read-share locked thus preventing concurrent updates until the outstanding read transaction commits. More recent versions of Oracle retain this mode which can be enabled by setting a hidden parameter to 'true'. So technically speaking, in this mode, precisely the same *single* statement would work in the same way as it would in any locking scheduler SERIALIZABLE mode. Needless to say that the 'true' Oracle SERIALIZABLE' was not a success story due to abysmal concurrency.

In comparison to the 'true' Oracle serializable, the locking scheduler SERIALIZABLE provides more concurrency in some situations and less in others as you no doubt know. Neither is very suitable for heavily concurrent transactions. In the OP's situation, however, Oracle's 'true' SERIALIZABLE (not SNAPSHOT) would have an edge thanks to allowing concurrent reads.

All this discussion is of course purely theoretical because one should just use IDENTITY im SQL Server and SEQUENCE in Oracle and forget about possible but not concurrent alternatives.

Regards.

VC

[tengiz]

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.

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

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

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