DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Table Level Locking

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    Table Level Locking

    I am running Oracle9.2 on Linux - can I specify locking at the table level ? If yes, then how and if not, then can I do it at the schema level(so all tables in the schema will acquire that locking mechanism)?

    Thanks.

  2. #2
    Join Date
    Oct 2002
    Posts
    807
    No idea what you are trying to do.

    But a "alter table table_name enable table lock;" will lock the table.

  3. #3
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    We have an issue with Data contention and the problem is with dirty reads - normally to solve this, one would use SELECT...FOR UPDATE in the SQL to acquire a Lock while accessing the table. Instead of doing this, I was wondering if it is possible to specify the locking mechanism at the table definition or schema definition time so that any SELECT query against that table will automatically put the required lock on that table ?

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by rshivagami
    the problem is with dirty reads
    Oracle doesn't have dirty reads. You always get a consistant view of the data.

    Introducing locking is a great way to limit your scalability and only allow one user to use the entire table at a time.
    Jeff Hunter

  5. #5
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Basically what is happening here is that there is a table XX and I read from a value from this table(lets say 100) and use that value as to insert into another table YY as a Primary key, increment the value to 101 in table xx, and then do a commit. Now after I have read from XX but before doing my commit, another instance of the appserver is reading from table xx(the same value 100) and trying to insert it into table YY and I am running into 'Primary Key Violated on Table YY' errors.
    Hence I was wondering if I can put a lock on Table XX (the sole purpose of this table is to store values that will be used as Primary keys) as the code cannot be changed anymore...

    Any ideas/suggestions will be greatly helpful.
    Thanks.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Sounds like a great single user system.
    Jeff Hunter

  7. #7
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    We acquired this table from someone else and Tabel XX is used like a sequence - Using oracle sequences was my original suggestion but that involves lot of code change, so I was wondering if it is possible from the DB end. Has anyone ever done anything like this before ?

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You don't need to lock the table.

    When the first session already acquired a lock on the row by executing the command, "select run_num from t10 for update;", the 2nd session will wait till the 1st session commits/rollbacks.

    Pessimistic locking mechanism works fine in your case.

    Tamil

  9. #9
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Thanks or the reply Tamil. The thing is that we are not using a SELECT FOR UPDATE and the code cannot be changed anymore. The code is just using a SELECt statement and another UPDATE. So I am looking to achieve it at the database end.

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Not possible at the DB level unless serializable is set to true;

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width