Cristian Dudila <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] wrote: >> Cristian Dudila <[EMAIL PROTECTED]> writes: >> >>> Hi, >>> >>> We use derby for an application and we need to lock some rows. The >>> problem is that we would like to know if this rows are already locked. >>> Using TRANSACTION_REPEATABLE_READ we can lock this rows ( using SELECT >>> )but we don't know if this rows was already locked. Using "SELECT FOR >>> UPDATE" derby returns an exception that tell us that the row is >>> locked, but unfortunately ( for us ) we can lock only one row using >>> "SELECT FOR UPDATE". >> I think it would be better if you could describe what it is you're >> trying to achieve. > I want to warn the user if a row is locked in derby database by > another user.
You will get an exception if you try to access that row and your transaction cannot get the lock within some timeout (which you can specify, I think). Note that the lock isn't kept by another user, but by another transaction. So if you limit the duration of all transactions accessing this particular row, there should be no need for a warning... >>> Is there a possibility to lock multiple rows, using one connection and >>> to receive an exception or message ( from derby ) if this rows are >>> locked by somebody else before ? >> Maybe I don't understand what you're trying to do, but in general it >> is not a good idea to use the database to communicate between >> threads. The database tries its best to hide the fact that other >> threads (connections) are accessing the data. >> > With TRANSACTION_REPEATABLE_READ using a SELECT we will have a shared > lock in derby, so we can read data but not to change it( in case of > update, from another database connection, we receive an exception that > the lock cannot be obtained ). > Is there a possibility to check in derby if a row is locked( except to > try a dummy UPDATE on that row ) ? I don't think that will work. If another transaction has locked the row, your update will just wait for the lock to be released. It will only give an exception if the lock request times out, I think. You can dump all locks from a system table (check the manuals for the details). But even if this shows that the row isn't locked there is no guarantee that your transaction will get the lock, (another transaction may beat you to it). -- dt
