The objective of this post is to explain how to lock a record on a Oracle Database, using a select statement.
Introduction
The objective of this post is to explain how to lock a record on a Oracle Database, using a select statement. This allows us to lock a record without the need to change it.
Although this may not seem a very common use case, the truth is that we may need it for more complex programs (implemented, for example, in PL/SQL), where we need to do some complex validations before deciding to update a certain register, and we need to maintain it locked during those validations, so no other transaction can concurrently modify it.
We will be using SQL Developer to interact with the database and to perform the queries for this tutorial.
Locking the same record
As usual, we will start by creating a simple dummy table, to hold our data. Check the create example bellow.
create table Worker( WorkerID varchar(20), WorkerName varchar(255), WorkerJob varchar(255), CONSTRAINT PK_Worker PRIMARY KEY (WorkerID) );
After that, we will insert some dummy records, so we have data to work on. We will insert three records and then commit the transaction. You can use the commit button on the SQL Developer menu or explicitly commit the transaction with a SQL statement.
insert into worker values ('1','John', 'nurse');
insert into worker values ('2','Grace', 'farmer');
insert into worker values ('3','Smith', 'doctor');
Now we will open a new unshared worksheet, in order for us to test the SQL statements in independent transactions. Check figure 1 on how to do it. You can read more about SQL Developer unshared worksheets in this previous post.
Figure 1 – Opening a new unshared worksheet.
Now, the first thing we are going to do is executing a regular select in both of the worksheets. We will do a select to get the record where the WorkerID is equal to 1. Don’t do any commits or rollbacks, just execute the same query in both worksheets.
select * from worker where WorkerID = 1;
As expected, there is no problem with concurrence and we will receive the record in both transactions (with the WorkerName equal to “John”).
Now, we will go back to the first worksheet (the same where we created the table) and add a “for update” clause in the end of the select statement, as indicated bellow. Again, don’t do any commit or rollback.
select * from worker where WorkerID = 1 for update;
When we do this, we are locking the records of the result set, without the need for updating them [1]. In other words, we are explicitly locking the records. Of course that once we have them locked, we can update them if we want. The locks on the records are released when we do the next commit or rollback [1].
So, to test if the locks are working, we are now going to the second worksheet (the unshared worksheet we opened). There, if we do a regular select, without the “for update sentence”, we will get the record without any problem.
select * from worker where WorkerID = 1;
But then, if we repeat the select, now with the “for update” sentence in the end, it will hang, since other transaction has the record with WorkerID equal to 1 locked, and we are trying to lock it again.
select * from worker where WorkerID = 1 for update;
To unlock it, just go back to the first worksheet and commit or rollback the transaction. Now, go back to the unshared worksheet and the SQL statement should have executed, returning the desired record.
Locking different records
One important thing to keep in mind is that only the records that are part of the result set are locked by the transaction. So, different transactions can do “select for update” in different records.
To test this, go to the first worksheet and select both record 1 and 3, with the following select sentence:
select * from worker where WorkerID = 1 or WorkerID = 3 for update;
Now go to the unshared worksheet and try to lock record 2.
select * from worker where WorkerID = 2 for update;
It should work and return a result, since the record with WorkerID equal to 2 was not locked by the first transaction. Now, if in the same unshared worksheet we try to lock the record with Worker ID equal to 1 or the record with Worker ID equal to 3 (or both) it will hang until we commit or rollback the transaction from the first worksheet.
Related content
References
[1] https://www.techonthenet.com/oracle/cursors/for_update.php