Learning is not a spectator sport

June 8, 2013

can you lock part of a table ?

Filed under: Uncategorized — connormcdonald @ 1:15 pm

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.


  1. Hi there, after reading this remarkable paragraph i am also happy to share my experience here with mates.

    Comment by wordpress — July 16, 2013 @ 9:23 am

  2. Wonderful blog! I found it while searching on Yahoo News.

    Do you have any tips on how to get listed in Yahoo News?
    I’ve been trying for a while but I never seem to get there! Thanks

    Comment by i want to lose 10 pounds in a month — July 26, 2013 @ 10:36 am

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The WordPress Classic Theme. Blog at WordPress.com.


Get every new post delivered to your Inbox.

Join 94 other followers

%d bloggers like this: