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.

Advertisements

7 Comments on “SELECT… FOR UPDATE”

  1. Sean Durity says:

    To back up a little, what is the use case for using select for update? Why not just do the update and be done? Or is the case that something else must be checked before the update is possibly done? “Retain update locks” was new to me, so thanks!

    • Ben Thompson says:

      Hi Sean,

      Usually because you perform some other processing before you modify each row. The update can, of course, be a delete. I agree – if all you want to do is update data – doing it in a one-liner is much faster.

      Ben.

  2. Andrew Ford says:

    Nice post Ben, thanks for writing it.

    You ask for a situation where SELECT … FOR UPDATE is useful without RETAIN UPDATE LOCKS or repeatable read isolation and I think the answer is performance.

    SELECT … FOR UPDATE with UPDATE… WHERE CURRENT OF should outperform the typical SELECT Primary Key Fields, Other Fields/UPDATE … WHERE Primary Key Fields = ? because the engine doesn’t have to evaluate the WHERE clause of the UPDATE and find the row to update again, it already has the row identifier and can quickly update the row.

    I think this is useful whether or not RETAIN UPDATE LOCKS is set or not, it just depends on if you need to worry about the data changing before you update it.

    I found an old 4GL example where someone tested this here: http://www.angelfire.com/planet/azrealhk/4GLOpt.htm Search for UPDATE WHERE CURRENT OF to get to their results.

    In their example the UPDATE WHERE CURRENT OF was almost 25% faster.

  3. Doing a “SELECT … FROM …. WHERE … FOR UPDATE” is not the same as preparing the statement, declaring a cursor and effectively FETCHing.
    With a cursor as soon as you FETCH, you have an UPDATABLE lock, so no one will be able to change the “current” row. But everybody will be able to access it with a simple read (SELECT).

    As Andrew mentioned, performance is a very good reason… with a “… WHERE CURRENT OF cursor_name” the server already knows where to go… if you repeat the UPDATE … WHERE…. you’re forcing it to go all the way through the SQL layer, optimizer, disk/memory access and so on.

    HTH
    Regards

  4. Ben Thompson says:

    Thanks for the comments, guys. I think your answers are pretty definitive.

  5. […] Thompson over at Informed Mix currently wrote about using “select for update/where current of” syntax and in the […]

  6. Art S. Kagel says:

    If you are using a cursor to fetch rows that will later be updated, the difference between using and not using RETAIN UPDATE LOCKS is what happens once you update a given row. Without the RETAIN UPDATE LOCKS clause the exclusive lock obtained be the FETCH is released. With that clause invoked those exclusive locks will persist until the transaction is committed or rolled back. If many rows are processed by the cursor and updated or deleted all of those outstanding locks will cause serious lock contention if you have more than one user processing that table.

    One should also keep in mind that reading is not inhibited, so you still have to use Optimistic Locking Protocols to prevent another user from updating the row you just updated with stale data after you commit your transaction!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s