Basic SQL prcoedure question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gregor Kovač - 27 Feb 2006 17:35 GMT

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

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

Reply to this Message

s.sathyaram@googlemail.com - 27 Feb 2006 18:01 GMT

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/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.
udb.doc/admin/c0007870.htm

gives details of isolation levels.

BTW, the default isolation level is CS .

HTH

Sathyaram

Reply to this Message

Gregor Kovač - 28 Feb 2006 08:41 GMT

> 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/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.
udb.doc/admin/c0007870.htm

> gives details of isolation levels.
>
[quoted text clipped - 3 lines]
>
> 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

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

Reply to this Message

Serge Rielau - 28 Feb 2006 10:42 GMT

>> By default, You will see 13 rows ...
>>
[quoted text clipped - 14 lines]
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Gregor Kovač - 28 Feb 2006 10:56 GMT

>>> 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/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.
udb.doc/admin/c0007870.htm

>>> gives details of isolation levels.
>>>
[quoted text clipped - 18 lines]
> 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

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

Reply to this Message

Serge Rielau - 28 Feb 2006 11:21 GMT

>>>> By default, You will see 13 rows ...
>>>>
[quoted text clipped - 33 lines]
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Gregor Kovač - 28 Feb 2006 11:41 GMT

>>>>> 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/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.
udb.doc/admin/c0007870.htm

>>>>> gives details of isolation levels.
>>>>>
[quoted text clipped - 42 lines]
> Cheers
> Serge

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

Best regards,
Kovi

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

Reply to this Message

Knut Stolze - 28 Feb 2006 17:37 GMT

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

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Knut Stolze - 28 Feb 2006 17:34 GMT

> Hi!
>
[quoted text clipped - 5 lines]
> 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.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message