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 Bogdan Calmac <bc...@gmail.com> on 2008/05/07 17:00:44 UTC
SELECT places shared locks in READ_UNCOMMITTED insolation level?
Derby documentation specifies that SELECT statements do not place any locks
in the READ_UNCOMMITTED isolation level. However, the query plan shows that
it uses shared locks (which is also confirmed by application behaviour). So
is the documentation wrong or is this a bug?
This is derby 10.2.2.0, see the query plan below:
2008-05-07 14:21:39.990 GMT Thread[ExportWorker 2,5,ArchiveGroup] (XID =
583431), (SESSIONID = 17), select ID, AGENT_SESSION_ID, SEQUENCE_NO,
SCRIPT_SESSION_ID, EVENT_TIME, EVENT_TIME_SRV, L1, L2, L3, L4, L5, TOPIC_ID,
OBJECT_ID, TEXT, ATTRIBUTES from TRACK_EVENT where AGENT_SESSION_ID in
(43909,43914,43921,43922,43923,43924,43925,43926,43927,43930,43931,43932,43933,43934,43935,43936,43937,43938,43939,43940)
******* Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 63645
Rows filtered = 0
restriction = true
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: 16824.30
optimizer estimated cost: 127904.69
Source result set:
Index Row to Base Row ResultSet for TRACK_EVENT:
Number of opens = 1
Rows seen = 63645
Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,
12, 13, 14}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 16824.30
optimizer estimated cost: 127904.69
Index Scan ResultSet for TRACK_EVENT using index TE_IDX_SESSION
at read uncommitted isolation level using share row locking chosen by the
optimizer
Number of opens = 1
Rows seen = 63645
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, 2}
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=512
Number of rows qualified=63645
Number of rows visited=63646
Scan type=btree
Tree height=4
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
0
qualifiers:
None
optimizer estimated row count: 16824.30
optimizer estimated cost: 127904.69
--
View this message in context: http://www.nabble.com/SELECT-places-shared-locks-in-READ_UNCOMMITTED-insolation-level--tp17106210p17106210.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: SELECT places shared locks in READ_UNCOMMITTED insolation level?
Posted by Mike Matrigali <mi...@sbcglobal.net>.
I am not sure if derby documents the meaning of the line you are
referring to in the query plan:
at read uncommitted isolation level using share row locking chosen by the
> optimizer.
I think this is what it always says for read uncommitted, as is up to
a lower part of the code to not get the read locks. Could you describe
the behavior you are seeing or give a small test case that could be run.
Note Derby's implementation of read uncommitted does get table level
intent locks, so if you try operations that depend on table level
locking you will get blocking. The system is set up such that the top
level optimizer passes down the isolation level and then the information
about row vs. table and share vs exclusive down to a lower level. At
the lower level if things are working right the code makes a share row
lock call that goes into the read uncommitted module and then no actual
read lock is requested.
There have been significant improvements to IN handling in 10.3/10.4 so
you may want to try that.
Bogdan Calmac wrote:
> Derby documentation specifies that SELECT statements do not place any locks
> in the READ_UNCOMMITTED isolation level. However, the query plan shows that
> it uses shared locks (which is also confirmed by application behaviour). So
> is the documentation wrong or is this a bug?
>
> This is derby 10.2.2.0, see the query plan below:
>
> 2008-05-07 14:21:39.990 GMT Thread[ExportWorker 2,5,ArchiveGroup] (XID =
> 583431), (SESSIONID = 17), select ID, AGENT_SESSION_ID, SEQUENCE_NO,
> SCRIPT_SESSION_ID, EVENT_TIME, EVENT_TIME_SRV, L1, L2, L3, L4, L5, TOPIC_ID,
> OBJECT_ID, TEXT, ATTRIBUTES from TRACK_EVENT where AGENT_SESSION_ID in
> (43909,43914,43921,43922,43923,43924,43925,43926,43927,43930,43931,43932,43933,43934,43935,43936,43937,43938,43939,43940)
> ******* Project-Restrict ResultSet (3):
> Number of opens = 1
> Rows seen = 63645
> Rows filtered = 0
> restriction = true
> 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: 16824.30
> optimizer estimated cost: 127904.69
>
> Source result set:
> Index Row to Base Row ResultSet for TRACK_EVENT:
> Number of opens = 1
> Rows seen = 63645
> Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,
> 12, 13, 14}
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 0
> close time (milliseconds) = 0
> optimizer estimated row count: 16824.30
> optimizer estimated cost: 127904.69
>
> Index Scan ResultSet for TRACK_EVENT using index TE_IDX_SESSION
> at read uncommitted isolation level using share row locking chosen by the
> optimizer
> Number of opens = 1
> Rows seen = 63645
> 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, 2}
> Number of columns fetched=2
> Number of deleted rows visited=0
> Number of pages visited=512
> Number of rows qualified=63645
> Number of rows visited=63646
> Scan type=btree
> Tree height=4
> start position:
> >= on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> stop position:
> > on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> qualifiers:
> None
> optimizer estimated row count: 16824.30
> optimizer estimated cost: 127904.69
>
Re: how to get names of column constraints ?
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Maris Orbidans <ma...@ingg.com> writes:
> Yes, please run attached class.
Thanks, Maris, I can reproduce your problem. I think the issue here is
that in Derby, an index may support more than one constraint, so there
is not necessarily a one-to-one relationship here.
So I am not convinced that the assumption than HA-JDBC makes here is
warranted. It would seem that JDBC should have supported a
getConstraintInfo call also...
In the case of the primary key, there is no named constraint, so
Derbys underlying index name ("SQL<big number>") is used as a
constraint name (and you can thus drop it). In the named constraint
case, the underlying index name is not exposed and the drop fails. I
agree this seems a bit un-orthogonal.
Does anyone have more insight on this?
Dag
Re: how to get names of column constraints ?
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Maris,
When you declare a constraint, Derby creates a backing index for that
constraint. If you do not explicitly name the constraint, then Derby
will make up a name. The constraint name which Derby makes up is the
same name that Derby gives to the backing index. If you don't name your
constraints, then you can use DatabaseMetaData.getIndexInfo() to find
the system-generated constraint names. However, if you do name your
constraints (as you did below with a unique constraint named
RECEIPT_REC_REF), then the backing index name will not match the
constraint name. For primary key constraints, you can use
DatabaseMetaData.getPrimaryKeys() to find the correct name for primary
keys (either user supplied or system generated). I don't see parallel
DatabaseMetaData methods for unique and foreign key constraints.
I am attaching two files which demonstrate this behavior:
DBMetaData.java - This is a compact set of VTIs which present some
DatabaseMetaData methods as table functions.
dbmetadata.sql - This script declares the table functions and uses them
to present getIndexInfo() and getPrimaryKey() information alongside
queries against the Derby catalogs.
Hope this helps,
-Rick
Maris Orbidans wrote:
> Yes, please run attached class.
>
> Using metadata it finds those two indexes and fails to drop last one.
>
> Table: RECEIPT Index: SQL080509095407610
> Table: RECEIPT Index: SQL080509095407630
> java.sql.SQLSyntaxErrorException: ALTER TABLE failed. There is no
> constraint 'APP.SQL080509095407630' on table '"APP"."RECEIPT"'.
>
> If you use method dropUsingSysconstraints(con) instead dropUsingMetadata
> then everything works.
>
> SQL080509095407610
> RECEIPT_REC_REF
>
> Maris
>
> -----Original Message-----
> From: Dag.Wanvik@Sun.COM [mailto:Dag.Wanvik@Sun.COM]
> Sent: 08 May 2008 14:32
> To: Derby Discussion
> Subject: Re: how to get names of column constraints ?
>
> Maris Orbidans <ma...@ingg.com> writes:
>
>
>> I know I can read constraint names from SYS.SYSCONSTRAINTS but can I
>>
> get
>
>> it from DatabaseMetaData?
>>
>> I am trying to get ha-jdbc synchronization working with my derby
>> database. The problem is that ha-jdbc reads INDEX_NAMEs from
>> DatabaseMetaData (with getIndexInfo) and assumes that column
>>
> constraint
>
>> has the same name and tries to drop it.
>>
>
> Not sure what your exact problem is, can you post a standalone repro?
> I tried the following which worked:
>
> st.execute("create table mytab (id int primary key, " +
> "name varchar(50))");
> :
> s = con.createStatement();
> DatabaseMetaData dbm = con.getMetaData();
>
> rs = dbm.getIndexInfo(null, null, "MYTAB", false, false);
>
> while (rs.next()) {
> System.out.println("Table: " + rs.getString(3) + " Index: " +
> rs.getString(6));
> s.executeUpdate("alter table " + rs.getString(3) + " drop
> constraint "
> + rs.getString(6));
> }
>
> Dag
>
> _____________________________________________________________________
> This e-mail has been scanned for viruses by MessageLabs.
>
> **********************************************************************
> Confidentiality : This e-mail and any attachments are intended for the addressee only and may be confidential. If they come to you in error you must take no action based on them, nor must you copy or show them to anyone. Please advise the sender by replying to this e-mail immediately and then delete the original from your computer.
>
> Opinion : Any opinions expressed in this e-mail are entirely those of the author and unless specifically stated to the contrary, are not necessarily those of the author’s employer.
>
> Security Warning : Internet e-mail is not necessarily a secure communications medium and can be subject to data corruption. We advise that you consider this fact when e-mailing us.
>
> Viruses : We have taken steps to ensure that this e-mail and any attachments are free from known viruses but in keeping with good computing practice, you should ensure that they are virus free.
>
> Inspired Gaming (UK) Limited
> Registered in England No 3565640
> Registered Office 3 The Maltings Wetmore Road, Burton On Trent, Staffordshire DE14 1SE
> _______________________________________________________________
> This message has been checked for all known viruses by the MessageLabs Virus Control Centre.
>
RE: how to get names of column constraints ?
Posted by Maris Orbidans <ma...@ingg.com>.
Yes, please run attached class.
Using metadata it finds those two indexes and fails to drop last one.
Table: RECEIPT Index: SQL080509095407610
Table: RECEIPT Index: SQL080509095407630
java.sql.SQLSyntaxErrorException: ALTER TABLE failed. There is no
constraint 'APP.SQL080509095407630' on table '"APP"."RECEIPT"'.
If you use method dropUsingSysconstraints(con) instead dropUsingMetadata
then everything works.
SQL080509095407610
RECEIPT_REC_REF
Maris
-----Original Message-----
From: Dag.Wanvik@Sun.COM [mailto:Dag.Wanvik@Sun.COM]
Sent: 08 May 2008 14:32
To: Derby Discussion
Subject: Re: how to get names of column constraints ?
Maris Orbidans <ma...@ingg.com> writes:
> I know I can read constraint names from SYS.SYSCONSTRAINTS but can I
get
> it from DatabaseMetaData?
>
> I am trying to get ha-jdbc synchronization working with my derby
> database. The problem is that ha-jdbc reads INDEX_NAMEs from
> DatabaseMetaData (with getIndexInfo) and assumes that column
constraint
> has the same name and tries to drop it.
Not sure what your exact problem is, can you post a standalone repro?
I tried the following which worked:
st.execute("create table mytab (id int primary key, " +
"name varchar(50))");
:
s = con.createStatement();
DatabaseMetaData dbm = con.getMetaData();
rs = dbm.getIndexInfo(null, null, "MYTAB", false, false);
while (rs.next()) {
System.out.println("Table: " + rs.getString(3) + " Index: " +
rs.getString(6));
s.executeUpdate("alter table " + rs.getString(3) + " drop
constraint "
+ rs.getString(6));
}
Dag
_____________________________________________________________________
This e-mail has been scanned for viruses by MessageLabs.
**********************************************************************
Confidentiality : This e-mail and any attachments are intended for the addressee only and may be confidential. If they come to you in error you must take no action based on them, nor must you copy or show them to anyone. Please advise the sender by replying to this e-mail immediately and then delete the original from your computer.
Opinion : Any opinions expressed in this e-mail are entirely those of the author and unless specifically stated to the contrary, are not necessarily those of the author�s employer.
Security Warning : Internet e-mail is not necessarily a secure communications medium and can be subject to data corruption. We advise that you consider this fact when e-mailing us.
Viruses : We have taken steps to ensure that this e-mail and any attachments are free from known viruses but in keeping with good computing practice, you should ensure that they are virus free.
Inspired Gaming (UK) Limited
Registered in England No 3565640
Registered Office 3 The Maltings Wetmore Road, Burton On Trent, Staffordshire DE14 1SE
_______________________________________________________________
This message has been checked for all known viruses by the MessageLabs Virus Control Centre.
Re: how to get names of column constraints ?
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Maris Orbidans <ma...@ingg.com> writes:
> I know I can read constraint names from SYS.SYSCONSTRAINTS but can I get
> it from DatabaseMetaData?
>
> I am trying to get ha-jdbc synchronization working with my derby
> database. The problem is that ha-jdbc reads INDEX_NAMEs from
> DatabaseMetaData (with getIndexInfo) and assumes that column constraint
> has the same name and tries to drop it.
Not sure what your exact problem is, can you post a standalone repro?
I tried the following which worked:
st.execute("create table mytab (id int primary key, " +
"name varchar(50))");
:
s = con.createStatement();
DatabaseMetaData dbm = con.getMetaData();
rs = dbm.getIndexInfo(null, null, "MYTAB", false, false);
while (rs.next()) {
System.out.println("Table: " + rs.getString(3) + " Index: " + rs.getString(6));
s.executeUpdate("alter table " + rs.getString(3) + " drop constraint "
+ rs.getString(6));
}
Dag
how to get names of column constraints ?
Posted by Maris Orbidans <ma...@ingg.com>.
I know I can read constraint names from SYS.SYSCONSTRAINTS but can I get
it from DatabaseMetaData?
I am trying to get ha-jdbc synchronization working with my derby
database. The problem is that ha-jdbc reads INDEX_NAMEs from
DatabaseMetaData (with getIndexInfo) and assumes that column constraint
has the same name and tries to drop it.
DEBUG net.sf.hajdbc.sync.SynchronizationSupport - ALTER TABLE
APP.RECEIPT DROP CONSTRAINT SQL080507133401610
ERROR net.sf.hajdbc.sql.AbstractDatabaseCluster - Error for batch
element #0: ALTER TABLE failed. There is no constraint
'APP.SQL080507133401610' on table '"APP"."RECEIPT"'.
Maris
**********************************************************************
Confidentiality : This e-mail and any attachments are intended for the addressee only and may be confidential. If they come to you in error you must take no action based on them, nor must you copy or show them to anyone. Please advise the sender by replying to this e-mail immediately and then delete the original from your computer.
Opinion : Any opinions expressed in this e-mail are entirely those of the author and unless specifically stated to the contrary, are not necessarily those of the author�s employer.
Security Warning : Internet e-mail is not necessarily a secure communications medium and can be subject to data corruption. We advise that you consider this fact when e-mailing us.
Viruses : We have taken steps to ensure that this e-mail and any attachments are free from known viruses but in keeping with good computing practice, you should ensure that they are virus free.
Inspired Gaming (UK) Limited
Registered in England No 3565640
Registered Office 3 The Maltings Wetmore Road, Burton On Trent, Staffordshire DE14 1SE
_______________________________________________________________
This message has been checked for all known viruses by the MessageLabs Virus Control Centre.