Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Posts
    5

    Unanswered: Table Lock Mechanism - Specify

    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 ?

    Or is there any other way of achieving this as the locking at the session level OR via SQL is not a possiblity.

    Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >the problem is with dirty reads
    HUH?
    Exactly what do you mean by "dirty reads"?
    What problem are you really trying to solve?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Posts
    5
    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.

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    use an oracle sequence instead of table xx to generate your sequence numbers

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    In Oracle readers of data (SELECT) are NEVER "blocked".
    It appears to me you have a serious design flaw in the application.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jun 2004
    Posts
    5
    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 at the DB level. Has anyone ever done anything like this before ?
    Last edited by rshivagami; 06-21-04 at 15:11.

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You should be able to do a select for update as this will lock the row BUT you will get serious performance problems as sessions will start waiting in a queue to access the row. I would suggest you go for the oracle sequence as otherwise this problem will likely keep coming back.

    Alan

  8. #8
    Join Date
    Jun 2004
    Posts
    2

    Lightbulb Have code level check

    Why don't you check for the row being updated. Do a select for 100 and if 100 doesnot exist in the table xx, raise an error.

    HTH
    -Engi

Posting Permissions

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