By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,287 Members | 1,287 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,287 IT Pros & Developers. It's quick & easy.

Basic SQL prcoedure question

P: n/a
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**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Feb 27 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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

P: n/a
s.*********@googlemail.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**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Feb 28 '06 #3

P: n/a
Gregor KovaÄŤ wrote:
s.*********@googlemail.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

P: n/a
Serge Rielau wrote:
Gregor Kovač wrote:
s.*********@googlemail.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**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Feb 28 '06 #5

P: n/a
Gregor Kovač wrote:
Serge Rielau wrote:
Gregor Kovač wrote:
s.*********@googlemail.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_SKIPINSERTED 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

P: n/a
Serge Rielau wrote:
Gregor Kovač wrote:
Serge Rielau wrote:
Gregor Kovač wrote:
s.*********@googlemail.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_SKIPINSERTED 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**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Feb 28 '06 #7

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.