Locking and Blocking Basics

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s