473,545 Members | 386 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

isolation levels and locks

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
Sep 2 '08 #1
4 6307
"Mateusz Mrozewski" <ma****@gmail.c omwrote in message
news:13******** *************** ***********@k13 g2000hse.google groups.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?
Sep 2 '08 #2
"Mateusz Mrozewski" <ma****@gmail.c omwrote in message
news:67******** *************** ***********@d77 g2000hsb.google groups.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).

Sep 3 '08 #3
On Sep 3, 3:12*am, "Mark A" <some...@someon e.comwrote:
"Mateusz Mrozewski" <mat...@gmail.c omwrote in message

news:67******** *************** ***********@d77 g2000hsb.google groups.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.
Sep 3 '08 #4
On 4 Wrz, 04:01, Pierre StJ <p.stjacq...@vi deotron.cawrote :
On Sep 3, 3:12*am, "Mark A" <some...@someon e.comwrote:
"Mateusz Mrozewski" <mat...@gmail.c omwrote in message
news:67******** *************** ***********@d77 g2000hsb.google groups.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
Sep 4 '08 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
12669
by: Markus Breuer | last post by:
I have a question about oracle commit and transactions. Following scenario: Process A performs a single sql-INSERT into a table and commits the transaction. Then he informs process B (ipc) to read the new date. So process B starts "select ..." but does not get the previously inserted row. The timespan between commit and select is very short....
4
14869
by: Eddie | last post by:
I wondering which one of the following I should use to get the best performance. 1. "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" OR 2. "WITH (NOLOCK)" I notice that when I use the #1 "SET TRANSACTION..." it sets a lock Mode type of "Sch-S" (Schema stability Lock) which described by SQL Books Online as "Schema stability (Sch-S) locks...
9
2898
by: yu_sha | last post by:
Hello everyone We have a bunch of components registered under COM+ with 'transaction required' option. On the client we are using iSeries Access 5.2.0, with all possible fixes applied (Service level SI16136, iSeries ODBC driver version 9.00.09.00). Server is DB2/AS400 05.02.0001 (that's what ODBC driver reports. So, I
3
4882
by: ibm_97 | last post by:
Session 1: $db2 +c db2 => set current isolation = UR db2 => select * from t T1 ------ ABC
375
17699
by: rkusenet | last post by:
This article is very bleak about future of DB2. How credible is the author. http://www.eweek.com/article2/0,1895,1839681,00.asp
4
8505
by: unkwb | last post by:
Dealing with the Oracle / DB2 XA (2-phase commit) drivers, I found a locking situation in DB2 I do not understand. It is actually a pretty simple scenario to which I can drill it down. Let say I have a table with one column. This table contains 2 rows: select * from my_tab test1 test2 Now I insert a value (with a 1st CLP): insert into...
2
8110
by: kanda | last post by:
Hello. I am developing the application (VBA&ODBC, to be exact) which periodically calls the stored procedures in the IBM DB2. A few of the procedures require executing with isolation level RR ( ANSI "SERIALIZABLE" ), not the default; default is CS (ANSI "Read Committed")). The procedure language is SQL. According to the documentation, I...
3
4602
by: joshsackett | last post by:
I am redesigning an application that distributes heldesk tickets to our 50 engineers automatically. When the engineer logs into their window a stored procedure executes that searches through all open tickets and assigns a predetermined amount of the open tickets to that engineer.The problem I am running into is that if 2 or more engineers log...
3
7203
by: D. | last post by:
I have a question about the "readCommitted" transaction isolation level. I have a client that is updating a record on a table. I suspend the execution after the UPDATE but before the commit statement. Than another client is trying to read the same record. As transaction isolation is set to "readCommited" I expected that the second...
0
7401
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7656
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7807
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7419
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7756
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5971
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
4944
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3442
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
703
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.