473,320 Members | 1,955 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Locking issue

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
6 1513
"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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: Alban Hertroys | last post by:
Good day, I have a number of threads doing inserts in a table, after which I want to do a select. This means that it will occur that the row that I want to select is locked (by the DB). In these...
3
by: Ryan | last post by:
I have a problem with record locking / blocking within an application. The app is quite straight forward. Written in Delphi 5 using BDE to access a SQL 7 database (Win2K server). Every so often...
9
by: john smile | last post by:
Hi All, I want to lock 2 tables on 2 servers using TABLOCKX hint. These tables function as semaphores in my application. It means when the tables are locked then other users will not be able to...
16
by: Nid | last post by:
How do I do row-level locking on SQL Server? Thanks, Nid
4
by: darrel | last post by:
I've been dealing with a file locking issue for a while. Our CMS spits out a new XML file each time an item in the DB is updated. This XML file is basically our site menu, and is what we use on...
2
by: tim | last post by:
I am really new to ASP.NET and I was wondering if most developers use locking hints when accessing the data in SQL Server. What kind of multi-user issues come up when using an ASP.NET application?
1
by: ABrown | last post by:
Hello, I have a 2003 database set up with about 20 users (only about 4 at a time) but I repeatedly get a problem with the records all locking. Each User is assigning billing codes to jobs so they...
1
by: Paul H | last post by:
I have an Employees table with the following fields: EmployeeID SupervisorID Fred Bob Bob John Bob Mary Bill Bill I have created a self join in...
7
dlite922
by: dlite922 | last post by:
I need to do some sort of Locking mechanism at interface level, instead of DB Level. I know how MySQL table locking works, but that won't work in my scenerio. Requirements: When someone is...
9
by: zmickle | last post by:
Experts and books all say that you can share an Access back end on a shared drive with the front end running on each host computer. I have a simple database that tracks student data and it is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, youll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shllpp 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.