Select without locked row
The poster wanted a SELECT that skips rows locked by other sessions, to let a pool of servers pull work from a message queue table without blocking on messages already being processed. Art Kagel explained Informix can't simply skip locked rows (options are DIRTY READ, COMMITTED READ with LAST COMMITTED on 11.10+, or LOCK MODE TO WAIT n), and recommended an 'in_process' flag column updated via a FIRST 1 / FOR UPDATE cursor. When the poster needed the update inside his transaction, Art suggested selecting with DIRTY READ, then updating by ROWID with an 'in_process = 0' recheck in a retry loop to handle race conditions. No confirmation from the poster that it worked is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi there, I want to do a selection that does not include locked row in the result. Is this possible ? I tried to change the SET TRANSACTION and SET ISOLATION parameters, but never succeed.. If not possible, how can i bypass the locked row in my FETCH ? Thank you all Yd
'Bypass' as in ignore locked rows and return all other rows? You cannot do that. Here's what you can do: - You can set your isolation level to DIRTY READ and you will see the current value of locked rows even if those values will be rolled back. - If you are running IDS 11.10+ you can set isolation level to COMMITTED READ with the LAST COMMITTED option and see the last committed value of locked rows. - You can set LOCK MODE TO WAIT <Nsecs> and block on short lived locks without getting a lockout error regardless of isolation level. As long as no lock is kept for longer than Nsecs this will get you a complete result. Now tell us what problem you are seeking to solve. Maybe we have a more specific solution. Art S. Kagel ----- Original Message ----- From: Yann Delanoe <ids@iiug.org> To: ids@iiug.org At: 10/08 8:33:51 Hi there, I want to do a selection that does not include locked row in the result. Is this possible ? I tried to change the SET TRANSACTION and SET ISOLATION parameters, but never succeed.. If not possible, how can i bypass the locked row in my FETCH ? Thank you all Yd ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Thank you for the answer. Here is my problem : I've got a table that store messages to be treated. I've got a pool a server to treat those messages, they are working in parallel. I just want that if a message is currently treated by one server, the others wont block on this locked row but that they will take the next ones. They all use the same select order.. so messages in treatment always appears in first in the result. I cannot and dont want to know, in a server, if and how many messages are currently in treatment. Regards Yann Delanoë
set isolation to dirty read;
select ...
...should work
HTH
Reinhard
> -----Ursprüngliche Nachricht-----
> Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Im Auftrag von
> YANN DELANOE
> Gesendet am: Montag, 8. Oktober 2007 14:33
> An: ids@iiug.org
> Betreff: Select without locked row [10056]
>
> Hi there,
>
> I want to do a selection that does not include locked row in
> the result.
> Is this possible ? I tried to change the SET TRANSACTION and
> SET ISOLATION
> parameters, but never succeed..>
> If not possible, how can i bypass the locked row in my FETCH ?
>
> Thank you all
> Yd
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Ahh. A common paradigm. I would use an active flag to accomplish this:
- Add an 'in_process' column to the table.
- SELECT using COMMITTED READ (without LAST COMMITTED option if in IDS 11.10)
and with SET LOCK MODE TO WAIT 5;
- In the application, instead of locking active message rows for the duration
of the operation, do with a cursor:
DECLARE busy CURSOR FOR
SELECT FIRST 1 *
FROM message_table
WHERE in_process = 0
ORDER BY arrival
FOR UPDATE OF in_process;
OPEN busy;
FETCH busy INTO :message_struct_var;
UPDATE message_table SET in_process = :mypid_or_threadid WHERE CURRENT OFbusy;
CLOSE busy;
BEGIN WORK;
.....
COMMIT WORK;
DELETE FROM message WHERE in_process = :mypid_or_threadid;LOOP;
Now each consumer task/thread will block momentarily if another consumer is in
the process of selecting a message to work on and will take the first message
that's not already being worked on by another thread/task.
Art S. Kagel
----- Original Message -----
From: Yann Delanoe <ids@iiug.org>
To: ids@iiug.org
At: 10/08 8:54:03
Thank you for the answer.
Here is my problem :
I've got a table that store messages to be treated.
I've got a pool a server to treat those messages, they are working in
parallel.
I just want that if a message is currently treated by one server, the others
wont block on this locked row but that they will take the next ones.
They all use the same select order.. so messages in treatment always appears
in first in the result.
I cannot and dont want to know, in a server, if and how many messages are
currently in treatment.
Regards
Yann Delanoë
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
The active flag solution is a solution that i thought about, but wanted to know if there was nothing more optimized for that. Thank you Art. Regards Yd
Active flag are pretty optimal actually, Yann. The SELECT you have to do anyway, adding the FIRST 1 clause prevents the engine from processing more than one row out to the client library reducing server overhead. The UPDATE ... WHERE CURRENT OF is the fastest way to update a row that's already been selected. Finally, 99.99% of the time the consumers will not block because the other consumers will already be working on rows. If there is a compound index on the ORDER BY column(s) and the active flag column the SELECT will only have to examine a single row and not actually have to sort at all (especially if you run the query under SET OPTIMIZATION FIRST_ROWS). Data locality will mean that unless the message queue rows are rather large (shouldn't be BTW) there will be lots of them on a page meaning that the vast majority of the time the next message will be on the same page as the last one or on a page read by readahead processing and so already in memory. DO NOT UPDATE STATISICS on the message queue table (or at best to LOW level) as it is too volatile, the optimizer will become confused. All in all, this is very cheap if implemented correctly. Art S. Kagel ----- Original Message ----- From: Yann Delanoe <ids@iiug.org> To: ids@iiug.org At: 10/08 9:12:33 The active flag solution is a solution that i thought about, but wanted to know if there was nothing more optimized for that. Thank you Art. Regards Yd ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Art, The solution you give, doesn't work for me. I modify it to place the BEGIN transaction at the beginning cause i need the update of in_process to be in the transaction so that if something fail, the in_process will get his good value back. Until the commit of a first process, other process always have the locked row in the result ... Regards Yann Delanoë
It is REALLY useful to include the threaded msg to which you are posting for
those on the list using DUMB mailers to monitor the list - like me ;-(
IB this is the thread with the work queue message table, yes?
There are ways of handling that, but let's take a direct approach. Now that
you are using an in_procress flag, you can set the isolation level of the one
query to dirty read so that it will ignore the currently locked and updated
(but not committed) rows. Only two rubs: 1- IB you cannot use a FOR UPDATE
clause to update the message efficiently, so you'll select the row's ROWID and
update by ROWID which is almost as good, and 2- you'll have to prevent race
conditions on the message table. Pseudo-code below:
SET LOCK MODE TO WAIT; -- Infinite wait so race condition lock-out doesn't
-- timeout
BEGIN WORK;
do {
SET ISOLATION DIRTY READ;
SELECT FIRST 1 ROWID, *
INTO :rowid, :data_struct
FROM message
WHERE in_process = 0
ORDER BY ...;
SET ISOLATION COMMITTED READ;
UPDATE message SET in_process = :mypid
WHERE ROWID = :rowid and in_process = 0; --Double check for race condition
-- The update will actually block if another consumer selected and already
-- updated the same message until that task commits. This race condition
-- should be rare enough to not cause throughput problems though.
SELECT DBINFO('sqlca.sqlerrd2')
INTO :n_rows_updated
FROM systables
WHERE tabid = 1;
} while (n_rows_updated == 0);
...... -- Continue processing
COMMIT WORK;
Art S. Kagel
----- Original Message -----
From: Yann Delanoe <ids@iiug.org>
To: ids@iiug.org
At: 10/09 3:30:08
Hi Art,
The solution you give, doesn't work for me. I modify it to place the BEGIN
transaction at the beginning cause i need the update of in_process to be in
the transaction so that if something fail, the in_process will get his good
value back.
Until the commit of a first process, other process always have the locked row
in the result ...
Regards
Yann Delanoë
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi. Try to catch excepion with "with resume" option in cursor. Server must continue processing cursor after exception( error) have been caught. Best regards , Konstantin. ----- Original Message ----- From: "YANN DELANOE" <yann.delanoe@sterci.com> To: <ids@iiug.org> Sent: Monday, October 08, 2007 3:33 PM Subject: Select without locked row [10056] > Hi there, > > I want to do a selection that does not include locked row in the result. > Is this possible ? I tried to change the SET TRANSACTION and SET ISOLATION > parameters, but never succeed.. > > If not possible, how can i bypass the locked row in my FETCH ? > > Thank you all > Yd > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >