up till now I've used Isolation xiDirtyRead , which worked pretty good . But unfortunately sometimes some items were deleted , the cause was that the Remote Rows sometimes had incorrect data .
I googled and it looks like the obvious solution is to simply set it to xiReadCommited . But I am starting to get confused about it .
According to this page : Committed Read Isolation it does not Lock and it is almost as fast as xiDirtyRead (or read Uncommitted ) . It also says it does not actually place locks which is perfect , since I CANNOT under any circumstance block anything to the database .
But just after the text it says
In the Committed Read isolation level, locks held by other sessions can cause SQL operations to fail if the current session cannot acquire a lock or if the database server detects a deadlock. (A deadlock occurs when two users hold locks, and each user wants to acquire a lock that the other user owns.) The LAST COMMITTED keyword option to the SET ISOLATION COMMITTED READ statement of SQL reduces the risk of locking conflicts.
I do not understand this for the life of me , sorry . What does this mean ?
Example : I open a table with 16.000 rows , I go trough this table row by row , by each move in the table the database checks if the current row is locked , if it is I get an exception ?
If yes fine , if it simply continues and returns 15980 rows committed ( and skips 20 locked/uncommitted ) bad .
If a deadlock occurs as stated above since I am only ReadingCommited that means that I am the one being killed of and NOT another process which actually has full read/write access .
Thank you.
Informix uses locks to resolve concurrency ( other databases, like PostgreSQL user Multiversion concurrency control ). What this means is that every time a row is changed in a transaction, it will be locked until the transaction commits or rollbacks.
In Informix Dirty Read isolation will ignore locks and read rows that have uncommitted data.
Committed Read will only read committed data and if it encounters a locked row ( uncommitted data ) it will:
If "Committed Read" aborts due to a lock, it will only return the rows that it found until the locked row that caused it to abort.
COMMITTED READ LAST COMMITTED will only read data that is committed, and when it encounters a locked row ( uncommitted data ), it will:
Be aware that even with the COMMITTED READ isolation levels, rows can change in the table after you have read them.