Manual Locking
Table locks can be assigned manually using the LOCK TABLE command.
The command is normally used by application programmers. If an application
should behave in a manner that requires additional locking being performed,
this command could prove very useful. However, as a general guideline avoid
high locking levels. Manual locking overrides automatic locking and permits or
denies access to a table or view by other users for the duration of your
operation.
|
Lockmode Clause
|
Description
|
|
ROW
SHARE (RS)
|
ROW SHARE permits concurrent access to the locked table but
prohibits users from locking the entire table for exclusive access.
|
|
ROW
EXCLUSIVE (RX)
|
Is the same as ROW SHARE, however it also
prohibits locking in SHARE mode.
|
|
SHARE
(S)
|
SHARE permits concurrent queries but
prohibits updates to the locked table.
|
|
SHARE
ROW EXCLUSIVE (SRX)
|
SHARE ROW EXCLUSIVE is used to permit
queries, but prohibits locking the table in SHARE mode or from updating rows.
|
|
EXCLUSIVE
(X)
|
EXCLUSIVE permits queries on the locked
table, but prohibits any other activity on it.
|
Example Row Share:
|
Moon
|
Manik
|
|
SQL>
lock table tloc in row share mode;
|
|
|
SQL>
update tloc
2
set id=7
3
where id=8;
|
SQL>
update moon.tloc
2
set id=2
3
where id=7;
|
|
|
SQL>
commit;
|
|
|
SQL>
lock table moon.tloc in exclusive mode;
Waiting
|
|
SQL> commit;
|
|
|
|
Table(s)
Locked.
|
Example of Row Exclusive:
|
Moon
|
Manik
|
|
SQL>
lock table tloc in row share mode;
|
SQL>
lock table moon.tloc in share mode;
|
|
|
SQL>
update moon.tloc
2
set id=0
3
where id=9;
|
|
SQL>
update tloc
2
set id=7
3
where id=2;
Waiting
|
|
|
|
SQL>
commit;
|
|
1
rows updated.
|
|
Example of Share:
|
Moon
|
Manik
|
|
SQL>
lock table tloc in share mode;
|
|
|
SQL>
update tloc
2
set id=1
3
where id=0;
1
row updated.
|
SQL>
update moon.tloc
2
set id=4
3
where id=7;
Waiting
|
|
SQL>
commit;
|
|
|
|
2
rows updated.
|
Example of Share row Exclusive:
|
Moon
|
Manik
|
|
SQL>
lock table tloc in share row exclusive mode;
|
|
|
|
SQL>
lock table moon.tloc in row share mode;
Table(s)
Locked.
|
|
|
SQL>
lock table moon.tloc in share mode;
Waiting
|
|
SQL>
commit;
|
|
|
|
Table(s)
Locked.
|
Example of Exclusive:
|
Moon
|
Manik
|
|
SQL>
lock table tloc in exclusive mode;
|
|
|
|
SQL>
lock table moon.tloc in row share mode;
Or
SQL>
lock table moon.tloc in row exclusive mode;
Or
SQL>
lock table moon.tloc in share mode;
Or
SQL>
lock table moon.tloc in share row exclusive mode;
|
|
SQL>
commit;
|
|
|
|
Table(s)
Locked.
|
Killing User Session:
|
Moon
|
Manik
|
Sys
|
|
|
SQL>
update moon.tloc
2
set id=11
3
where id=9;
1
row updated.
|
|
|
SQL>
update tloc
2
set id=12
3
where id=9;
Waiting
|
|
|
|
|
|
SQL>
SELECT USERNAME, SID, SERIAL#
2
FROM V$SESSION;
(A partial output has been displayed)
USERNAME SID SERIAL#
MANIK 135 6
MOON 141 4
|
|
|
|
SQL> alter system kill session '135,6';
|
|
1 row updated.
|
|
|
|
|
SQL>
commit;
ERROR at line 1:
ORA-00028: your session has been killed
|
|
|