Home » SQL & PL/SQL » SQL & PL/SQL » How to use Optimistic locking in my Package (Oracle 12c Release 2)
Re: How to use Optimistic locking in my Package [message #673688 is a reply to message #673686] Mon, 03 December 2018 04:59 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Oracle locks rows when one of the following happens (*):
1) you update a row
2) you delete a row
3) you select a row for update

Oracle doesn't lock the whole table in any of those cases - it just locks the rows that match the where clause on the statement.
Those rows are then held locked by your session until
1) you issue a commit
2) you issue a rollback
3) an error occurs that causes an implicit rollback (they always do this but exception handlers can stop it)

While you hold a lock any other session that tries to lock the same rows with select/update/delete will hang. The exception to that is if you add the NOWAIT (or WAIT n) keyword to the select for update - in that case the 2nd session will get an error telling them the row is locked.

As for the last question - you need to lock rows for as long as necessary in order to ensure the correct answer, but no longer than that or you'll get users complaining they can't do anything.
If the procedure is part of a larger transaction then you may well want to lock the rows sooner.
And the fact that the procedure could be part of a larger transaction (say you're transferring money between two accounts) means you really shouldn't have a commit in there.

Lets talk about optimistic locking for a second - the reason it exists is because of connection pools. A Connection pool holds a number of connections (each it's own oracle session) to the DB. When the end user makes a request it takes a connection from the pool and uses that to talk to the DB. Each time a request is made it'll make use of the first unused connection, or create a brand new one if they're all being used. The important point here is that an end user can do one action using database session 1 and then their next action will use database session 2 (or 3 or 99). And the end user has no control over this.
So when that happens you can't use pessimistic locking to hold rows over multiple actions as it's the DB session holding the lock not the end user session.
Optimistic locking was invented to get round that problem.

If you're going to use pessimistic locking over multiple procedures you need to be aware of that (inside a single procedure it's never an issue).

(*) - insert doesn't lock rows, but it does hold a lock on any unique/primary keys on the table so no one else can enter the same set of key values until your transaction is complete - if they try they will hang until you're done and then get an error unless you did a rollback.
Previous Topic: Charater set
Next Topic: SQL Help
Goto Forum:
  


Current Time: Thu Mar 28 04:01:58 CDT 2024