Oracle Tips and Tricks — David Fitzjarrell

October 20, 2008

Lock ‘Em Up

Filed under: General — dfitzjarrell @ 17:16

There has been, recently, a large number of requests to google.com about Oracle database locks. I’ve blogged before on locks and how to see who has which object locked, but I haven’t said much as to the nature and intent of locks. I think it’s about time that topic was addressed.

Oracle, in the on-line documentation, states:

“Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource — either user objects such as tables and rows or system objects not visible to users, such as shared data structures in memory and data dictionary rows.”

[To get a bit picky here, locks on shared data structures in memory are usually called latches, but they perform the same task so we’ll carry on with the discussion.]

So a lock, when taken, prevents ‘destructive interaction’ between transactions. What DOES that mean? Possibly an example between two sessions trying to modify data in the same table will illustrate (this would be a ‘blocking’ lock, and querying V$LOCK where block 0 would show session #2 blocked by session #1). Session #1 locks and modifies data in the EMP table by taking both a TX (transaction row-level) and a TM (row exclusive) lock:

SQL> select empno, ename, sal
  2  from emp;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7935 SMITH             900
      7369 SMYTHE            800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500

     EMPNO ENAME             SAL
---------- ---------- ----------
      7876 ADAMS            1100
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300
      7955 SMITH             900
      7956 SMYTHE            800
      7957 ALLEN            1600
      7958 WARD             1250
      7959 JONES            2975
      7960 MARTIN           1250
      7961 BLAKE            2850

     EMPNO ENAME             SAL
---------- ---------- ----------
      7962 CLARK            2450
      7963 SCOTT            3000
      7964 KING             5000
      7965 TURNER           1500
      7966 ADAMS            1100
      7967 JAMES             950
      7968 FORD             3000
      7969 MILLER           1300
      8000 SMITH             900
      8001 SMYTHE         1382.4
      8002 ALLEN          2764.8

     EMPNO ENAME             SAL
---------- ---------- ----------
      8003 WARD             2160
      8004 JONES          5140.8
      8005 MARTIN           2160
      8006 BLAKE          4924.8
      8007 CLARK          4233.6
      8008 SCOTT            5184
      8009 KING             8640
      8010 TURNER           2592
      8011 ADAMS          1900.8
      8012 JAMES          1641.6
      8013 FORD             5184

     EMPNO ENAME             SAL
---------- ---------- ----------
      8014 MILLER         2246.4
      8015 SMITH          1555.2
      8016 SMYTHE         1382.4
      8017 ALLEN          2764.8
      8018 WARD             2160
      8019 JONES          5140.8
      8020 MARTIN           2160
      8021 BLAKE          4924.8
      8022 CLARK          4233.6
      8023 SCOTT            5184
      8024 KING             8640

     EMPNO ENAME             SAL
---------- ---------- ----------
      8025 TURNER           2592
      8026 ADAMS          1900.8
      8027 JAMES          1641.6
      8028 FORD             5184
      8029 MILLER         2246.4

60 rows selected.

SQL>
SQL> update emp
  2  set sal = sal *.985
  3  where empno 
SQL> exec dbms_lock.sleep(60)

At this point we start session #2 and try to modify the same data in the same table:

SQL> select empno, ename, sal
  2  from emp;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7935 SMITH             900
      7369 SMYTHE            800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500

     EMPNO ENAME             SAL
---------- ---------- ----------
      7876 ADAMS            1100
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300
      7955 SMITH             900
      7956 SMYTHE            800
      7957 ALLEN            1600
      7958 WARD             1250
      7959 JONES            2975
      7960 MARTIN           1250
      7961 BLAKE            2850

     EMPNO ENAME             SAL
---------- ---------- ----------
      7962 CLARK            2450
      7963 SCOTT            3000
      7964 KING             5000
      7965 TURNER           1500
      7966 ADAMS            1100
      7967 JAMES             950
      7968 FORD             3000
      7969 MILLER           1300
      8000 SMITH             900
      8001 SMYTHE         1382.4
      8002 ALLEN          2764.8

     EMPNO ENAME             SAL
---------- ---------- ----------
      8003 WARD             2160
      8004 JONES          5140.8
      8005 MARTIN           2160
      8006 BLAKE          4924.8
      8007 CLARK          4233.6
      8008 SCOTT            5184
      8009 KING             8640
      8010 TURNER           2592
      8011 ADAMS          1900.8
      8012 JAMES          1641.6
      8013 FORD             5184

     EMPNO ENAME             SAL
---------- ---------- ----------
      8014 MILLER         2246.4
      8015 SMITH          1555.2
      8016 SMYTHE         1382.4
      8017 ALLEN          2764.8
      8018 WARD             2160
      8019 JONES          5140.8
      8020 MARTIN           2160
      8021 BLAKE          4924.8
      8022 CLARK          4233.6
      8023 SCOTT            5184
      8024 KING             8640

     EMPNO ENAME             SAL
---------- ---------- ----------
      8025 TURNER           2592
      8026 ADAMS          1900.8
      8027 JAMES          1641.6
      8028 FORD             5184
      8029 MILLER         2246.4

60 rows selected.

SQL>
SQL> update emp
  2  set sal = sal * 1.005
  3  where empno <= 8000;

The update process stops here, waiting for session #1 to commit its changes. Session #1 has an exclusive lock on the data we want to modify in session #2, so that session needs to wait until the lock is cleared (by a commit or a rollback) to effect any changes. As we continue on in both sessions we see that session #1 has ended its waiting period and committed its changes:

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select empno, ename, sal
  2  from emp;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7935 SMITH           886.5
      7369 SMYTHE            788
      7499 ALLEN            1576
      7521 WARD          1231.25
      7566 JONES         2930.38
      7654 MARTIN        1231.25
      7698 BLAKE         2807.25
      7782 CLARK         2413.25
      7788 SCOTT            2955
      7839 KING             4925
      7844 TURNER         1477.5

     EMPNO ENAME             SAL
---------- ---------- ----------
      7876 ADAMS          1083.5
      7900 JAMES          935.75
      7902 FORD             2955
      7934 MILLER         1280.5
      7955 SMITH           886.5
      7956 SMYTHE            788
      7957 ALLEN            1576
      7958 WARD          1231.25
      7959 JONES         2930.38
      7960 MARTIN        1231.25
      7961 BLAKE         2807.25

     EMPNO ENAME             SAL
---------- ---------- ----------
      7962 CLARK         2413.25
      7963 SCOTT            2955
      7964 KING             4925
      7965 TURNER         1477.5
      7966 ADAMS          1083.5
      7967 JAMES          935.75
      7968 FORD             2955
      7969 MILLER         1280.5
      8000 SMITH           886.5
      8001 SMYTHE         1382.4
      8002 ALLEN          2764.8

     EMPNO ENAME             SAL
---------- ---------- ----------
      8003 WARD             2160
      8004 JONES          5140.8
      8005 MARTIN           2160
      8006 BLAKE          4924.8
      8007 CLARK          4233.6
      8008 SCOTT            5184
      8009 KING             8640
      8010 TURNER           2592
      8011 ADAMS          1900.8
      8012 JAMES          1641.6
      8013 FORD             5184

     EMPNO ENAME             SAL
---------- ---------- ----------
      8014 MILLER         2246.4
      8015 SMITH          1555.2
      8016 SMYTHE         1382.4
      8017 ALLEN          2764.8
      8018 WARD             2160
      8019 JONES          5140.8
      8020 MARTIN           2160
      8021 BLAKE          4924.8
      8022 CLARK          4233.6
      8023 SCOTT            5184
      8024 KING             8640

     EMPNO ENAME             SAL
---------- ---------- ----------
      8025 TURNER           2592
      8026 ADAMS          1900.8
      8027 JAMES          1641.6
      8028 FORD             5184
      8029 MILLER         2246.4

60 rows selected.

SQL>

We can see that the only changes to see are those committed by session #1. Session #2 now has ‘free reign’ over that same data, and implements its changes:


31 rows updated.

SQL>
SQL> exec dbms_lock.sleep(60)

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select empno, ename, sal
  2  from emp;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7935 SMITH          890.93
      7369 SMYTHE         791.94
      7499 ALLEN         1583.88
      7521 WARD          1237.41
      7566 JONES         2945.03
      7654 MARTIN        1237.41
      7698 BLAKE         2821.29
      7782 CLARK         2425.32
      7788 SCOTT         2969.78
      7839 KING          4949.63
      7844 TURNER        1484.89

     EMPNO ENAME             SAL
---------- ---------- ----------
      7876 ADAMS         1088.92
      7900 JAMES          940.43
      7902 FORD          2969.78
      7934 MILLER         1286.9
      7955 SMITH          890.93
      7956 SMYTHE         791.94
      7957 ALLEN         1583.88
      7958 WARD          1237.41
      7959 JONES         2945.03
      7960 MARTIN        1237.41
      7961 BLAKE         2821.29

     EMPNO ENAME             SAL
---------- ---------- ----------
      7962 CLARK         2425.32
      7963 SCOTT         2969.78
      7964 KING          4949.63
      7965 TURNER        1484.89
      7966 ADAMS         1088.92
      7967 JAMES          940.43
      7968 FORD          2969.78
      7969 MILLER         1286.9
      8000 SMITH          890.93
      8001 SMYTHE         1382.4
      8002 ALLEN          2764.8

     EMPNO ENAME             SAL
---------- ---------- ----------
      8003 WARD             2160
      8004 JONES          5140.8
      8005 MARTIN           2160
      8006 BLAKE          4924.8
      8007 CLARK          4233.6
      8008 SCOTT            5184
      8009 KING             8640
      8010 TURNER           2592
      8011 ADAMS          1900.8
      8012 JAMES          1641.6
      8013 FORD             5184

     EMPNO ENAME             SAL
---------- ---------- ----------
      8014 MILLER         2246.4
      8015 SMITH          1555.2
      8016 SMYTHE         1382.4
      8017 ALLEN          2764.8
      8018 WARD             2160
      8019 JONES          5140.8
      8020 MARTIN           2160
      8021 BLAKE          4924.8
      8022 CLARK          4233.6
      8023 SCOTT            5184
      8024 KING             8640

     EMPNO ENAME             SAL
---------- ---------- ----------
      8025 TURNER           2592
      8026 ADAMS          1900.8
      8027 JAMES          1641.6
      8028 FORD             5184
      8029 MILLER         2246.4

60 rows selected.

SQL>

No destructive interaction between the sessions could occur; session #2 could not update the data locked by session #1 until that lock was released. Once the first lock was gone a second lock, by another session, could be taken so further updates could be processed. Were there a third session attempting to modify this same data it, too, would be locked as session #2 was before, preventing multiple sessions from updating the same data immediately on top of, and interfering with, the current transaction.

Locks can be a problem, however, especially when one session has locked data another session needs, and the waiting session has locked data the first session wants to modify. This is known as a deadlock, and later releases of Oracle detect such conditions and issue a rollback on the ‘newer’ transaction (here ‘newer’ means the transaction which didn’t lock the data its waiting upon but has data a transaction higher up the queue needs to access or modify). To give a representation of this:

— Transaction A modifies data in the EMP table
— Transaction B modifies data in the DEPT table
— Transaction A now needs to modify records in the DEPT table,
but transaction B has those records locked
— Transaction B now wants to modify records in the EMP table

that transaction A has locked
— Oracle will ‘rollback’ transaction B as it’s in the queue after
transaction A and holds data that transaction A needs to complete
its work

Older releases of Oracle (pre-9.0) will let a deadlock continue forever, so the DBA will need to handle such occurrences in 8.1.7.4 and earlier versions.

Some locks don’t affect anyone, such as shared row locks (taken by plain old SELECT statements); others, such as those taken by DDL statements, prevent any action against the affected object. Additionally, since DDL transactions implicitly commit before and after the statement execution the exclusive object lock is only active for a short period of time, which in many cases won’t noticeably affect other sessions.

Locks may be considered by some to be inconvenient, but they are necessary to preserve data integrity and provide transaction isolation (see the example above). And transaction isolation and data integrity are important if your data has any worth at all (and everyone’s data is worth something to their user community).

“Lock ’em up, Sheriff!”

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: