Locking questions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Locking questions

  1. #1
    Join Date
    Feb 2003
    Location
    Hong Kong
    Posts
    6

    Locking questions

    Two questions:

    1. I am writing a web application. I wonder if the following scenario is possible?

    First, page1.jsp connect to DB, select some rows and lock them up, disconnect, forward clients to page2.jsp.
    Then, page2.jsp connect to DB, update those rows and release the lock, disconnect.

    The only locking method method I know is "select ....for update". But it seems not applicable. Is there any other locking method in Oracle that can fit my situation?


    2. For "select.....for update", if my connection to DB is somehow lost before I can "commit" to release the lock, will the rows/tables be locked up forever? I read from other topics that there is a parameter called "sqlnet.timout" to set at server to prevent permanent locking. But I am not a DBA and hence cannot modify any server-setting. So is there any thing I can do in my application to set a timeout mechanism?

  2. #2
    Join Date
    May 2002
    Posts
    108
    1. dunno why you would want to disconnect when you forward to page2.
    (May be it is your requirement, but this will cause contention.)

    2. "Select ... for update" locks will be released if that session is disconnected.

    - Nandu
    Never give up !

    Nanda Kumar - Vellore

  3. #3
    Join Date
    Feb 2003
    Location
    Hong Kong
    Posts
    6
    Originally posted by nandu
    1. dunno why you would want to disconnect when you forward to page2.
    (May be it is your requirement, but this will cause contention.)
    hm, you mean I should pass the Connection object to page2?

    Originally posted by nandu

    2. "Select ... for update" locks will be released if that session is disconnected.

    - Nandu
    thx~ Then, consider another case, if the application executes a "select...for update" statement, and wait for user's input before it executes the "commit" command. Will the rows/tables be locked up forever, until the user makes his move?

  4. #4
    Join Date
    May 2002
    Posts
    108
    1. I am writing a web application

    hm, you mean I should pass the Connection object to page2?
    hmmm... u want to write a web application?!!! Heard of Connection pooling?!
    thx~ Then, consider another case, if the application executes a "select...for update" statement, and wait for user's input before it executes the "commit" command. Will the rows/tables be locked up forever, until the user makes his move?
    Yes, will do(As a result of bad design/deployment) until you have a DB profile associated which will expire the connection or a client implementation which will Timeout the connection.

    - Nandu
    Never give up !

    Nanda Kumar - Vellore

  5. #5
    Join Date
    Aug 2001
    Posts
    267
    The rows never be locked permanently. Locks are based on session. If the session got disconnected or killed all locks will be released. Why not you try a dummy column on the same table to identify what rows are selected . And based on the dummy column status you can identify the rows in your second session.
    Raghu

  6. #6
    Join Date
    Feb 2003
    Location
    Hong Kong
    Posts
    6
    Originally posted by nandu


    hmmm... u want to write a web application?!!! Heard of Connection pooling?!

    - Nandu
    oh, sorry.....you are right...in fact I am using Connection pooling. But I just regard it as a way to save time(I don't know, perhaps time for object-construction, or time for DB-login, or both. I am not quite familiar with what my app server actually does behind the scences). I haven't thought of making use of it to transfer something from page1 to page2.

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    First rule of contention:

    Never return control to the user while resources are locked.

    'nuff said,

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

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