On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Monday, September 22, 2008

Lock Table

Allowed:Prohibited:Other Notes:ROW SHARE (SHARE UPDATE*)ROW EXCLUSIVESHARESHARE ROW EXCLUSIVEEXCLUSIVE
Concurrent accessLocking for exclusive access
YesYes---

Locking in SHARE modeAutomatically obtained for DML-Yes---
Concurrent queriesUpdates
--Yes--
Others to look at rowsOthers from locking w/SHARE. Updating rowsUsed to look at a whole table---Yes-
QueriesAnything else
----Yes



* kept for backward compatilibity



  • Some locks can be placed at the same time. Other locks allow only one. (??)
  • Remains locked until you commit or rollback.
  • Never prevents queries
  • 'View' locks the base tables of the view.
  • If view is part of a hierarchy, then it must be the root.

The table or view
  1. must be in your own schema or
  2. you have LOCK ANY TABLE or
  3. you have any object privilege on the table or view.
LOCK TABLE
[ schema. ] { table | view } [ { PARTITION (partition) | SUBPARTITION (subpartition) } | @ dblink ] [,
[ schema. ] { table | view } [ { PARTITION (partition) | SUBPARTITION (subpartition) } | @ dblink ] ]...
IN lockmode MODE
[ NOWAIT ] ;

If you specify [SUB]PARTITION, then an implicit lock of the same type is first acquired on the table except for:
  • SHARE: acquires an implicit ROW SHARE lock on the table.
  • EXCLUSIVE: acquires an implicit ROW EXCLUSIVE lock on the table.

dblink - only if distributed functionality(??). All must be on the same database.

NOWAIT a message returns if other locks. Otherwise, waits until table available, locks it before returning control.

No comments:

Post a Comment