One of the most important aspects of designing a database application is to consider concurrency; multiple users or sessions simultaneously accessing and modifying the data. This is also one area that the various relational database platforms differ so it’s important to understand how it is handled in the one you work with. There are various aspects to concurrency and this post looks at the basics of row locking and blocking within Oracle.
Before we get started I’d like to mention a couple of points with regard to how Oracle works:
- Locking is done at the row level. Yes, it’s possible to issue a LOCK TABLE statement but if you are simply modifying data then you will only be locking individual rows. There is no locking at the block or extent levels. Furthermore, Oracle does not escalate a series of row level locks to a table lock.
- Oracle does not support dirty reads of data, which means that changes made by one session will only be visible to other sessions after a COMMIT has been issued. There is simply no facility in Oracle to perform a dirty read even if you wanted to do so.
The following examples all use the table and data defined below and were run under Oracle 11.2.0.3:
CREATE TABLE lock_test (id NUMBER(10) NOT NULL ,cat NUMBER(2) NOT NULL ,padding CHAR(30) NOT NULL) / INSERT INTO lock_test (id, cat, padding) SELECT ROWNUM , MOD(ROWNUM,5) , 'X' FROM dual CONNECT BY level <= 10 / COMMIT / ALTER TABLE lock_test ADD CONSTRAINT lock_test_pk PRIMARY KEY (id) /
Example 1
Let’s start with a simple test of a session updating one row whilst another session queries that same row before the update has been committed:
Session 1
SQL>UPDATE lock_test 2 SET cat = 99 3 WHERE id = 5 4 / 1 row updated.
Session 2
SQL>SELECT *
2 FROM lock_test
3 WHERE id = 5
4 /
ID CAT PADDING
---------- ---------- ------------------------------
5 5 X
So, since the change in session 1 had not been committed session 2 does not see those changes and the value of the CAT column is 5 and not 99. However, from a locking and blocking perspective, whilst session 1 has modified the data and presumably taken out a lock on the row, session 2 was still able to query it, albeit to obain the pre-committed data. This example illustrates how sessions reading data are not blocked by sessions writing data in Oracle. This is a very important aspect of how locking and blocking works in Oracle; it is very difficult to block readers of data. This situation is what gives rise to the type of problem I described in my very first blog post.
Example 2
So, let’s see what would happen if session 2 tries to modiy the same row that session 1 is modifying:
Session 1
SQL>UPDATE lock_test 2 SET cat = 99 3 WHERE id = 5 4 / 1 row updated.
Session 2
SQL>UPDATE lock_test 2 SET cat = 98 3 WHERE cat = 5 4 /
Ummm… well, that’s not easy to show in a blog post. Anyway, session 2 appears to have simply stopped. It’s being blocked by the uncommitted transaction in session 1. Once we go into session 1 and issue a COMMIT (or a ROLLBACK for that matter) then session 2 reports:
1 row updated.
If we repeat the test, and while session 2 is blocked, we can see the details of the locks causing session 2 to wait via the data dictionary view DBA_WAITERS.
SELECT *
FROM dba_waiters
WHERE holding_session = SYS_CONTEXT('userenv','sid')
/
If we run this query in session 1, the session holding the lock, we get:
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
--------------- --------------- ------------ ---------- -------------- ---------- ----------
131 200 Transaction Exclusive Exclusive 131095 982
So, session 1, which is SID 200, is holding an exclusive lock that is being requested by session 2, which is SID 131. We can see the details of the lock by querying v$lock:
SELECT * FROM v$lock WHERE id1 = 131095 AND id2 = 982 /
which gives us the following rather cryptic details:
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ----- -- ------- ------- ------ -------- ------ ------ 000007FF3D8DAE98 000007FF3D8DAEF0 131 TX 131095 982 0 6 995 0 000007FF3BCE6938 000007FF3BCE69B0 200 TX 131095 982 6 0 1010 1
I don’t want to delve into the Oracle’s locking internals for this post so for now I’ll just note that the above view contains two rows, one for the holder of the lock (session 1) and one for the requester (session 2). The SIDs of the sessions are shown as is the time the lock has been held and whether the lock is blocking others. Very useful details when troubleshooting locking and blocking problems in a system.
A very important point to understand with how Oracle works is that session 2 will remain blocked until session 1 issues a commit or rollback. There is no automatic timeout. This feature in particular has significant application design implications. For example, you probably need to think very carefully when designing long running batch processes that will modify data which will operate concurrently with short OLTP-type operations. If your batch process obtains locks on rows that an OLTP session requires then you will be facing end-user complaints of bad performance!
Example 3
Back to our original tables. Let’s repeat the insert test again but alter the selection criteria of the row that is updated. Rather than modifying the row where the ID value is 5, we’ll modify the row where the CAT value is 5:
Session 1
SQL>UPDATE lock_test 2 SET cat = 99 3 WHERE cat = 5 4 / 1 row updated.
Session 2
SQL>UPDATE lock_test 2 SET cat = 98 3 WHERE cat = 5 4 /
Once again session 2 is blocked. If we commit the transaction in session 1 then session 2 reports:
0 rows updated.
Session 2 was blocked by session 1 as it wanted to update the same row based on the pre-existing, committed data. However, when session 1 committed its changes the row no longer satisfied the selection criteria for the update session 2 was performing so session 2 ended up modifing no rows.
What is interesting in this example was that session 2 initially attempted to obtain a lock on the row and was blocked by session 1. When session 1 released that lock, session 2 decided that it didn’t want to update the row since it no longer satisified the update criteria so it actually never needed to obtain any locks. Such are the complexities when dealing with concurrency… get used to it as it will be an important consideration in any multi-user database.
Example 4
So what would happen if session 2 tried to perform an update using a selection criteria that would include uncommitted rows from session 1? Let’s find out:
Session 1
SQL>UPDATE lock_test 2 SET cat = 99 3 WHERE cat = 5 4 / 1 row updated.
Session 2
SQL>UPDATE lock_test 2 SET cat = 98 3 WHERE cat = 99 4 / 0 rows updated.
Session 2 returned immediately and reported that it didn’t update any rows even though session 1 had an uncommitted change that would have satisified the selection criteria for session 2. Logically what happened was that session 2 read the data in the table, ignoring the uncommitted changes, and based its update on that view of the data… and that view did not have a row with a CAT value of 99. Since session 2 did not attempt to modify any rows that were locked by session 1 it was not blocked by session 1.
Example 5
Enough of updates, let’s take a look at inserts next. This time we’ll start off with 2 sessions inserting a single row into the table:
Session 1
SQL>INSERT INTO lock_test 2 (id, cat, padding) 3 VALUES 4 (11, 99, 'X') 5 / 1 row created.
Session 2:
SQL>INSERT INTO lock_test 2 (id, cat, padding) 3 VALUES 4 (12, 98, 'X') 5 / 1 row created.
Nothing unexpected here. Both sessions inserted a row without any problem since locking, and hence blocking, is done at the row level and not at a table, extent or block level.
Example 6
Let’s modify the example such that both attempt to insert the same set of values:
Session 1
SQL>INSERT INTO lock_test 2 (id, cat, padding) 3 VALUES 4 (11, 99, 'X') 5 / 1 row created.
Session 2:
SQL>INSERT INTO lock_test 2 (id, cat, padding) 3 VALUES 4 (11, 99, 'X') 5 /
Again, not easily llustrated in a blog post but session 2 is blocked by session 1. The reason? The uniqueness constraint on the table prevents multiple rows having the same ID value. So why has Oracle blocked session 2 and not simply aborted the operation? Session 1 hasn’t actually committed its insert so Oracle doesn’t know for certain if there is a problem. If we were to issue a commit in session 1 then session 2 would report:
INSERT INTO lock_test * ERROR at line 1: ORA-00001: unique constraint (DEVELOPER.LOCK_TEST_PK) violated
However, if session 1 issued a rollback instead of a commit then session 2 would report:
1 row created.
As per our previous blocking examples, session 2 will remain blocked until session 1 performs a commit or rollback.
Example 7
Onto deletes then. The situation where two sessions both try to delete the same row probably causes no surprises:
Session 1
SQL>DELETE 2 FROM lock_test 3 WHERE id = 5 4 / 1 row deleted.
Session 2
SQL>DELETE 2 FROM lock_test 3 WHERE id = 5 4 /
Session 2 is blocked by session 1 as it’s trying to obtain a lock that session 1 already has. Once session 1 issues a commit then session 2 reports:
0 rows deleted.
Obviously session 1 has already deleted the row that session 2 was trying to delete so session 2 can’t delete it after all.
So, hopefully the above examples illustrates the basics of locking and blocking in Oracle, which are necessary features in any multi-user database system. These details are important to understand if you want to develop high performance systems. Time spent waiting whilst being blocked is essentially wasted time from an end user perspective so it’s important to minimise this.
Mark Hoxey 