Lock Duration
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Lock Duration

  1. #1
    Join Date
    Dec 2001
    Posts
    221
    I'm havin one pl/sql script where i'm firing an update statement. What i want is if the script is not completed its execution within a certain duration of time ( say within 60secs) i need to rollback it. Cause i'm not affording the locks for such a long duration.

    Santosh Jadhav
    8i OCP DBA

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by sjadhavdba
    I'm havin one pl/sql script where i'm firing an update statement. What i want is if the script is not completed its execution within a certain duration of time ( say within 60secs) i need to rollback it. Cause i'm not affording the locks for such a long duration.

    You don't rollback scripts, you rollback transactions. Do you want to abort the script after 30' if it has not finished by then? It's a bit risky to do such things.




  3. #3
    Join Date
    Dec 2001
    Posts
    221
    thanx julian

    i know we rollback transactions. thats why i've mentioned "update statement".

    and thats exactly what i want . do u have any answer
    Santosh Jadhav
    8i OCP DBA

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Is the version 9i? Then you may use the WAIT N clause.
    If V < 9i, then tell me do you run it as a job or do you start the scrip manually.


  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    wait N is for select for update isnt it? wait N seconds if you cant get the row then return error...

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Yes, WAIT N does tell how long should you wait to obtain a lock on a row that is currently locked by some other transaction, you can not use it to limit for how long you should keep the lock on a row.

    Nevertheless, it is a very strange requirenment to limit the time in which a particular transaction should execute and to roll it back if this time is exceeded. I think the closest think to meet this requirement would be to limit the CPU_PER_CALL in the calling user's profile. And yes, I know it is not the same, it is just the closest thing I could think of.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    He can set the resource plan directive parameter MAX_ESTIMATED_EXEC_TIME. If more than 30', then the operation will not start.


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