Hi,
Is there a difference between:
SELECT * FROM mytable WHERE somecolumn='Y' FOR UPDATE WITH RS
and
SELECT * FROM mytable WHERE somecolumn='Y' FOR UPDATE WITH RS USE AND
KEEP UPDATE LOCKS

I have two transactions running concurrently which perform select on
some table and try to update some column from the first result from
the list. I want to prevent concurrent updates so only one transaction
is able to update that row. Is there any other option to do that not
using WITH RS statement?

Cheers,
Mateusz Mrozewski

"Mateusz Mrozewski" <matixo@gmail.comwrote in message
news:13556681-853b-49fc-91b4-5ba6ce0df32c@k13g2000hse.googlegroups.com...It seems to me that to some degree, FOR UPDATE OF and WITH RS USE AND KEEP
UPDATE LOCKS are redundant, even though they are not exactly the same.

Comments?

"Mateusz Mrozewski" <matixo@gmail.comwrote in message
news:67c4e1c1-510f-47a0-99ed-8f2a20c6cf2f@d77g2000hsb.googlegroups.com...If you only do a select (without update lock) and then do an update later,
you could have overlapping updates. So you don't want to do this. That is
why FOR UPDATE and WITH RS USE AND KEEP UPDATE LOCKS were implemented.

If you have user think time in between the first select and the subsequent
update, then you don't want to use an update lock (FOR UPDATE) since it will
cause lock contention. In that case you need to code your application to
first select the data and save it in memory, then when the update is
attempted later, have the application select the row again with the FOR
UPDATE clause and check that no-one has updated it since the original select
(or use a single timestamp column of when row was last updated). If the row
has been changed by another application since the first select, then you
will have to inform the user with a error message and ask them to try the
update again (showing them the latest data changes). In the old CICS
mainframe days, we called this the pseudo-conversational save-compare coding
technique.

If you don't have operator (user) think time between the first select and
subsequent update, you should use the FOR UPDATE or WITH RS USE AND KEEP
UPDATE LOCKS. The first SELECT FOR UPDATE will hold an update lock (allowing
share locks only) and the second SELECT FOR UPDATE will be in lock wait
state (preventing overlapping updates) until the first transaction commits
and release its update lock and exclusive lock (when the update is actually
performed).

On Sep 3, 3:12*am, "Mark A" <some...@someone.comwrote:If you are on V9.5, go to the DB2 Info Center and search on RCT
timestamp column, and RID_BIT variable and read.
This new functionality may address exactly what you are palnning to
do.
Regards, Pierre.

On 4 Wrz, 04:01, Pierre StJ <p.stjacq...@videotron.cawrote:Thanks guys for your help. After reading your repsponses and reading
about RID_BIT and having some discussion with other guys from team we
decided and agreed on a sollution is the simplest possible for us: we
just have another column which is always updated. We've added that
column to a where clause of the update statement. So the first udpate
is fine, and the second can't find that row. I handle that on
application level. To do the select query I use uncommited read (it is
perfectly fine in my scenario), which leads to a little bit better
performace. And I don't have to mess with locks :)

Thanks again,
Mateusz Mrozewski