Monday, February 26, 2007

Cheetah spot by spot: LAST COMMITTED READ

This is the first of a series of articles about the announced features of IDS v11.10, code named Cheetah and currently available as Beta release. I'll try to show the features in random order. Being the first doesn't mean it's more or less important than other features.

LAST COMMITTED READ is a new isolation level that is available in Cheetah. As you probably know, the isolation levels define how "isolated" one session's transactions are from other sessions. If you're not familiar with the subject I suggest you search online. Wikipedia has a nice article on this.

If you ever did any programming in Informix databases you surely understand the subject and you know that the two most used isolation levels are COMMITTED READ (the default for logging databases) and DIRTY READ. In short terms their definitions are:

    In this isolation level, your session will "honour" other session's locks. The session's behaviour when it finds a locked resource (table row, index key) depends on the LOCK MODE you selected. If using the default (LOCK MODE NOT WAIT), you'll get an error immediately. If using a time wait (LOCK MODE TO WAIT [x seconds]), it will wait the maximum number of seconds for the lock release. If the lock remains after that timeout you'll get the same error as with lock mode NOT WAIT.
    This is equivalent to the ANSI COMMITTED READ isolation level
    In this mode, your session won't "see" any locks. It will not give you errors or wait. It will read whatever value is available at the moment. It is called "dirty" because this means you may get values that aren't and possibly will never be committed in the database.
    This is equivalent to the ANSI UNCOMMITTED READ
Most of the time you have probably used COMMITTED READ and we usually try to minimize the time we're holding locks. This way we minimize the number of lock waits and lock related errors. Sometimes, depending on the kind of data and what we're doing with it we can use DIRTY READ.

Note also that this lock waiting and/or lock ignoring happens when we're SELECTing data. Obviously, if you want to change the data, even in DIRTY READ you'll have to wait for any lock holder to finish his transaction and unlock the resource.

So, what does Cheetah bring into this issue? A new isolation level called "COMMITTED READ LAST COMMITTED".
This isolation level is similar to COMMITTED READ in the sense that it only returns committed data, but with the great advantage that your reads will never block. If your session hits a locked resource, the IDS engine will retrieve the value that was there before the current lock holder session made any change. That's where the name "LAST COMMITTED" came...

This information is always available because if the lock holding transaction tries to ROLLBACK WORK, the original value will have to be restored.

Some of you with knowledge from other RDBMS like Oracle or Postgres may be wondering what's new about this... Well, historically there have been two types of RDBMS: The versioning (each transaction gets a "version" number that marks all the data it changes) and the non-versioning. The versioning RDBMSs have always this sort of isolation levels because each transaction sees the "current image" of the Database. But the non-versioning RDBMS usually didn't implement this. They have more light-weight reading primitives that read the data directly (not the before images marked with earlier versions stamps) and this causes the locking conflicts.
Also, some versioning RDBMS don't support the ANSI UNCOMMITTED READ, because the way they read doesn't allow it.

So, it's has been a kind of trade-off. But currently, most of the non-versioning databases implement this kind of COMMITTED isolation without blocking the readers.

Ok... But how do we use it?!

Well, the obvious answer is by issuing the "SET ISOLATION TO COMMITTED READ LAST COMMITTED". After this statement your session will be working in this new mode and it won't get blocked by any row or index locks while reading.

But this would force you to change your existing application code... and in some cases you possibly don't have access to that. So what can be done? Ah!... The nice people of R&D did think about this and you have three solutions:

  1. There is a new ONCONFIG parameter called USELASTCOMMITTED
    You can set it to several values and depending of these values, the database server will automatically "promote" the specified isolation level to the new mode. The values are:
    1. 'NONE'
      This is the same as clearing it. No isolation level will be promoted. This is also the default value
      The transactions with traditional COMMITTED READ will work in the new isolation level
    3. 'DIRTY READ'
      The transactions with traditional DIRTY READ will work in the new isolation level
    4. 'ALL'
      The transactions with traditional DIRTY READ and the transactions with traditional COMMITTED READ will work in the new isolation level

  2. You can also use USELASTCOMMITTED as a client side environment variable. The syntax is the same

  3. If you want this promotion to happen only to specific users, you can use the new feature that allows you to force the execution of a stored procedure (sysdbopen) when a user connects to a database. Inside this procedure you could use the SET ISOLATION.... statement
Some limitations or considerations:
  • This feature requires that the tables use the ROW level locking. Tables with PAGE level locking will inhibit the feature in that transaction.
  • When you established this new isolation level for a session, if it makes a distributed query to a remote database server that don't support this feature (i.e.: any pre v11.10) it will fallback to COMMITTED READ
  • If the table is a RAW (non logged) table the feature will not work
  • If the table has some user-defined columns and these don't support the new mode, the feature will not work

Now, let's see an example. To test this you'll need two sessions. We'll use the customer table from stores_demo database. Please make sure it's defined to use row level locking.

Session one:
  1. connect to the stores_demo
  2. show the customer data (fname, lname) for customer number 101
  3. upate this data to "John Doe" inside a transaction and maintain the transaction open> dbaccess stores_demo -

Database selected.

> select fname, lname from customer where customer_num = 101;

fname lname

Ludwig Pauli

1 row(s) retrieved.

> begin work; update customer set fname='John', lname='Doe' where customer_num=101;

Started transaction.

1 row(s) updated.


Now, in session 2:
  1. Set the isolation to committed read (default) and select the data: An error is returned
  2. Set the isolation to dirty read: the "current" data, as changed by session 1
  3. Set the isolation to the new mode: we get the original (pre session 1 update) data> dbaccess stores_demo -

Database selected.
> set isolation to committed read; select fname, lname from customer where customer_num = 101;
Isolation level set.

fname lname

244: Could not do a physical-order read to fetch next row.

107: ISAM error: record is locked.
Error in line 1
Near character position 90
> set isolation to dirty read; select fname, lname from customer where customer_num = 101;

Isolation level set.

fname lname

John Doe

1 row(s) retrieved.

> set isolation to committed read last committed; select fname, lname from customer where customer_num = 101;

Isolation level set.

fname lname

Ludwig Pauli

1 row(s) retrieved.


One final note: Recent versions of IDS have an ONCONFIG parameter (DEF_TABLE_LOCKMODE) where you can configure the default lock mode for tables created without the "LOCK MODE" clause. The current Cheetah Beta drop apparently doesn't support this. So, if you're counting on this to make your tests it won't work.

You can see aditional info about this subject at:


Anonymous said...

"Note also that this lock waiting and/or lock ignoring happens when we're SELECTing data."

This waiting would happen iff another session is updating the same data (rows/page), if another session is also selecting data, we would not having any lock waiting, right?

PS: Just moving to IDS from Oracle and trying to understand the HUGE difference in the way these two DBs handle transactions.

Fernando Nunes said...

Not necessarly correct. Depends on what you're trying to do and the other session's isolation level. A session in REPEATABLE READ can put shared locks in the data that it had to look to generate the result set. This shared lock won't prevent you from reading the records, but will disable any UPDATE/DELETE from your session...

This isn't too different from Oracle (with SERIALIZABLE). What is different is the "writers won't block readers". In current versions of IDS this happens. In Cheetah with LAST COMMITTED READ you can avoid it.

If this isn't clear please send me an e-mail and I'll try to clarify the subject.

Regards and thanks for the feedback.

Anonymous said...

Thanks for clearing that up.

Just to clear up my mind, if my session is just selecting data and the other session is also just selecting data irrespective of the isolation levels we would not have any kind of locking concerns. Locking/waiting concerns crop up only when we have sessions doing inserts also and as you pointed out if my session wants to insert/update and another session with isolation level of REPEATABLE READ is selecting data, I would be blocked.

So in effect what this new isolation level LAST COMMITTED READ provides is equivalent to what Oracle provides under READ COMMITTED - "readers not being blocked by writers" and the behaviour of the isolation level COMMITTED READ (i.e. readers being blocked by writers) provided by Informix does not have a clear-cut equivalent in the Oracle world.

In the case of Informix with my session in REPEATABLE READ mode another session just cannot update the data which I am querying. SERIALIZABLE's behavior in Oracle is slightly different from what you mention. If my session is in SERIALIZABLE mode another session can update the data that my session is trying to query and in that case my query would error out with a ORA-08177 and as a corollary, I believe, you can modify the data irrespective of the fact that another transation has a SERIALIZABLE isolation level.


Fernando Nunes said...

Yes for 1st paragraph: "readers won't block readers"

Yes for 2nd paragraph: Oracle and others (Postgres) are called "versioned RDBMS", so they don't block... In fact I think in Oracle you can't do a dirty read... Not that it's usually useful.. Most of the times we used it to workaround the blocking issue.

I had a different idea about Oracle's SERIALIZABLE behavior, but as you can imagine, Oracle is not my speciality :)
So I'll take it as you say.

You can find a good description of locking and isolation levels in Informix on the following URL:

Hope this helps,

Fernando Nunes

Fernando Nunes said...

The URL isn't visible... At least for me... try this

Anonymous said...

Thanks very informative...