You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by anthonyri <an...@inbox.com> on 2012/02/01 01:01:32 UTC
Can't seem to force table level locking
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.
Re: Can't seem to force table level locking
Posted by Bryan Pendleton <bp...@gmail.com>.
> Yes, autocommit is turned off.
>
> Any other thoughts?
Well, I never had any trouble getting the LOCK TABLE feature to work,
so I'm not sure what's wrong.
One possibility is that the query plan output is misleading you.
That is, although the query plan output might indicate that the
optimizer is choosing row-level locking, and it might even be
requesting row-level locks when it runs, the locking system may
be quietly processing those locks as table-level locks and so
everything might be working fine, just confusing output from the
optimizer dumps.
Do you have a reason to believe that your table is not being
locked at table level? For example, you see unexpected concurrent
accesses to the table?
Another thing you might try is to access some of the diagnostic
locking tools from within your transaction. For example, plant
a call to the LOCK_TABLE function and print out the results, and
see what locks it shows that you are *actually* holding.
thanks,
bryan
Re: Can't seem to force table level locking
Posted by anthonyri <an...@inbox.com>.
Brian,
Yes, autocommit is turned off.
Any other thoughts?
Regards,
Anthony
Bryan Pendleton-3 wrote:
>
>> 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
>
> Do you have autocommit turned off?
>
> Otherwise Derby is committing (and releasing your table lock) after step
> (1).
>
> thanks,
>
> bryan
>
>
--
View this message in context: http://old.nabble.com/Can%27t-seem-to-force-table-level-locking-tp33240337p33241503.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Can't seem to force table level locking
Posted by Bryan Pendleton <bp...@gmail.com>.
> 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
Do you have autocommit turned off?
Otherwise Derby is committing (and releasing your table lock) after step (1).
thanks,
bryan