Hi,
I have tried to force table level locking by;
1. SQL - lock table wayNodes6 in share mode
2. st.execute("call
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.locks.escalationThreshold','1')");
But the query execution plan always states it has chosen row level locking
as you can see below...
Can anyone spot my dumb error please?
===============================================
Tue Jan 31 23:56:19 GMT 2012 Thread[main,5,main] (XID = 3510808), (SESSIONID
= 1), lock table wayNodes6 in share mode ******* null
...
...
...
Tue Jan 31 23:56:31 GMT 2012 Thread[main,5,main] (XID = 3510818), (SESSIONID
= 1), select 1, 1, type, name, latGrid, lonGrid, latRight, lonRight from
wayNodes6 where ((latGrid between ? and ?) and (lonGrid between ? and ?))
and type < ? ******* Scroll Insensitive ResultSet:
Number of opens = 1
Rows seen = 64135
Number of reads from hash table = 64135
Number of writes to hash table = 64135
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 18613.63
optimizer estimated cost: 323585.02
Source result set:
Project-Restrict ResultSet (2):
Number of opens = 1
Rows seen = 64135
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 18613.63
optimizer estimated cost: 323585.02
Source result set:
Table Scan ResultSet for WAYNODES6 at read committed isolation
level using
instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 64135
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1, 4, 5, 6, 7}
Number of columns fetched=6
Number of pages visited=12132
Number of rows qualified=64135
Number of rows visited=902474
Scan type=heap
start position:
null
stop position:
null
qualifiers:
Column[0][0] Id: 0
Operator: <
Ordered nulls: false
Unknown return value: true
Negate comparison result: true
Column[0][1] Id: 0
Operator: <=
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][2] Id: 1
Operator: <
Ordered nulls: false
Unknown return value: true
Negate comparison result: true
Column[0][3] Id: 1
Operator: <=
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][4] Id: 4
Operator: <
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 18613.63
optimizer estimated cost: 323585.02
--
View this message in context:
http://old.nabble.com/Can%27t-seem-to-force-table-level-locking-tp33240337p33240337.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
