Repeatable Read Question 
Author Message
 Repeatable Read Question

   A question about repeatble read.

   Suppose in your application you set the isolation level to repeatable read.
   Your transaction  has 1 select then multiple inserts into different tables.
   In testing  with concurrent transactions,  a deadlock occurs , the -271/143 combination.

   Question .  What  is actually occuring ?  Does Informix do a page level lock on inserts , hence  the deadlock?  Could one resolve the problem  by forcing a row level lock?

   From what I understand, repeatable read transactions are expensive, and probably should be avoided.  But what if you waned to do this anyway?

   Stated another way, can one avoid deadlocks  in a repeatable read in environment?

   Many thanks!

ciao
p

******************************************************************************
Peter Wages

Work: (703) 741-7436   Home: (703) 528-9569
******************************************************************************



Wed, 02 Jun 2004 04:59:36 GMT
 Repeatable Read Question

The last sentence says it all.

If you use repeatable read in an environment, then you need to be sure that you do not have any sequential scans in your application.  Read about the optimizer hints.   And yes -
I would  urge RR sites to use row level locking.

Quote:

>    A question about repeatble read.

>    Suppose in your application you set the isolation level to repeatable read.
>    Your transaction  has 1 select then multiple inserts into different tables.
>    In testing  with concurrent transactions,  a deadlock occurs , the -271/143 combination.

>    Question .  What  is actually occuring ?  Does Informix do a page level lock on inserts , hence  the deadlock?  Could one resolve the problem  by forcing a row level lock?

>    From what I understand, repeatable read transactions are expensive, and probably should be avoided.  But what if you waned to do this anyway?

>    Stated another way, can one avoid deadlocks  in a repeatable read in environment?

>    Many thanks!

> ciao
> p

> ******************************************************************************
> Peter Wages

> Work: (703) 741-7436   Home: (703) 528-9569
> ******************************************************************************

--
---------------------------------------------------------
Madison Pruet
Enterprise Replication Product Development
IBM Informix Dynamic Server


Wed, 02 Jun 2004 07:00:18 GMT
 Repeatable Read Question

Quote:

>    A question about repeatble read.

>    Suppose in your application you set the isolation level to repeatable read.
>    Your transaction  has 1 select then multiple inserts into different tables.
>    In testing  with concurrent transactions,  a deadlock occurs , the -271/143 combination.

>    Question .  What  is actually occuring ?  Does Informix do a page level lock on inserts , hence  the deadlock?  Could one resolve the problem  by forcing a row level lock?

Row level locking versus page level is determined by an attribute of the table which is
set at create time and can be altered:

alter table fred lock mode (row);

Art S. Kagel

Quote:
>    From what I understand, repeatable read transactions are expensive, and probably should be avoided.  But what if you waned to do this anyway?

>    Stated another way, can one avoid deadlocks  in a repeatable read in environment?

>    Many thanks!



Sat, 05 Jun 2004 07:31:03 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. SERIALIZABLE or Repeatable Read required?

2. difference between phantom and non-repeatable read

3. How does Oracle support REPEATABLE READ

4. Repeatable Read isolation (was Re: TPC-A and -B Benchmarks)

5. problems with repeatable read - whole table is locked

6. row locking / repeatable read

7. Repeatable read isolation level and locks

8. phantom or repeatable read

9. "non-repeatable reads","phantom reads"???

10. Question about READs and READ level clauses

11. Repeatable Random Sort

12. Transaction REPEATABLE..


 
Powered by phpBB® Forum Software