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?
I would use the automatic locking of Informix.
----------------------- delete --------------------------
SET LOCK MODE TO WAIT 2; BEGIN WORK;
DECLARE xxx CURSOR WITH HOLD FOR SELECT yourfields FROM
yourtable FOR UPDATE;
OPEN xxx;
modifiedRows_i = 0;
while ( sqlca.sqlcode == 0L )
{
FETCH xxx INTO yourrecord; /* update lock */
/* you can do anything you want between the FETCH
and the final DELETE */
DELETE FROM yourtable WHERE
CURRENT OF xxx; /* exclusive lock */
if ( modifiedRows_i++ == 100 )
{
COMMIT WORK; /* release the locks */
BEGIN WORK; modifiedRows_i = 0;
}
}
COMMIT WORK;
------------------------- insert -----------------------
SET LOCK MODE TO WAIT 2; BEGIN WORK;
DECLARE yyy CURSOR WITH HOLD FOR INSERT INTO yourtable
VALUES( :recordVar );
OPEN yyy;
for ( i = 0; i < numberOfInserts; )
{
/* fill your record variable with the values */
recordVar.field1 = value1;
...
PUT yyy;
if ( ( ++i % 100 ) == 0 )
{
COMMIT WORK;
BEGIN WORK;
}
}
COMMIT WORK;
>
> 2. If not what if any locks should we be setting? These transactions will
> not be adding/deleting the same rows.
>
> 3. Can isolation level Cursor Stability control this?
The Isolation levels determine how long a lock will be placed
on an object while you are READing rows from a table.
> 4. What are the default settings for locking and isolation?
No, not the complete description. Just what happens when
you start a delete statement inside a transaction:
All the rows you delete will be locked exclusive until the
end of your transaction. Even if you enter several delete
statements, the locks will be freed at the end of your
transaction.
( Similiar behaviour if you insert rows ).
I always recommend not to use one large transaction, but
several small transactions. In my programm above I try to
split the complete transaction into several transactions
where I delete/insert 100 rows per transaction.
Your default isolation levels:
DATABASE w NO LOGGING -> DIRTY READ
DATABASE w BUFFERED/UNBUFFERD LOGGING -> COMMITTED READ
DATABASE w ANSI MODE -> REPEATABLE READ
Best regards,
Stefan Weideneder