473,836 Members | 1,608 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Basic SQL prcoedure question

Hi!

If I create a procedure with BEGIN ATOMIC block I know these means that when
an error occurs all the SQL statements will be rolled back.
The question I have is:
If I select all records form a table and process them. The processing of
those records takes about 1 minute for example. In the meanwhile new
records come into the same table. If I issues the same SQL statement will I
see these new rows too ?

Steps in BEGIN ATOMIC block of a procedure:
1.) SELECT * FROM TABLE1;
2.) -- Do something with these rows. These takes 1 minute and in the
meanwhile another user inserts a row into TABLE1
3.) SELECT * FROM TABLE1;

In step 1 I have 10 rows.
In step 2 user inserts 3 rows.
Will I see 13 rows in step 3 or only the original 10.

Thanks for your help.

Best regards,
Kovi

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mi kropis.si |
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Feb 27 '06 #1
8 1389
By default, You will see 13 rows ...

Whether you allow the inserts into the table when you have a cursor (of
step 1 open) is based on the Isolation level of the Stored Proc
package or if you have explicityly defined at the statement level ..

http://publib.boulder.ibm.com/infoce...n/c0007870.htm
gives details of isolation levels.

BTW, the default isolation level is CS .

HTH

Sathyaram

Feb 27 '06 #2
s.*********@goo glemail.com wrote:
By default, You will see 13 rows ...

Whether you allow the inserts into the table when you have a cursor (of
step 1 open) is based on the Isolation level of the Stored Proc
package or if you have explicityly defined at the statement level ..

http://publib.boulder.ibm.com/infoce...n/c0007870.htm

gives details of isolation levels.

BTW, the default isolation level is CS .

HTH

Sathyaram

Thanks for the info, but..
In a SQL stored procedure is every single SQL statement unit of work or is
the entire procedure one unit of work?

Best regards,
Kovi

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mi kropis.si |
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Feb 28 '06 #3
Gregor KovaÄŤ wrote:
s.*********@goo glemail.com wrote:
By default, You will see 13 rows ...

Whether you allow the inserts into the table when you have a cursor (of
step 1 open) is based on the Isolation level of the Stored Proc
package or if you have explicityly defined at the statement level ..

http://publib.boulder.ibm.com/infoce...n/c0007870.htm

gives details of isolation levels.

BTW, the default isolation level is CS .

HTH

Sathyaram

Thanks for the info, but..
In a SQL stored procedure is every single SQL statement unit of work or is
the entire procedure one unit of work?

An SQL Procedure is merely an extension of your application. You can
COMMIT and ROLLBACK as you please. You can use SAVEPOINTS as well.
BEGIN ATOMIC.... END is a shorthand for a savepoint.
Each statement in itself is always ATOMIC (that is it executes or it
doesn't - black or white)

Note that COMMIT and ROLLBACK are global operations. If you want to
achieve atomicity within a procedure use BEGIN ATOMIC ... END or use
SAVEPOINTs.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 28 '06 #4
Serge Rielau wrote:
Gregor Kovač wrote:
s.*********@goo glemail.com wrote:
By default, You will see 13 rows ...

Whether you allow the inserts into the table when you have a cursor (of
step 1 open) is based on the Isolation level of the Stored Proc
package or if you have explicityly defined at the statement level ..

http://publib.boulder.ibm.com/infoce...n/c0007870.htm

gives details of isolation levels.

BTW, the default isolation level is CS .

HTH

Sathyaram

Thanks for the info, but..
In a SQL stored procedure is every single SQL statement unit of work or
is the entire procedure one unit of work?

An SQL Procedure is merely an extension of your application. You can
COMMIT and ROLLBACK as you please. You can use SAVEPOINTS as well.
BEGIN ATOMIC.... END is a shorthand for a savepoint.
Each statement in itself is always ATOMIC (that is it executes or it
doesn't - black or white)

Note that COMMIT and ROLLBACK are global operations. If you want to
achieve atomicity within a procedure use BEGIN ATOMIC ... END or use
SAVEPOINTs.

Cheers
Serge

Hi!

So if I understand correctly:
Every SQL in a procedure is one unit of work. The BEGIN ATOMIC block is
there so that every SQL statement in the block gets commited or rolled back
if something goes wrong.
So in my example I would see 13 rows in step 3 (10 original and 3 that user
inserted while I was doing some work). Right ?

Best regards,
Kovi

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mi kropis.si |
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Feb 28 '06 #5
Gregor Kovač wrote:
Serge Rielau wrote:
Gregor Kovač wrote:
s.*********@goo glemail.com wrote:

By default, You will see 13 rows ...

Whether you allow the inserts into the table when you have a cursor (of
step 1 open) is based on the Isolation level of the Stored Proc
package or if you have explicityly defined at the statement level ..

http://publib.boulder.ibm.com/infoce...n/c0007870.htm gives details of isolation levels.

BTW, the default isolation level is CS .

HTH

Sathyaram
Thanks for the info, but..
In a SQL stored procedure is every single SQL statement unit of work or
is the entire procedure one unit of work?

An SQL Procedure is merely an extension of your application. You can
COMMIT and ROLLBACK as you please. You can use SAVEPOINTS as well.
BEGIN ATOMIC.... END is a shorthand for a savepoint.
Each statement in itself is always ATOMIC (that is it executes or it
doesn't - black or white)

Note that COMMIT and ROLLBACK are global operations. If you want to
achieve atomicity within a procedure use BEGIN ATOMIC ... END or use
SAVEPOINTs.

Cheers
Serge

Hi!

So if I understand correctly:
Every SQL in a procedure is one unit of work. The BEGIN ATOMIC block is
there so that every SQL statement in the block gets commited or rolled back
if something goes wrong.
So in my example I would see 13 rows in step 3 (10 original and 3 that user
inserted while I was doing some work). Right ?

Maybe :-)
This has nothing to do with your transaction, but with your ISOLATION.
Let's assume you are using CURSOR STABILITY (aka READ COMMITTED).
That means at any given point in time you will see the latest committed
data. So if that other transaction commits their INSERT BEFORE you do
the second select you will see the new rows. If the other transaction
does NOT commit (as in using the COMMIT statement, nothing else!) you
will not see the rows (assuming you have DB2_SKIPINSERTE D set) or your
procedure will WAIT for the other transactions commit or rollback.

READ STABILITY will work the same as CS. In case of REPEATABLE READ (the
highest isolation level. You are guaranteed to see the exact same 10
rows every time.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 28 '06 #6
Serge Rielau wrote:
Gregor Kovač wrote:
Serge Rielau wrote:
Gregor Kovač wrote:
s.*********@goo glemail.com wrote:

> By default, You will see 13 rows ...
>
> Whether you allow the inserts into the table when you have a cursor
> (of
> step 1 open) is based on the Isolation level of the Stored Proc
> package or if you have explicityly defined at the statement level ..
>
>

http://publib.boulder.ibm.com/infoce...n/c0007870.htm
> gives details of isolation levels.
>
> BTW, the default isolation level is CS .
>
> HTH
>
> Sathyaram
Thanks for the info, but..
In a SQL stored procedure is every single SQL statement unit of work or
is the entire procedure one unit of work?
An SQL Procedure is merely an extension of your application. You can
COMMIT and ROLLBACK as you please. You can use SAVEPOINTS as well.
BEGIN ATOMIC.... END is a shorthand for a savepoint.
Each statement in itself is always ATOMIC (that is it executes or it
doesn't - black or white)

Note that COMMIT and ROLLBACK are global operations. If you want to
achieve atomicity within a procedure use BEGIN ATOMIC ... END or use
SAVEPOINTs.

Cheers
Serge

Hi!

So if I understand correctly:
Every SQL in a procedure is one unit of work. The BEGIN ATOMIC block is
there so that every SQL statement in the block gets commited or rolled
back if something goes wrong.
So in my example I would see 13 rows in step 3 (10 original and 3 that
user inserted while I was doing some work). Right ?

Maybe :-)
This has nothing to do with your transaction, but with your ISOLATION.
Let's assume you are using CURSOR STABILITY (aka READ COMMITTED).
That means at any given point in time you will see the latest committed
data. So if that other transaction commits their INSERT BEFORE you do
the second select you will see the new rows. If the other transaction
does NOT commit (as in using the COMMIT statement, nothing else!) you
will not see the rows (assuming you have DB2_SKIPINSERTE D set) or your
procedure will WAIT for the other transactions commit or rollback.

READ STABILITY will work the same as CS. In case of REPEATABLE READ (the
highest isolation level. You are guaranteed to see the exact same 10
rows every time.

Cheers
Serge


Great.. This really clears few things for me .. :)

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mi kropis.si |
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Feb 28 '06 #7
Gregor KovaÄŤ wrote:
Hi!

If I create a procedure with BEGIN ATOMIC block I know these means that
when an error occurs all the SQL statements will be rolled back.
The question I have is:
If I select all records form a table and process them. The processing of
those records takes about 1 minute for example. In the meanwhile new
records come into the same table. If I issues the same SQL statement will
I see these new rows too ?


It depends on your isolation level. If you have "repeatable read", DB2 will
ensure that you won't see those new 'phantom' rows.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Feb 28 '06 #8
Gregor KovaÄŤ wrote:
So if I understand correctly:
Every SQL in a procedure is one unit of work.


No. A unit of work begins implicitly after the last transaction was ended
and it ends at COMMIT or ROLLBACK (or when something unwanted like a lost
connection or deadlock happens).

SQL statements are a level deeper, inside the unit of work (aka
transaction).

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Feb 28 '06 #9

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

Similar topics

6
2487
by: pauldepstein | last post by:
I am reading Grimshaw and Ortega's "C++ and Numerical Methods." They construct a vector class which contains the variable vec, a float* variable where the length of the array (number of components in the vector) is given by the variable name veclength. That is what I _do_ understand. What I don't understand is the coding for the default constructor which includes vec=0; What does it mean for a pointer to be equal to 0? Presumably...
6
5122
by: DH | last post by:
I have a VERY basic question about figuring database size. I've inherited a database which is generally similar to this basic one: Item, Red, Blue, Green, Yellow (text), (int),(int),(int),(int) box, 1,0,0,2 hat, 0,0,0,1 car, 3,0,0,0 This format leads to a lot of zeros in the rows which take up a lot of
9
2244
by: Malcolm | last post by:
After some days' hard work I am now the proud possessor of an ANSI C BASIC interpreter. The question is, how is it most useful? At the moment I have a function int basic(const char *script, FILE *in, FILE *out, FILE *err); It returns 0 on success or -1 on fail.
4
2235
by: Ramesh | last post by:
hi, Let me ask some basic questions. Can anybody explain me about the following questions: 1. When we have to create sn key? Whenever we compiled Component we have to create or it is a one time process? 2. What information contained in sn key. I gone through that it is having public key. How it is using this key to intract with client. 3. When we have to run gacutil.exe file. Whenever we
13
15575
by: Pete | last post by:
I'm cross posting from mscom.webservices.general as I have received no answer there: There has been a number of recent posts requesting how to satisfactorily enable BASIC authorization at the HTTP level but as yet no fully useful answer. I too have been trying to call an apache/axis webservice which desires a username/password from my C# Client. (ie the equivalent of _call.setUsername("Myname") name from within a Java client proxy)...
5
1815
by: Aussie Rules | last post by:
Hi, Having a mental block on this one. Have done it before but can't rack my brain on how... I have an object, with a bunch on property, and I add that object to a combo box. I want the property '.fulladdress' to be the value that appears in the drop downs text section. How to I set that parameter to be the one shown inthe drop down
4
1736
by: MikeB | last post by:
I've been all over the net with this question, I hope I've finally found a group where I can ask about Visual Basic 2005. I'm at uni and we're working with Visual Basic 2005. I have some books, - Programming Visual Basic by Balena (MS Press) and - Visual Basic 2005 by Willis (WROX), but they don't go into the forms design aspects and describing the various controls at all. What bookscan I get that will cover that?
4
3106
by: Chris Asaipillai | last post by:
Hi there My compay has a number of Visual Basic 6 applications which are front endeed onto either SQL Server or Microsoft Access databases. Now we are in process of planning to re-write these applications into Visual Basic.Net. My managers main thought is that Visual Basic 6 is (or has!) stopped being supported by Microsoft.
3
1946
by: Scott Stark | last post by:
Hello, I'm trying to get a better handle on OOP programming principles in VB.NET. Forgive me if this question is sort of basic, but here's what I want to do. I have a collection of Employee objects that I can iterate through relatively easily. I've included code at the bottom of this message. I can pretty easily iterate through my employee objects like so: Dim theEmployees As Employees = New Employees
0
10852
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10553
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
10596
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
9382
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
7793
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
6980
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
5829
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4459
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4021
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.