Locking questions

[wcleung9]

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?

[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.)

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

- Nandu

[wcleung9]

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?

[nandu]

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

[raghud]

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.

[wcleung9]

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.

[chrisrlong]

First rule of contention:

Never return control to the user while resources are locked.

'nuff said,

- Chris