SELECT… FOR UPDATE

I’m a DBA and not really a developer so I don’t often get involved in writing SPL or complex code unless it’s for a monitoring or maintenance script.

Recently I was asked for some help with the SELECT… FOR UPDATE construct, the end result of which made me realise that I didn’t understand the effects of using this. If you’re an experienced Informix person, this is another one of my “so-what” posts as I am not covering anything new. However, this feature is not brilliantly documented in the manual and so I think it’s worth covering.

I had thought or assumed that SELECT… FOR UPDATE would select the rows and place exclusive locks on them, preventing another session updating them before I did. This is the functionality I wanted but this is not what it does.

So what does it do? Luckily on my desk is a dusty copy of Managing and Optimizing Dynamic Server 2000 Databases, left by a previous DBA and looking like the handbook given out at a training course. This holds some of the answers. And there is no better way of testing what’s in there than by firing up my DBA sandbox.

I begin by creating table on which I’m going to perform some updates and use multiple sessions to see the effect of locking.

create table tobeupdated (
col1 int not null,
col2 int not null
);

insert into tobeupdated values (1,1);
insert into tobeupdated values (2,1);
insert into tobeupdated values (3,1);
insert into tobeupdated values (4,1);
insert into tobeupdated values (5,1);
insert into tobeupdated values (6,1);

create unique index ix_tobeupdated on tobeupdated (col1);
alter table tobeupdated add constraint primary key (col1) constraint pk_tobeupdated;

For reasons that will become clear soon, I should mention that I am using a logged non-ANSI database and therefore the default isolation mode is committed read.

To use SELECT… FOR UPDATE we must explicitly begin a transaction so let’s run the following:

> begin work;

Started transaction.

> select * from tobeupdated where col1=1 for update;

col1 col2

1 1

1 row(s) retrieved.

And now I will check what locks this has placed with onstat -k. From querying systables and sysfragments I know the part numbers in hexadecimal for my table is 0x00800654 and its unique index is 0x00900D40. I’ve removed all irrelevant output:

Locks
address wtlist owner lklist type tblsnum rowid key#/bsiz
44ec7d98 0 7d448418 44f0b298 HDR+IX 800654 0 0

This was my first surprise when investigating it initially: there is no exclusive row lock; I have just an intended lock on the table.

So what can other sessions do to the row I have just selected for update? It seems they can select it AND update it:

> select * from tobeupdated where col1=1;

col1 col2

1 1

1 row(s) retrieved.

> update tobeupdated set col2=2 where col1=1;

1 row(s) updated.

Where does this leave my original session, still with its open transaction?

> select * from tobeupdated where col1=1 for update;

col1 col2

1 2

1 row(s) retrieved.

Arrgghh! My row has been updated underneath me. If I were to update it now, the original session would lose its update.

I thought about using repeatable read isolation to keep the row lock after the select. That dusty old training manual to the rescue… It seems I was thinking along the right lines but there is no need to go that far. You can also issue the statements:

SET ISOLATION TO DIRTY READ RETAIN UPDATE LOCKS;
SET ISOLATION TO COMMITTED READ RETAIN UPDATE LOCKS;
SET ISOLATION TO CURSOR STABILITY RETAIN UPDATE LOCKS;

The training manual states:

“The RETAIN UPDATE LOCKS feature is a switch which can be turned on and off at any time during a user connection to the server. It only effects SELECT… FOR UPDATE statements with isolation levels DIRTY READ, COMMITTED READ and CURSOR STABILITY.

“When the UPDATE LOCK has been placed on a row during a FETCH of a SELECT… FOR UPDATE with one of the above isolation levels it is not released at the subsequent FETCH or when the cursor is closed. The UPDATE LOCK is retained until the end of the transaction. This feature lets the user avoid the overhead of Repeatable Read isolation level or work arounds such as dummy updates on a row.”

Let’s see what effect one of these has by running through the same process again:

> begin work;

Started transaction.

> set isolation to committed read retain update locks;

Isolation level set.

> select * from tobeupdated where col1=5 for update;

col1 col2

5 1

1 row(s) retrieved.

Running onstat -k shows I have an extra lock that was not there before:

Locks
address wtlist owner lklist type tblsnum rowid key#/bsiz
44ec7d18 0 7d44b5c8 44f0b298 HDR+U 800654 105 0
44f0b298 0 7d44b5c8 44f0cd98 HDR+IX 800654 0 0

It looks promising because the lock is on a specific row. The HDR+U lock is a special promotable lock used for rows retrieved for update but are not yet updated. It prevents anyone else from placing an exclusive or promotable lock on the object.

A quick check with a second session shows that we can’t place an exclusive lock to update the row:

> update tobeupdated set col2=10 where col1=5;

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 42

We can, however, select from the table in the second session showing that the promotable lock is just that: a shared lock that can be promoted to an exclusive lock.

> select * from tobeupdated where col1=5;

col1 col2

5 1

1 row(s) retrieved.

But we can’t perform a second SELECT… FOR UPDATE in the second session until the first session commits or rolls back:

> begin work;

Started transaction.

> select * from tobeupdated where col1=5 for update;

col1 col2

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 48

So in summary, I nearly got what I wanted or expected in the first place when I used RETAIN UPDATE LOCKS. However, there appears to be no way of placing an exclusive lock on the row before the update, preventing another session from selecting it unless the session is well-behaved and uses SELECT.. FOR UPDATE as well. It looks like dummy updates could still be needed to prevent any code doing a normal SELECT and then a subsequent update.

I am struggling to think of a situation where SELECT… FOR UPDATE is useful without either RETAIN UPDATE LOCKS or repeatable read isolation. Perhaps someone could suggest a scenario in the comments on my WordPress blog?

This was an interesting thing to investigate on a Friday afternoon and I hope it is useful to someone. To avoid anyone trying to debug a stored procedure that is never going to work, it’s worth adding a note that SELECT… FOR UPDATE cannot be used in SPL, at least not directly. You need to look at using UPDATE… WHERE CURRENT instead.


CREATE SCHEMA statement

Recently at work we had an Informix consultant work with us for three months. Other than a small newspaper cutting entitled Disasters blamed on over-confidence, his other advice to me was be anal!

So it was with this simple dictum in mind that I approached the rebuild of some databases using dbschema.

Perhaps at this point I should warn readers that this post does not cover anything new – the feature I am about to talk about has been in Informix since at least version 10.00 and is in many ways unremarkable – but it seems to be rarely used, judging by how infrequently I have seen it mentioned in various postings over the years.

The scenario is simple: I want to import a database under my own user id but at the end of the exercise I want all the tables, indices, constraints, triggers, views and procedures/functions to be owned by a single schema user.

I thought I had it licked by diligently specifying owners for all the objects in the schema. However when I dug a little deeper and ran statements like:

select trim(t.owner) || '.' || t.tabname || '#' || trim(c.owner) || '.' || c.constrname from sysconstraints c, systables t where c.tabid=t.tabid and t.tabid>99 and c.owner!='schemaowner' and c.constrtype='N';

I found that all the not null constraints were owned by myself and some of the other objects as well.

Thinking back to the past, I think Informix 7.31 used to export the owner and name of not null constraints when dbschema was used but this feature seems to have gone, even when the -ss option is used.

So what to do? This kind of thing is a source of irritation to the anal DBA. Would I need to do through all my create table statements and add explicit constraint owner and names in for all the not null columns as well as checking for a plethora of other problems like implicit indices? Of course I could create the tables whilst logged in as the schema owner but such actions upset PCI compliance officers.

Browsing the docs today I happened across the CREATE SCHEMA statement. This is a simple one-liner you can put at the start of the script to create your objects. Interestingly you have to remove the semi-colons from your script and leave just one at the end, a little bit like using the FOREACH statement in SPL. It makes you wonder whether Informix needs this terminator most of the time.

A simple example of this is:

create schema authorization schemaowner
create table tab1 (
col1 int not null,
col2 int not null
)

create table tab2 (
col1 int not null,
col2 int not null
);

Checking sysconstraints and systables everything now belongs to schemaowner. I feel I can rest at night now 🙂