Thanks for the question, Bhanu Teja.
Asked: October 05, 2016 - 10:54 pm UTC
Last updated: October 06, 2016 - 1:53 am UTC
Version: 11g
Viewed 1000+ times
You Asked
Hi Tom,
i am a beginner in Oracle. I want to lock the records while we update in a stored procedure. Meanwhile if any other user trying to fetch the same record which is locked, i should not allow him to fetch that record. As per my knowledge i can use "Select...for update of..." and "Update Skip Lock". "Update Skip Lock" will provide the records which are not locked and lock the records which were fetched. Now, my question is, If we fetch a record by a primary key and the second session also trying to pull the same record, what would the second query return to a cursor? Null?
Thanks
Bhanu.
and Connor said...
select for update
- successful person fetches the record(s) and locks them
- unsucessful person waits forever until the first person commits/rolls back.
select for update wait 'n'
- successful person fetches the record(s) and locks them
- unsucessful person waits for up to 'n' seconds, and will get an error if not successful before then
select for update nowait
- successful person fetches the record(s) and locks them
- unsucessful person will get an error immediately if not successful
select for update skip locked
- successful person fetches the record(s) and locks them
- unsucessful person also fetches the record(s), and then tries to get a lock on each one. Any record already locked is dumped from the result set. So they *wont* get an error, they might just get "no rows returned"
Hope this helps.
Is this answer out of date? If it is, please let us know via a Comment