How can I insert a record and lock that row simultaneously?

Question

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.

Answer 1

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 insert and the select ... for update. The row is locked by the insert; 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.

Answer 2

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.

1. Add a flag to the table, e.g. VISIBLE, default 'N'.

2. When you insert a row, insert it with the default value.

3. After the commit, select it for update, and update the flag to 'Y' (so that when your code commits the second time, it will update the flag as well).

4. Modify your application so that other sessions will ignore rows where VISIBLE='N'.