isolation levels and locks

[Mateusz Mrozewski]

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

[Mark A]

"Mateusz Mrozewski" <ma****@gmail.comwrote in message
news:13**********************************@k13g2000 hse.googlegroups.com...

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

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?

[Mark A]

"Mateusz Mrozewski" <ma****@gmail.comwrote in message
news:67**********************************@d77g2000 hsb.googlegroups.com...

So if I start two concurrent transactions which first perform select,
both should be able to get the results? How DB2 knows that I want to
perform update later? If one of the transactions perfors that update,
what will happen with the second transaction? Will DB throw any error?
My observation is that the second select hangs to avoid dirty read and
waits for the first transaction when I use "WITH RS USE AND KEEP
UPDATE LOCKS".

Maybe I will ask general question :-) Is it possible to allow two
transactions to perform SELECT from some table for a set of results,
but to avoid overlapping updates on the same row (one choosen from the
previous results), so only one UPDATE succeds and other fail?

As far as for now you helped me a lot and I think I'm close to fully
understand the issue :)

Thank you
Mateusz Mrozewski

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).

[Pierre StJ]

On Sep 3, 3:12*am, "Mark A" <[e-mail]:

"Mateusz Mrozewski" <[e-mail] in message

news:67**********************************@d77g2000 hsb.googlegroups.com...

So if I start two concurrent transactions which first perform select,
both should be able to get the results? How DB2 knows that I want to
perform update later? If one of the transactions perfors that update,
what will happen with the second transaction? Will DB throw any error?
My observation is that the second select hangs to avoid dirty read and
waits for the first transaction when I use "WITH RS USE AND KEEP
UPDATE LOCKS".
Maybe I will ask general question :-) Is it possible to allow two
transactions to perform SELECT from some table for a set of results,
but to avoid overlapping updates on the same row (one choosen from the
previous results), so only one UPDATE succeds and other fail?

As far as for now you helped me a lot and I think I'm close to fully
understand the issue :)

Thank you
Mateusz Mrozewski

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).- Hide quoted text -

- Show quoted text -

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.

[Mateusz Mrozewski]

On 4 Wrz, 04:01, Pierre StJ <[e-mail]:

On Sep 3, 3:12*am, "Mark A" <[e-mail]:

"Mateusz Mrozewski" <[e-mail] in message

news:67**********************************@d77g2000 hsb.googlegroups.com....

So if I start two concurrent transactions which first perform select,
both should be able to get the results? How DB2 knows that I want to
perform update later? If one of the transactions perfors that update,
what will happen with the second transaction? Will DB throw any error?
My observation is that the second select hangs to avoid dirty read and
waits for the first transaction when I use "WITH RS USE AND KEEP
UPDATE LOCKS".
Maybe I will ask general question :-) Is it possible to allow two
transactions to perform SELECT from some table for a set of results,
but to avoid overlapping updates on the same row (one choosen from the
previous results), so only one UPDATE succeds and other fail?

As far as for now you helped me a lot and I think I'm close to fully
understand the issue :)

Thank you
Mateusz Mrozewski

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 itwill
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 therow
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).- Hide quoted text -

- Show quoted text -

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.

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