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

Locking issue

P: n/a
Hi!

Let's say that I have a table TABLE1 in schema A and in SCHEMA B.
I have a stored procedure that does a select from A.TABLE1 and inserts rows
from that select in B.TABLE1.
When the procedure is running and I do a SELECT * FROM A.TABLE1 then this
statement get's locked. Why?

Any pointers to the docs I should read ? :)

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Mar 27 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"Gregor Kovac" <gr**********@mikropis.si> wrote in message
news:wP*******************@news.siol.net...
Hi!

Let's say that I have a table TABLE1 in schema A and in SCHEMA B.
I have a stored procedure that does a select from A.TABLE1 and inserts
rows
from that select in B.TABLE1.
When the procedure is running and I do a SELECT * FROM A.TABLE1 then this
statement get's locked. Why?

Any pointers to the docs I should read ? :)

Best regards,
Kovi
--


Statements do no get locked, rows or tables get locked. The only exception
is that the there is some locking going on for the package, but you should
probably ignore that unless you have some specific problem with that..

The table that is being selected will have a share lock on the rows
selected, and the table inserted will have an exclusive lock on the rows
inserted.

Share locks are released depending on the isolation level. For CS (cursor
stability), share locks will released when the cursor moves off the row to
the next row.

Exclusive locks will be released when a commit or rollback is taken.

Locking is probably discussed in the Administration:Planning and/or
Administration:Implementation manuals.
Mar 27 '06 #2

P: n/a
Gregor KovaÄŤ wrote:
Hi!

Let's say that I have a table TABLE1 in schema A and in SCHEMA B.
I have a stored procedure that does a select from A.TABLE1 and inserts rows
from that select in B.TABLE1.
When the procedure is running and I do a SELECT * FROM A.TABLE1 then this
statement get's locked. Why?

Any pointers to the docs I should read ? :)

I take a wild guess here and assume you use a cursor to select from
A.table1? Append FOR READ ONLY to the cursor.
The cursor is "ambiguous" in the sense that it could be updated.
Depending on your settings DB2 may either default to FOR UPDATE or FOR
READ ONLY. If it defaults to FOR UPDATE DB2 will acquire update locks.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 27 '06 #3

P: n/a
Serge Rielau wrote:
Gregor Kovač wrote:
Hi!

Let's say that I have a table TABLE1 in schema A and in SCHEMA B.
I have a stored procedure that does a select from A.TABLE1 and inserts
rows from that select in B.TABLE1.
When the procedure is running and I do a SELECT * FROM A.TABLE1 then this
statement get's locked. Why?

Any pointers to the docs I should read ? :)

I take a wild guess here and assume you use a cursor to select from
A.table1? Append FOR READ ONLY to the cursor.
The cursor is "ambiguous" in the sense that it could be updated.
Depending on your settings DB2 may either default to FOR UPDATE or FOR
READ ONLY. If it defaults to FOR UPDATE DB2 will acquire update locks.

Cheers
Serge


Well, not exactly. The procedure has couple of FOR loops, not CURSORs. The
truth is that the procedure itself is building SQL statements that are then
executed via
PREPARE S1 FROM SQLSTMT;
EXECUTE S1;

Maybe this is the problem?

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Mar 27 '06 #4

P: n/a
Gregor Kovač wrote:
Serge Rielau wrote:
Gregor Kovač wrote:
Hi!

Let's say that I have a table TABLE1 in schema A and in SCHEMA B.
I have a stored procedure that does a select from A.TABLE1 and inserts
rows from that select in B.TABLE1.
When the procedure is running and I do a SELECT * FROM A.TABLE1 then this
statement get's locked. Why?

Any pointers to the docs I should read ? :)

I take a wild guess here and assume you use a cursor to select from
A.table1? Append FOR READ ONLY to the cursor.
The cursor is "ambiguous" in the sense that it could be updated.
Depending on your settings DB2 may either default to FOR UPDATE or FOR
READ ONLY. If it defaults to FOR UPDATE DB2 will acquire update locks.

Cheers
Serge


Well, not exactly. The procedure has couple of FOR loops, not CURSORs. The
truth is that the procedure itself is building SQL statements that are then
executed via
PREPARE S1 FROM SQLSTMT;
EXECUTE S1;

FOR loops are cursors
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 27 '06 #5

P: n/a
Serge Rielau wrote:
Gregor Kovač wrote:
Serge Rielau wrote:
Gregor Kovač wrote:
Hi!

Let's say that I have a table TABLE1 in schema A and in SCHEMA B.
I have a stored procedure that does a select from A.TABLE1 and inserts
rows from that select in B.TABLE1.
When the procedure is running and I do a SELECT * FROM A.TABLE1 then
this statement get's locked. Why?

Any pointers to the docs I should read ? :)
I take a wild guess here and assume you use a cursor to select from
A.table1? Append FOR READ ONLY to the cursor.
The cursor is "ambiguous" in the sense that it could be updated.
Depending on your settings DB2 may either default to FOR UPDATE or FOR
READ ONLY. If it defaults to FOR UPDATE DB2 will acquire update locks.

Cheers
Serge


Well, not exactly. The procedure has couple of FOR loops, not CURSORs.
The truth is that the procedure itself is building SQL statements that
are then executed via
PREPARE S1 FROM SQLSTMT;
EXECUTE S1;

FOR loops are cursors

hmm.. that explains a lot of things :)))

Thanks a million times :)
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Mar 28 '06 #6

P: n/a
Gregor Kovač wrote:
Serge Rielau wrote:
Gregor Kovač wrote:
Serge Rielau wrote:

Gregor Kovač wrote:
> Hi!
>
> Let's say that I have a table TABLE1 in schema A and in SCHEMA B.
> I have a stored procedure that does a select from A.TABLE1 and inserts
> rows from that select in B.TABLE1.
> When the procedure is running and I do a SELECT * FROM A.TABLE1 then
> this statement get's locked. Why?
>
> Any pointers to the docs I should read ? :)
I take a wild guess here and assume you use a cursor to select from
A.table1? Append FOR READ ONLY to the cursor.
The cursor is "ambiguous" in the sense that it could be updated.
Depending on your settings DB2 may either default to FOR UPDATE or FOR
READ ONLY. If it defaults to FOR UPDATE DB2 will acquire update locks.

Cheers
Serge

Well, not exactly. The procedure has couple of FOR loops, not CURSORs.
The truth is that the procedure itself is building SQL statements that
are then executed via
PREPARE S1 FROM SQLSTMT;
EXECUTE S1;

FOR loops are cursors

hmm.. that explains a lot of things :)))

Thanks a million times :)

Hmm...

So if I have a FOR loop defined like this:
FOR FOR1 AS SELECT F1, F2 FROM TABLE1 DO
UDPATE TABLE2 SET B = FOR1.F1 WHERE ID = F2;
END FOR

how do I apply FOR READ ONLY to it?
Maybe:
FOR FOR1 AS SELECT F1, F2 FROM TABLE1 FOR READ ONLY DO
UDPATE TABLE2 SET B = FOR1.F1 WHERE ID = F2;
END FOR
but these does not work.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Mar 31 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.