Question about isolation

Hi, here is my problem: we are using informix online version 5.01 on solaris 2.6. The database is relatively small and there is 6 users. There is a lot of update, and we got several deadlock issues. We are using cursor stability isolation. In the documentation, it is said that if we were using committed read isolation, there is less chance that conflict locking would arrive? Is committed read isolation safe for data integrity? Thank you for your help. Stéphane

"Stéfane Ruel" wrote: > > Hi, > here is my problem: > we are using informix online version 5.01 on solaris 2.6. The database > is relatively small and there is 6 users. There is a lot of update, and > we got several deadlock issues. We are using cursor stability isolation. > In the documentation, it is said that if we were using committed read > isolation, there is less chance that conflict locking would arrive? > Is committed read isolation safe for data integrity? > Yes and no. It depends on how your application updates the data. Committed Read gives you no guarantees except that the data that you read was committed when you read it, that is, you will not read "phantom rows" as you might do if your isolation level was Dirty Read. Committed Read does not place shared locks on rows, so data that you have read can change (or even be deleted) at any time. If your update strategy is "Optimistic", then Committed Read gives you a high level of concurrency. "Optimistic" can be implemented as :- 1) Read the record; 2) Some time passes (whilst others can modify the record = high concurrency); 3) You want to update the record so you re-read it; 4) If the record has changed since the first read, take the appropriate action. Hopefully, this is rare (hence the term "Optimistic"). Otherwise, read it FOR UPDATE; 5) Update the record; 6) Release the exclusive lock; If your application is going to use Optimistic Locking, unless you implement steps 3-4 properly, you can get the "lost update" syndrome. Cursor Stability is a way of implementing "Pessimistic Locking", where you assume that "everyone wants to update your record". Cursor Stability guards against this by placing a shared lock on the current record, guaranteeing that it cannot be updated or deleted. The lock is released when the next record is fetched or the cursor is closed. For some applications (i.e. critical reports), even Cursor Stability is no good. Repeatable Read is needed, especially when analysis of a complete "set" of rows is needed for a job. Imagine a report that adds up the balance of all your bank accounts. If the report is at account 5 out of 10, and another process transfers $100 from account 3 to account 7, then the report will overstate your balance by $100 when it completes. So, horses for courses when it comes to isolation levels. HTH Brett Randall > Thank you for your help. > > Stéphane