Results 1 to 5 of 5

Thread: Lock Table

  1. #1
    Join Date
    Jun 2003
    Posts
    294

    Unanswered: Lock Table

    Hello There

    I have to LOCK a table before selecting, in Oracle 8i, to get the max val of a column. I mean, Nobody can get the same value at the same time (is some people is trying to use the table). (I can't use a sequence)


    the sentence is like this:


    SELECT NVL(MAX(TO_NUMBER(VAL_COLUMN))+1,1) INTO luMaxConfig FROM TABLE_1;

  2. #2
    Join Date
    Feb 2004
    Posts
    108
    In such cases sequence is the most reliable strategy.
    Since you can't use sequence, try using your MAX+1 login in Pre-Insert DB trigger. You cannot "lock" a record in such a way that nobody can even select it.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    There is the LOCK TABLE statement to lock the WHOLE table:

    LOCAK TABLE t IN EXCLUSIVE MODE;

    But that is a really nasty idea!

    By the way, why can you not use a sequence?

  4. #4
    Join Date
    Jun 2003
    Posts
    294

    Smile

    It didn't work for selecting

    I have to do It in the case that 2 or more users try to insert in a table at the same time. The problem with the sequences is that the Client does't permit them.

    Thanks

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, you are right, as pinakdb says you cannot lock a table against a SELECT - I was thinking of blocking any attempts to INSERT. If the table is locked in excludive mode by one session then no other session can insert into it.

    Your client appears to be very foolish: what is their justification for outlawing sequences?

Posting Permissions

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