Locking issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gregor Kovač - 27 Mar 2006 09:59 GMT

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

Signature

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

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

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

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

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

Reply to this Message

Mark A - 27 Mar 2006 13:07 GMT

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

Reply to this Message

Serge Rielau - 27 Mar 2006 15:05 GMT

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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Gregor Kovač - 27 Mar 2006 15:37 GMT

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

Signature

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

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

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

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

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

Reply to this Message

Serge Rielau - 27 Mar 2006 18:05 GMT

>>> Hi!
>>>
[quoted text clipped - 19 lines]
> PREPARE S1 FROM SQLSTMT;
> EXECUTE S1;

FOR loops are cursors

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Gregor Kovač - 28 Mar 2006 07:57 GMT

>>>> Hi!
>>>>
[quoted text clipped - 20 lines]
>> EXECUTE S1;
> FOR loops are cursors

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

Thanks a million times :)

Signature

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

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

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

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

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

Reply to this Message

Gregor Kovač - 31 Mar 2006 10:16 GMT

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

Signature

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

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

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

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

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

Reply to this Message