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 <= 8000;
31 rows updated.
SQL>
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!"
0 comments:
Post a Comment