Is Oracle INSERT with SELECT thread safe?
402 观看
2回复
1 作者的声誉
Example query:
insert into book (id, name, count)
values(book_id_seq.nextval , 'stack', (select count(*) from book)+1);
Will this have any data inconsistencies ('count'
column) or be subject to race conditions in a multithreaded environment?
回应 2
1像
9334 作者的声誉
The answer depends on the setting on ISOLATION LEVEL - with the default setting read committed
every query executed by a transaction sees only data committed before the query began
This means in your case if two different sessions perform this insert in parallel
-- session 1
insert into book (id,name,cnt) values (2,'a',(select max(cnt)+1 from book));
-- session 2
insert into book (id,name,cnt) values (3,'b',(select max(cnt)+1 from book));
you'll see (after both sessions commit) the same count in both new records.
ID NAME CNT
---------- ---------- ----------
1 i 1
2 a 2
3 b 2
Anyway if you do this on a columns with a unique constraint (e.g. primary key) - which you should NOT do at any circumstances! the behaviour is different (use sequence to assing ID for concurent insert - the max(id) +1 approch is OK for strictly single session run).
-- session 1
insert into book (id,name,cnt) values ((select max(id)+1 from book),'a',1);
-- session2
insert into book (id,name) values ((select max(id)+1 from book),'b');
here the session 2 is waiting on a lock until the session 1 is commited and then you receive an error:
ORA-00001: unique constraint (REPORTER.SYS_C0026759) violated
so only the first insert succeeds.
So the short answer ist no there are no inconsistencies. If you do not want to see identical count in more records you could catch it defining a unique constraint on the CNT
column. But againg do not do this - the CNT column in the table is ill designed. The value should not be stored, but calculated in query (e.g. as a count of records with ID
lower than the records ID
).
0像
37789 作者的声誉
I'm not sure what you mean by "safe". The query you proposed:
insert into book (id, name, count)
values(book_id_seq.nextval , 'stack', (select count(*) from book)+1);
will run successfully in an environment where multiple connections to the database execute the same statement concurrently but may not give the results you'd like, although I must confess I don't know what results you're expecting.
Let's consider the following example:
- At time one there are no entries in BOOK and BOOK_ID_SEQ's "Next number" is set to 1.
At time two client A executes the above statement, creating the following row:
ID=1 NAME='stack' count=1
At time three client B executes the above statement, creating the following row (note that COUNT is 1 here because client A has not committed, and therefore client B cannot 'see' the row which has been created by client A yet):
ID=2 NAME='stack' COUNT=1
At time four client A commits changes.
- At time five client B commits changes.
At time six client C executes the above statement, creating the following row:
ID=3 NAME='stack' COUNT=3
At time seven client C commits changes.
So our BOOK table now contains
ID NAME COUNT
1 stack 1
2 stack 1
3 stack 3
The actual timing of the statements doesn't matter - the time between the statement executions and commits could be microseconds or hours. What does matter is the sequencing of the statement execution and the commits.
Best of luck.
作者: Bob Jarvis 发布者: 2017 年 12 月 27 日来自类别的问题 :
- sql 检查SQL Server表的更改?
- sql 数据库索引如何工作?
- sql 如何索引数据库列
- sql 删除名称以某个字符串开头的所有表
- sql 应该有人决定切换数据库系统
- sql 如何在SQL数据库表中选择第n行?
- multithreading 什么是比赛条件?
- multithreading 什么是僵局?
- multithreading 什么是互斥?
- multithreading 如何在普通C中启动线程?
- multithreading Threadsafe foreach枚举列表
- multithreading 何时在C#中使用volatile关键字?
- oracle 在Oracle中进行多行插入的最佳方法是什么?
- oracle 您要查询哪些表/视图以选择Oracle中架构中的所有表名?
- oracle 如何解决ORA-011033:正在进行ORACLE初始化或关闭
- oracle 如何在不同的表空间中导入oracle转储
- oracle Oracle中的双表是什么?