DB2 V9.5 on AIX,
This would be running on C code..The following query:

SELECT * FROM table1 WHERE col1 = 3 FETCH FIRST 10 ROWS ONLY

or

SELECT * FROM table1 WHERE col1 = 3 FETCH FIRST 10 ROWS ONLY FOR
UPDATE OF col2, col3

If the table actually has 100,000 records that match these queries, is
there any locking done on the rows other than the first 10....will DB2
only lock the first 10 rows?

"shorti" <lbryan21 (AT) juno (DOT) com> wrote

Depends on the access plan. If a table scan is needed, all rows may be
locked. If the rows are accessed via an index, then only rows accessed have
to be locked.

On Nov 10, 12:42*pm, "Mark A" <no... (AT) nowhere (DOT) com> wrote:

index scan is what I was interested in. Are the other 99,990 rows
that match the query not considered "accessed" if it is an index scan?

"shorti" <lbryan21 (AT) juno (DOT) com> wrote

Assuming that there is no sort required to determine which 10 rows to
return, then I would think that only the first 10 rows will be locked. But
you can easily do a test on that by submitting SQL via the command line and
turn auto-commit off, to see how many rows (or which rows) are locked.

On 11/10/2010 5:47 PM, shorti wrote:
What comes into play here is the quality of the predicate.
DB2 will avoid locking rows as long as it can and a simple predicate
such as col1 = 3 will be evaluated before the row is even brought into
the bufferpool.
The FFnR property will also cut of the scan once 10 rows have been
qualified.
So you will not see overlocking here.

Cheers
Serge


--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

On Nov 10, 5:21*pm, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:
Thanks for the help.