You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@jena.apache.org by Adrian Paleacu <ad...@gmail.com> on 2012/05/15 07:35:28 UTC
SDB deadlock
Hi everyone, I'm wonder if someone know about a possible deadlock in SDB
with this scenario. I'm not sure if is my app fault of SDB. :)
There are 2 objects which are involved in the deadlock:
- Nodes
- Quads
The statements which are failing are:
INSERT INTO Quads
SELECT DISTINCT #NQuads.t0 , #NQuads.t1 , #NQuads.t2 , #NQuads.t3
FROM #NQuads LEFT JOIN Quads ON
(t0=Quads.g AND t1=Quads.s AND t2=Quads.p AND t3=Quads.o)
WHERE
Quads.g IS NULL OR
Quads.s IS NULL OR
Quads.p IS NULL OR
Quads.o IS NULL
and
INSERT INTO Nodes (hash, lex, lang, datatype, type)
SELECT #NNodeQuads.n0 , #NNodeQuads.n1 , #NNodeQuads.n2 , #NNodeQuads.n3 ,
#NNodeQuads.n4
FROM #NNodeQuads LEFT JOIN Nodes ON (#NNodeQuads.n0=Nodes.hash)
WHERE Nodes.hash IS NULL
Process A has an exclusive lock on nodes and wants a share lock on quads
Process B has an exclusive lock on quads and wants a share lock on nodes
Hence the deadlock!!
regards,
Adrian
Re: SDB deadlock
Posted by Adrian Paleacu <ad...@gmail.com>.
Damian,
I would be more than happy to help but you came with the solution :)
My "fix" was to do a "select top 1 * from "+tableName on
the com.hp.hpl.jena.sdb.sql.TableUtils::hasTable() . Maybe the jtds driver
have some issues pulling info from
the connection.getMetaData().getTables().
The deadlock was reported but I don't have a fix yet and so far is not
reproducible anymore. Also some concurrency fixes were done in the
Application
that I presume is related to the deadlock.
Thank you Damian!
If you need more information please let me know,
Regards,
Adrian
[5/14/12 10:01:28:374 MDT] 00000088 LoaderTuplesN E
com.hp.hpl.jena.sdb.layout2.LoaderTuplesNodes$Commiter handleIssue Error in
thread: Exception flushing
java.sql.SQLException: Transaction
(Process ID 72) was deadlocked on lock resources with another process and
has been chosen as the deadlock victim. Rerun the transaction.
at
net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at
net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:723)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1166)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1119)
at
net.sourceforge.jtds.jdbcx.proxy.StatementProxy.executeUpdate(StatementProxy.java:69)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.pmiExecuteUpdate(WSJdbcStatement.java:1693)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeUpdate(WSJdbcStatement.java:1041)
at com.hp.hpl.jena.sdb.sql.SDBConnection.execUpdate(SDBConnection.java:153)
at
com.hp.hpl.jena.sdb.layout2.TupleLoaderBase.flush(TupleLoaderBase.java:202)
at
com.hp.hpl.jena.sdb.layout2.TupleLoaderBase.finish(TupleLoaderBase.java:155)
at
com.hp.hpl.jena.sdb.layout2.LoaderTuplesNodes.commitTuples(LoaderTuplesNodes.java:283)
at
com.hp.hpl.jena.sdb.layout2.LoaderTuplesNodes.access$1(LoaderTuplesNodes.java:280)
at
com.hp.hpl.jena.sdb.layout2.LoaderTuplesNodes$Commiter.run(LoaderTuplesNodes.java:318)
at java.lang.Thread.run(Thread.java:735)
com.hp.hpl.jena.sdb.SDBException: Exception flushing
at
com.hp.hpl.jena.sdb.layout2.TupleLoaderBase.flush(TupleLoaderBase.java:220)
at
com.hp.hpl.jena.sdb.layout2.TupleLoaderBase.finish(TupleLoaderBase.java:155)
at
com.hp.hpl.jena.sdb.layout2.LoaderTuplesNodes.commitTuples(LoaderTuplesNodes.java:283)
at
com.hp.hpl.jena.sdb.layout2.LoaderTuplesNodes.access$1(LoaderTuplesNodes.java:280)
at
com.hp.hpl.jena.sdb.layout2.LoaderTuplesNodes$Commiter.run(LoaderTuplesNodes.java:318)
at java.lang.Thread.run(Thread.java:735)
Caused by: java.sql.SQLException: Transaction (Process ID 72) was
deadlocked on lock resources with another process and has been chosen as
the deadlock victim. Rerun the transaction.
at
net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at
net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:723)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1166)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1119)
at
net.sourceforge.jtds.jdbcx.proxy.StatementProxy.executeUpdate(StatementProxy.java:69)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.pmiExecuteUpdate(WSJdbcStatement.java:1693)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeUpdate(WSJdbcStatement.java:1041)
at com.hp.hpl.jena.sdb.sql.SDBConnection.execUpdate(SDBConnection.java:153)
at
com.hp.hpl.jena.sdb.layout2.TupleLoaderBase.flush(TupleLoaderBase.java:202)
... 5 more
On Wed, May 16, 2012 at 10:41 PM, Damian Steer <d....@bristol.ac.uk>wrote:
>
> On 16 May 2012, at 14:12, Adrian Paleacu wrote:
>
> > Damian,
>
> Hi Adrian,
>
> > It worked okay. Sorry my hot deploy on websphere is not working all the
> > time.
> > I guess the connection.getMetaData() is not working all the time with
> jtds
> > driver.
>
> So could you explain how you fixed your issues? You had a deadlock, then
> an existing temp table issue as I recall. Any fixes you'd suggest we apply?
>
> Thanks,
>
> Damian
Re: SDB deadlock
Posted by Damian Steer <d....@bristol.ac.uk>.
On 16 May 2012, at 14:12, Adrian Paleacu wrote:
> Damian,
Hi Adrian,
> It worked okay. Sorry my hot deploy on websphere is not working all the
> time.
> I guess the connection.getMetaData() is not working all the time with jtds
> driver.
So could you explain how you fixed your issues? You had a deadlock, then an existing temp table issue as I recall. Any fixes you'd suggest we apply?
Thanks,
Damian
Re: SDB deadlock
Posted by Adrian Paleacu <ad...@gmail.com>.
Damian,
It worked okay. Sorry my hot deploy on websphere is not working all the
time.
I guess the connection.getMetaData() is not working all the time with jtds
driver.
Thank you for your quick replay,
regards,
On Wed, May 16, 2012 at 3:08 PM, Adrian Paleacu <ad...@gmail.com>wrote:
> Hi Damian,
>
> I changed the code from your idea with "select 1", but I still have
> errors ( There is already an object named '#NNodeQuads' in the database).
>
> :(
>
> I have to go up in the SDB functionality and trace the connection, since
> is the only one passed in the whole chain.
>
>
> regards,
>
> Adrian
>
>
> On Wed, May 16, 2012 at 11:53 AM, Damian Steer <d....@bristol.ac.uk>wrote:
>
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> On 16/05/12 06:42, Adrian Paleacu wrote:
>> > Damian,
>>
>> >
>> > [5/15/12 8:20:14:731 MDT] FFDC Exception:java.sql.SQLException
>> > SourceId:com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.execute
>> > ProbeId:296
>> > Reporter:com.ibm.ws.rsadapter.jdbc.WSJdbcStatement@38273827
>> > java.sql.SQLException: There is already an object named
>> > '#NNodeQuads' in the database.
>>
>>
>> > I tried to use Optimistic or Pessimistic ensureTempTablesX but it
>> > fails within these two lines of code.
>> >
>> > if (!TableUtils.hasTable(connection().getSqlConnection(),
>> > getNodeLoader())) connection().execSilent(getCreateTempNodes());
>> >
>> > Right now I'm investigating this problem but any help will be
>> > appreciated.
>>
>> hasTable uses JDBC metadata to work out whether a table exists. Given
>> the ... indifferent ... implementation of this is some drivers perhaps
>> it would be more robust to provide a fallback like:
>>
>> try { "SELECT 1 FROM <tablename>" } catch { return false }
>>
>> (will that query do the trick? something along those lines anyway)
>>
>> Trouble is that it can mask errors, since the exception is just a
>> generic sql exception, rather than 'table not there'. However it might
>> be better than nothing.
>>
>> How does that sound?
>>
>> Damian
>> -----BEGIN PGP SIGNATURE-----
>> Version: GnuPG v1.4.11 (GNU/Linux)
>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>>
>> iEYEARECAAYFAk+zawMACgkQAyLCB+mTtyl12ACg3u+cjJ8eoZWBRB/JDnJMCELk
>> LVMAoLg08jZeGq58Rhekpsa01BTFWpDu
>> =aWkW
>> -----END PGP SIGNATURE-----
>>
>
>
Re: SDB deadlock
Posted by Adrian Paleacu <ad...@gmail.com>.
Hi Damian,
I changed the code from your idea with "select 1", but I still have errors
( There is already an object named '#NNodeQuads' in the database).
:(
I have to go up in the SDB functionality and trace the connection, since is
the only one passed in the whole chain.
regards,
Adrian
On Wed, May 16, 2012 at 11:53 AM, Damian Steer <d....@bristol.ac.uk>wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 16/05/12 06:42, Adrian Paleacu wrote:
> > Damian,
>
> >
> > [5/15/12 8:20:14:731 MDT] FFDC Exception:java.sql.SQLException
> > SourceId:com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.execute
> > ProbeId:296
> > Reporter:com.ibm.ws.rsadapter.jdbc.WSJdbcStatement@38273827
> > java.sql.SQLException: There is already an object named
> > '#NNodeQuads' in the database.
>
>
> > I tried to use Optimistic or Pessimistic ensureTempTablesX but it
> > fails within these two lines of code.
> >
> > if (!TableUtils.hasTable(connection().getSqlConnection(),
> > getNodeLoader())) connection().execSilent(getCreateTempNodes());
> >
> > Right now I'm investigating this problem but any help will be
> > appreciated.
>
> hasTable uses JDBC metadata to work out whether a table exists. Given
> the ... indifferent ... implementation of this is some drivers perhaps
> it would be more robust to provide a fallback like:
>
> try { "SELECT 1 FROM <tablename>" } catch { return false }
>
> (will that query do the trick? something along those lines anyway)
>
> Trouble is that it can mask errors, since the exception is just a
> generic sql exception, rather than 'table not there'. However it might
> be better than nothing.
>
> How does that sound?
>
> Damian
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.11 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAk+zawMACgkQAyLCB+mTtyl12ACg3u+cjJ8eoZWBRB/JDnJMCELk
> LVMAoLg08jZeGq58Rhekpsa01BTFWpDu
> =aWkW
> -----END PGP SIGNATURE-----
>
Re: SDB deadlock
Posted by Damian Steer <d....@bristol.ac.uk>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 16/05/12 06:42, Adrian Paleacu wrote:
> Damian,
>
> [5/15/12 8:20:14:731 MDT] FFDC Exception:java.sql.SQLException
> SourceId:com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.execute
> ProbeId:296
> Reporter:com.ibm.ws.rsadapter.jdbc.WSJdbcStatement@38273827
> java.sql.SQLException: There is already an object named
> '#NNodeQuads' in the database.
> I tried to use Optimistic or Pessimistic ensureTempTablesX but it
> fails within these two lines of code.
>
> if (!TableUtils.hasTable(connection().getSqlConnection(),
> getNodeLoader())) connection().execSilent(getCreateTempNodes());
>
> Right now I'm investigating this problem but any help will be
> appreciated.
hasTable uses JDBC metadata to work out whether a table exists. Given
the ... indifferent ... implementation of this is some drivers perhaps
it would be more robust to provide a fallback like:
try { "SELECT 1 FROM <tablename>" } catch { return false }
(will that query do the trick? something along those lines anyway)
Trouble is that it can mask errors, since the exception is just a
generic sql exception, rather than 'table not there'. However it might
be better than nothing.
How does that sound?
Damian
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAk+zawMACgkQAyLCB+mTtyl12ACg3u+cjJ8eoZWBRB/JDnJMCELk
LVMAoLg08jZeGq58Rhekpsa01BTFWpDu
=aWkW
-----END PGP SIGNATURE-----
Re: SDB deadlock
Posted by Adrian Paleacu <ad...@gmail.com>.
Damian,
Sorry, please ignore the TupleLoaderBase remark. This is related to a
second problem that I have on MSSQL server with concurrent users (20).
[5/15/12 8:20:14:731 MDT] FFDC Exception:java.sql.SQLException
SourceId:com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.execute ProbeId:296
Reporter:com.ibm.ws.rsadapter.jdbc.WSJdbcStatement@38273827
java.sql.SQLException: There is already an object named '#NNodeQuads' in
the database.
at
net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at
net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:723)
at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1160)
at
net.sourceforge.jtds.jdbcx.proxy.StatementProxy.execute(StatementProxy.java:287)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.pmiExecute(WSJdbcStatement.java:1485)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.execute(WSJdbcStatement.java:632)
at com.hp.hpl.jena.sdb.sql.SDBConnection.execSilent(SDBConnection.java:203)
at
com.hp.hpl.jena.sdb.layout2.TupleLoaderBase.ensureTempTables2(TupleLoaderBase.java:364)
at
com.hp.hpl.jena.sdb.layout2.TupleLoaderBase.ensureTempTables(TupleLoaderBase.java:335)
at com.hp.hpl.jena.sdb.layout2.TupleLoaderBase.init(TupleLoaderBase.java:52)
at
com.hp.hpl.jena.sdb.layout2.TupleLoaderBase.<init>(TupleLoaderBase.java:45)
at
com.hp.hpl.jena.sdb.layout2.hash.TupleLoaderHashSQLServer.<init>(TupleLoaderHashSQLServer.java:17)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:45)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:39)
at java.lang.reflect.Constructor.newInstance(Constructor.java:515)
at
com.hp.hpl.jena.sdb.layout2.LoaderTuplesNodes.updateOneTuple(LoaderTuplesNodes.java:267)
at
com.hp.hpl.jena.sdb.layout2.LoaderTuplesNodes.access$2(LoaderTuplesNodes.java:256)
at
com.hp.hpl.jena.sdb.layout2.LoaderTuplesNodes$Commiter.run(LoaderTuplesNodes.java:334)
at java.lang.Thread.run(Thread.java:735)
I tried to use Optimistic or Pessimistic ensureTempTablesX but it fails
within these two lines of code.
if (!TableUtils.hasTable(connection().getSqlConnection(), getNodeLoader()))
connection().execSilent(getCreateTempNodes());
Right now I'm investigating this problem but any help will be appreciated.
Regards,
Adrian
On Tue, May 15, 2012 at 9:16 PM, Adrian Paleacu <ad...@gmail.com>wrote:
> Hi Damian,
>
> My SDB version is ~1.1 and the DB is MSSQL 2008, comparing
> TupleLoaderBase reveal no differences.
> I could try to port some of the changes from the current dev into my
> codebase. But I need some directions :)
>
> Thank you for your quick response,
>
> Adrian
>
>
> On Tue, May 15, 2012 at 4:20 PM, Damian Steer <d....@bristol.ac.uk>wrote:
>
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> On 15/05/12 06:35, Adrian Paleacu wrote:
>> > Hi everyone, I'm wonder if someone know about a possible deadlock
>> > in SDB with this scenario. I'm not sure if is my app fault of SDB.
>> > :)
>>
>> Which version of SDB are you using, and which database?
>>
>> > Process A has an exclusive lock on nodes and wants a share lock on
>> > quads Process B has an exclusive lock on quads and wants a share
>> > lock on nodes
>>
>> On some databases the loader now locks the node table on entry, but
>> I'm pretty sure that version hasn't been released yet. Could you try
>> the current SDB snapshot?
>>
>> (The jena issue is jena-118, but jira is unreachable for me at the moment)
>>
>> Thanks,
>>
>> Damian
>> -----BEGIN PGP SIGNATURE-----
>> Version: GnuPG v1.4.11 (GNU/Linux)
>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>>
>> iEYEARECAAYFAk+yWC4ACgkQAyLCB+mTtynm+wCg3kdkVRdNb8UwWZhptrrL23YR
>> HJgAoKPEXBQV1edAy87SmCpgwWRR2kbs
>> =RaMN
>> -----END PGP SIGNATURE-----
>>
>
>
Re: SDB deadlock
Posted by Adrian Paleacu <ad...@gmail.com>.
Hi Damian,
My SDB version is ~1.1 and the DB is MSSQL 2008, comparing TupleLoaderBase
reveal no differences.
I could try to port some of the changes from the current dev into my
codebase. But I need some directions :)
Thank you for your quick response,
Adrian
On Tue, May 15, 2012 at 4:20 PM, Damian Steer <d....@bristol.ac.uk> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 15/05/12 06:35, Adrian Paleacu wrote:
> > Hi everyone, I'm wonder if someone know about a possible deadlock
> > in SDB with this scenario. I'm not sure if is my app fault of SDB.
> > :)
>
> Which version of SDB are you using, and which database?
>
> > Process A has an exclusive lock on nodes and wants a share lock on
> > quads Process B has an exclusive lock on quads and wants a share
> > lock on nodes
>
> On some databases the loader now locks the node table on entry, but
> I'm pretty sure that version hasn't been released yet. Could you try
> the current SDB snapshot?
>
> (The jena issue is jena-118, but jira is unreachable for me at the moment)
>
> Thanks,
>
> Damian
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.11 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAk+yWC4ACgkQAyLCB+mTtynm+wCg3kdkVRdNb8UwWZhptrrL23YR
> HJgAoKPEXBQV1edAy87SmCpgwWRR2kbs
> =RaMN
> -----END PGP SIGNATURE-----
>
Re: SDB deadlock
Posted by Damian Steer <d....@bristol.ac.uk>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 15/05/12 06:35, Adrian Paleacu wrote:
> Hi everyone, I'm wonder if someone know about a possible deadlock
> in SDB with this scenario. I'm not sure if is my app fault of SDB.
> :)
Which version of SDB are you using, and which database?
> Process A has an exclusive lock on nodes and wants a share lock on
> quads Process B has an exclusive lock on quads and wants a share
> lock on nodes
On some databases the loader now locks the node table on entry, but
I'm pretty sure that version hasn't been released yet. Could you try
the current SDB snapshot?
(The jena issue is jena-118, but jira is unreachable for me at the moment)
Thanks,
Damian
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAk+yWC4ACgkQAyLCB+mTtynm+wCg3kdkVRdNb8UwWZhptrrL23YR
HJgAoKPEXBQV1edAy87SmCpgwWRR2kbs
=RaMN
-----END PGP SIGNATURE-----