The pessimistic locking method would be put into action the instant before a user modifies a value on the screen. For example, a row lock would be placed as soon as the user indicates their intention to perform an update on a specific row that they have selected and have visible on the screen (by clicking a button on the screen, say). That row lock would persist until the application applied the user’s modifications to the row in the database and committed.
Pessimistic locking is useful only in a stateful or connected environment—that is, one where your application has a continual connection to the database and you are the only one using that connection for at least the life of your transaction. This was the prevalent way of doing things in the early to mid-1990s with client/server applications. Every application would get a direct connection to the database to be used solely by that application instance. This method of connecting, in a stateful fashion, has become less common (though it is not extinct), especially with the advent of application servers in the mid to late 1990s.
Assuming you are using a stateful connection, you might have an application that queries the data without locking anything:
$ sqlplus scott/tiger@PDB1
SQL> select empno, ename, sal from emp where deptno = 10;
EMPNO ENAME SAL
———- ———- ———-
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
Eventually, the user picks a row they would like to update. Let’s say in this case, they choose to update the MILLER row. Our application will, at that point (before the user makes any changes on the screen but after the row has been out of the database for a while), bind the values the user selected so we can query the database and make sure the data hasn’t been changed yet. In SQL*Plus, to simulate the bind calls the application would make, we can issue the following:
SQL> variable empno number
SQL> variable ename varchar2(20)
SQL> variable sal number
SQL> exec :empno := 7934; :ename := ‘MILLER’; :sal := 1300; PL/SQL procedure successfully completed.
Now in addition to simply querying the values and verifying that they have not been changed, we are going to lock the row using FOR UPDATE NOWAIT. The application will execute the following query:
SQL> select empno, ename, sal from emp where empno = :empno and decode( ename, :ename, 1 ) = 1and decode( sal, :sal, 1 ) = 1for update nowait;
EMPNO ENAME SAL
———- ———- ———-
7934 MILLER 1300
Note Why did we use “decode( column, :bind_variable, 1 ) = 1”? It is simply a shorthand way of expressing “where (column = :bind_variable OR (column is NULL and :bind_variable is NULL)”. You could code either approach; the decode() is just more compact in this case, and since NULL = NULL is never true (nor false!) in SQL, one of the two approaches would be necessary if either of the columns permitted NULLs.
The application supplies values for the bind variables from the data on the screen (in this case, 7934, MILLER, and 1300) and requeries this same row from the database, this time locking the row against updates by other sessions; hence, this approach is called pessimistic locking. We lock the row before we attempt to update because we doubt—we are pessimistic—that the row will remain unchanged otherwise.
Since all tables should have a primary key (the preceding SELECT will retrieve at most one record since it includes the primary key, EMPNO) and primary keys should be immutable (we should never update them), we’ll get one of three outcomes from this statement:
•\ If the underlying data has not changed, we will get our MILLER row back, and this row will be locked from updates (but not reads) by others.
•\ If another user is in the process of modifying that row, we will get an ORA-00054 resource busy error. We must wait for the other user to finish with it.
•\ If, in the time between selecting the data and indicating our intention to update, someone has already changed the row, then we will get zero rows back. That implies the data on our screen is stale. To avoid the lost update scenario previously described, the application needs to requery and lock the data before allowing the end user to modify it. With pessimistic locking in place, when User2 attempts to update the telephone field, the application would now recognize that the address field had been changed and would requery the data. Thus, User2 would not overwrite User1’s change with the old data in that field.
Once we have locked the row successfully, the application will bind the new values, issue the update, and commit the changes:
SQL> update emp
set ename = :ename, sal = :sal where empno = :empno;1 row updated.
SQL> commit; Commit complete.
We have now very safely changed that row. It is not possible for us to overwrite someone else’s changes, as we verified the data did not change between when we initially read it out and when we locked it—our verification made sure no one else changed it before we did, and our lock ensures no one else can change it while we are working with it.
Leave a Reply