Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of lists@nabble.com designates
 216.139.236.26 as permitted sender)
Message-ID: <33240337.post@talk.nabble.com>
Date: Tue, 31 Jan 2012 16:01:32 -0800 (PST)
From: anthonyri <anthony.rich@inbox.com>
To: derby-user@db.apache.org
Subject: Can't seem to force table level locking
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


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.