如何同时插入一条记录并锁定该行？
我正在使用配置为使用自治事务的 Oracle 存储过程插入一行。我想插入此记录，提交该事务，然后锁定新插入的记录，以便除了我当前的会话之外没有其他人可以修改它（显然，在另一个事务中，因为插入它的事务是自治的）。
在我有机会SELECT...FOR UPDATE之前，如何确保没有其他人锁定此新记录？
使用Oracle 10g。
I'm inserting a row using an Oracle stored procedure which is configured to use an autonomous transaction. I'd like to insert this record, commit that transaction, and then lock the newly-inserted record so that nobody else can modify it except my current session (in another transaction, obviously, since the one that inserted it is autonomous).
How can I ensure that nobody else gets a lock on this new record before I have a chance to SELECT...FOR UPDATE it?
Using Oracle 10g.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不，您永远无法在事务之间保持锁定。您应该问自己的问题是为什么需要在
insert和select ... for update之间发出提交。该行被insert锁定；如果您在提交之前完成了对该行所做的任何操作，那么您不必担心重新锁定该行。No, you can never maintain a lock between transactions. The question you should be asking yourself is why you need to issue the commit between the
insertand theselect ... for update. The row is locked by theinsert; if you finish whatever it is that you're doing with that row before you commit, then you don't have to worry about re-locking the row.我的第一选择是删除 COMMIT，直到数据可供其他会话使用。
或者，您可以以确保新行仅被该事务锁定的方式设计应用程序。
向表格添加标志，例如
VISIBLE，默认'N'。插入行时，使用默认值插入。
提交后，
选择它进行更新，并将标志更新为'Y'（这样当您的代码提交第二个时间，它也会更新标志）。修改您的应用程序，以便其他会话将忽略
VISIBLE='N'处的行。修改
My first preference would be to remove the COMMIT until the data is ready for use by other sessions.
Alternatively, you could design your application in such a way as to make sure the new row only gets locked by that transaction.
Add a flag to the table, e.g.
VISIBLE, default'N'.When you insert a row, insert it with the default value.
After the commit,
selectitfor update, and update the flag to'Y'(so that when your code commits the second time, it will update the flag as well).Modify your application so that other sessions will ignore rows where
VISIBLE='N'.