如何在SELECT上锁定行，后跟UPDATE [英] How to lock a row over a SELECT followed by an UPDATE
问题描述
在以下内容中锁定单个行的最佳方法是什么？
scenerio：
--TODO - 锁定此行
- 返回下一个id
SELECT next_id
INTO next_id_out
FROM owner.my_id_table
WHERE app_id = app_id_in;
- 更新桌面上的下一个ID
UPDATE owner.my_id_table
SET next_id = next_id_out + 1
WHERE app_id = app_id_in;
我需要确保没有任何改变我之间的id表
选择id并使用下一个可用ID更新表格。
感谢
andrew
PS。是的我是oracle的新手:)
What is the best way to lock an individual row in the following
scenerio:
--TODO - LOCK THIS ROW
-- Return the next id
SELECT next_id
INTO next_id_out
FROM owner.my_id_table
WHERE app_id = app_id_in;
-- Update the next id on the table
UPDATE owner.my_id_table
SET next_id = next_id_out + 1
WHERE app_id = app_id_in;
I need to make sure that nothing changes the id table between me
selecting the id and updating the table with the next available id.
thanks
andrew
PS. yes I am new to oracle :)
推荐答案
除非你在SELECT之间做一些其他的程序处理或检查
和你的更新（没有包含在你的
代码示例中），那么你可以使用一个语句：
UPDATE owner.my_id_table
SET next_id = next_id + 1
WHERE app_id = app_id_in;
否则，请查看FOR UPDATE在手册中：概念或SQL
指南或应用程序开发人员指南（基础）。但是我真的好了/ b $ b认为上面的内容可以用于你的目的。
HTH。
我们******* @ vbusers.com （Andrew Baker）在留言中写道：< c1 ********* *****************@posting.google。 com> ...
Unless you will do some other procedural processing or checking
between your SELECT and your UPDATE (w/c you did not include in your
code sample), then you could just use one statement:
UPDATE owner.my_id_table
SET next_id = next_id + 1
WHERE app_id = app_id_in;
Otherwise, check out "FOR UPDATE" in the manuals: Concepts, or SQL
Guide, or Application Developer''s Guide (Fundamentals). But I really
think the above would work for your purposes.
HTH.
we*******@vbusers.com (Andrew Baker) wrote in message news:<c1**************************@posting.google. com>...
在以下
场景中锁定单个行的最佳方法是什么：
- TODO - 锁定此行 - 返回下一个id
SELECT next_id
INTO next_id_out
FROM owner.my_id_table
WHERE app_id = app_id_in;
- 更新下一个id在桌子上
UPDATE owner.my_id_table
SET next_id = next_id_out + 1
在哪里app_id = app_id_in;
我需要确保没有任何改变之间的id表我选择id并用下一个可用的id更新表格。
感谢
andrew
PS。是的我是oracle的新手：）
What is the best way to lock an individual row in the following
scenerio:
--TODO - LOCK THIS ROW
-- Return the next id
SELECT next_id
INTO next_id_out
FROM owner.my_id_table
WHERE app_id = app_id_in;
-- Update the next id on the table
UPDATE owner.my_id_table
SET next_id = next_id_out + 1
WHERE app_id = app_id_in;
I need to make sure that nothing changes the id table between me
selecting the id and updating the table with the next available id.
thanks
andrew
PS. yes I am new to oracle :)
我们* ****** @vbusers.com （安德鲁·贝克）在留言中写道：< c1 ************************ **@posting.google。 com> ...
we*******@vbusers.com (Andrew Baker) wrote in message news:<c1**************************@posting.google. com>...
在以下
场景中锁定单个行的最佳方法是什么：
- TODO - 锁定此行 - 返回下一个id
SELECT next_id
INTO next_id_out
FROM owner.my_id_table
WHERE app_id = app_id_in;
- 更新下一个id在桌子上
UPDATE owner.my_id_table
SET next_id = next_id_out + 1
在哪里app_id = app_id_in;
我需要确保没有任何改变之间的id表我选择id并用下一个可用的id更新表格。
感谢
andrew
PS。是的我是oracle的新手：）
What is the best way to lock an individual row in the following
scenerio:
--TODO - LOCK THIS ROW
-- Return the next id
SELECT next_id
INTO next_id_out
FROM owner.my_id_table
WHERE app_id = app_id_in;
-- Update the next id on the table
UPDATE owner.my_id_table
SET next_id = next_id_out + 1
WHERE app_id = app_id_in;
I need to make sure that nothing changes the id table between me
selecting the id and updating the table with the next available id.
thanks
andrew
PS. yes I am new to oracle :)
声明
光标my_date是
select SELECT next_id + 1
FROM owner.my_id_table
WHERE app_id = app_id_in
更新next_id;
虚拟数字;
开始
打开my_date;
将my_date取回假人;
update owner.my_table
set next_id = dummy
my_date的当前值;
关闭my_date;
commit;
end;
这种方法避免了两个会话选择相同的next_id。
然而，这种方法会妨碍并发性，这就是为什么Oracle实现*序列的原因
*。他们不需要明确的锁定。
Sybrand Bakker
高级Oracle DBA
declare
cursor my_date is
select SELECT next_id + 1
FROM owner.my_id_table
WHERE app_id = app_id_in
for update of next_id;
dummy number;
begin
open my_date;
fetch my_date into dummy;
update owner.my_table
set next_id = dummy
where current of my_date;
close my_date;
commit;
end;
This approach avoids two sessions select the same next_id.
However, this approach will hamper concurrency, and this is the reason
why Oracle implemented *sequences*. They don''t need explicit locks.
Sybrand Bakker
Senior Oracle DBA
谢谢对于回复，但我认为你可能错过了阅读SQL（后来我用的是b $ b $后来我并不是很清楚！）。第一个语句选择
选择名为next_id_out的store procs输出参数：
SELECT next_id
INTO next_id_out
FROM owner.my_id_table
WHERE app_id = app_id_in;
下一个sql更新表以移动下一个可用的Id通过
一个：
UPDATE owner.my_id_table
SET next_id = next_id_out + 1
WHERE app_id = app_id_in;
我的问题是这个sp将被许多
进程密集调用。所以我认为相同的Id可以两次返回，除非
我锁定了行...
提前感谢
andrew
ro******@hotmail.com （罗密欧奥林匹亚）在留言新闻中写道：< 42 ************************* @ posting.google.c om> ...
Thanks for the reply, but I think you may have miss-read the SQL (with
hindsight I wasn''t being very clear!). The first statement does a
select into the store procs output parameter called "next_id_out":
SELECT next_id
INTO next_id_out
FROM owner.my_id_table
WHERE app_id = app_id_in;
The next sql updates the table to move the next available Id on by
one:
UPDATE owner.my_id_table
SET next_id = next_id_out + 1
WHERE app_id = app_id_in;
my problem is that this sp will be called v. intensively by lots of
processes. So I think that the same Id could be returned twice unless
I lock the row...
thanks in advance
andrew
ro******@hotmail.com (Romeo Olympia) wrote in message news:<42*************************@posting.google.c om>...
除非你在SELECT和UPDATE之间做一些其他的程序处理或检查（没有你没有包含在你的代码示例中），那么你可以使用一个语句：
UPDATE owner.my_id_table
SET next_id = next_id + 1
WHERE app_id = app_id_in;
否则，请查看FOR UPDATE在手册中：概念，SQL指南或Application Developer's Guide（Fundamentals）。但我真的认为上述内容适用于您的目的。
HTH。
我们******* @ vbusers.com （Andrew Baker）在留言中写道：< c1 ***************** *********@posting.google。 com> ...
Unless you will do some other procedural processing or checking
between your SELECT and your UPDATE (w/c you did not include in your
code sample), then you could just use one statement:
UPDATE owner.my_id_table
SET next_id = next_id + 1
WHERE app_id = app_id_in;
Otherwise, check out "FOR UPDATE" in the manuals: Concepts, or SQL
Guide, or Application Developer''s Guide (Fundamentals). But I really
think the above would work for your purposes.
HTH.
we*******@vbusers.com (Andrew Baker) wrote in message news:<c1**************************@posting.google. com>...
在以下
场景中锁定单个行的最佳方法是什么：
- TODO - 锁定此行 - 返回下一个id
SELECT next_id
INTO next_id_out
FROM owner.my_id_table
WHERE app_id = app_id_in;
- 更新下一个id在桌子上
UPDATE owner.my_id_table
SET next_id = next_id_out + 1
在哪里app_id = app_id_in;
我需要确保没有任何改变之间的id表我选择id并用下一个可用的id更新表格。
感谢
andrew
PS。是的我是oracle的新手：）
What is the best way to lock an individual row in the following
scenerio:
--TODO - LOCK THIS ROW
-- Return the next id
SELECT next_id
INTO next_id_out
FROM owner.my_id_table
WHERE app_id = app_id_in;
-- Update the next id on the table
UPDATE owner.my_id_table
SET next_id = next_id_out + 1
WHERE app_id = app_id_in;
I need to make sure that nothing changes the id table between me
selecting the id and updating the table with the next available id.
thanks
andrew
PS. yes I am new to oracle :)
这篇关于如何在SELECT上锁定行，后跟UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
