Re: Error -246

In article <2p1ddq$g0e@emory.mathcs.emory.edu>, jmathwig@STL-03SIMA.ARMY.MIL (Jim Mathwig) writes: |> Hi All, |> |> Need assistance with this row vs. table locking issue! |> |> When executing the following query: |> |> "SELECT rowid, * from table_name WHERE rowid = 425 FOR UPDATE" |> |> another user get an error -246, when attempting to update another record |> from this same table. |> |> Thanx in advance! |> |> ============================================================================ |> jmathwig@stl-03sima.army.mil (Jim Mathwig) |> ITT - Fderal Services Corp. 805-928-4371 |> Santa Maria, Cal. |> ============================================================================ This is not a row vs table locking issue. It is a locking issue. This particular error indicated that another has requested a lock on this entity, be it row or page (depends on how you built the table.) Assuming on-line engine with database logging, ISOLATION LEVEL COMMITTED READ, ROW LOCKING: The above SELECT FOR UPDATE is how you let others that you want that row. When the others, attempting to gain access to this row, fetch it, they get this error number. This lets them know that the row is 'busy'. This is desirable! Write your application to handle this error number. If you get it, tell the user that another user is accessing this row, try again later. Or build a While loop to retry a certain number of times. If you want to be able to fetch that row even though locked, SET ISOLATION TO DIRTY READ, this will ignore the lock. Use this only for querying or fast, dirty reports, as it also gets non-committed rows. If the locking mode is PAGE for that table, if any row in the same page as that you are attempting to fetch is locked, you will get this error. For that reason, row locking is more desireable; the fact that another row in that page is locked is usually of no interest to an application, while the fact that the row they are trying to access is locked is very important info. Only lock the table in exclusive mode in you want to single thread access to the entire table, therby reducing concurrency to ZERO. Hope this helps, DCT