Re: Locking and Isolation Levels
We have a 24 x 7 system (well, almost) and our approach is to carry out a lot of
small transactions to allow the users to work between the cracks. We select the
primary keys of records to be deleted into a temporary table. Then we loop through
the table, deleting each associated record in a separate transaction. This process
can be refined to record status and audit information in the temporary table.
Jim Wright
Stefan Weideneder wrote:
> 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