Loc Conflict

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

 

 

Comments