473,700 Members | 3,126 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 6348
"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
12707
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. (NOTE: two different sessions are used) Questions: 1.) Does commit when returning from call...
4
14881
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 do not block any transactional locks, including exclusive (X) locks"
9
2910
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
4888
by: ibm_97 | last post by:
Session 1: $db2 +c db2 => set current isolation = UR db2 => select * from t T1 ------ ABC
375
18060
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
8514
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 my_tab values('x1')
2
8133
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 can adjust procedure *run*-time isolation level by setting *compile*-time dataserver-wide option
3
4609
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 in at the same time the stored procedure will distribute the same set of tickets multiple times. ...
3
7213
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 client will read the old version of the record (before the update). Instead, the second client hangs and...
0
8709
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9058
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8952
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
7791
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6555
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5894
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 into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4395
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2371
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2018
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.