can you lock part of a table ?

Posted by

I should stress that this is all well covered in the documentation, but it was something I stumbled upon just recently.

Its common knowledge that you can lock a table:

SQL> create table T 
  2   ( x int ) 
  3  partition by range ( x ) 
  4  ( 
  5    partition p1 values less than (10), 
  6    partition p2 values less than (20), 
  7    partition p3 values less than (30) 
  8  );

Table created.

SQL> lock table T in exclusive mode;

Table(s) Locked.

However, you can also selectively lock just partitions of a table as well, using the standard partition clause, for example:

Session 1

SQL> lock table T partition ( p1 ) in exclusive mode;

Table(s) Locked.

whilst the following is still possible in session 2:

SQL> insert into T values (20);

1 row created.


Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.