Re: Locking and Isolation Levels

rbruenin@my-dejanews.com wrote: > > We have an application that adds records to a table. It runs constantly and > uses a stored procedure. The first thing it does is > Lock Table in Exclusive Mode > > At night we have a purge process that drops records from this same table. It > also does a Lock Table in Exclusive Mode. If the many inserts are occuring > the delete program runs for hours. If few are occurring it can run in > a few minutes. > > Obviously both transactions cannot lock the same table which I am > assuming is the cause of the performance problem. > > My Questions: > 1. Should we be aquiring an exclusive table lock in order to > insert a record? This will speed the inserts and prevent you from overflowing the lock table (ie running out of page/row locks), however, as you have discovered locking a table prevents any kind of concurrent processing. > 2. If not what if any locks should we be setting? These transactions will > not be adding/deleting the same rows. Sounds like you should not be locking the table so the two jobs can run concurrently. You will still have lock clashes if your table uses page mode locking which is the default when a table is created. If so alter the lock mode to row. You have to be certain that you have enough locks configured in the ONCONFIG file to permit both transactions. You will need one lock for each row being inserted or deleted plus one lock per row per index. > 3. Can isolation level Cursor Stability control this? No. Cursor stability locks each row/page selected with a shared lock which is released when the next row is selected. It has nothing to do with this. > 4. What are the default settings for locking and isolation? Default is to lock each row or page affected as it is updated based on the tables locking mode. The default locking mode for a table is page. The default isolation depends on the database's logging mode: LogMode Isolation ------- --------- Unlogged Dirty Read Buffered Log Committed Read Unbuffered Log Committed Read ANSI Mode Repeatable Read This is documented in the discussion of SET ISOLATION in the SQL Syntax manual. Art S. Kagel