Brett: thank you for your prompt response.

 

So, how can I solve this situation? I have one thread which runs a select which may execute for a minute or longer, and other threads which insert and update records (more precisely, they batch inserts/updates/deletes and then execute those batches). How can I prevent these errors from happening and how can I assure the proper functioning of the application?

 

Thanks,

Pavel.

 

From: Bergquist, Brett [mailto:BBergquist@canoga.com]
Sent: Monday, February 13, 2012 9:01 AM
To: Derby Discussion
Subject: RE: "The selected victim is XID"

 

You either had a deadlock as you discovered.  You are going to either have to order your transactions to avoid a deadlock or you can catch the exception and retry the transaction.  Nothing in Derby is going to automatically retry the insert.  Note that the “select” transaction may have be the one that is terminated in the future, it depends on what Derby decides is best to allow one of the transactions to complete.

 

I do not believe this is anything different than for any other database, at least not the ones that I have used.

 

Brett

 

From: Pavel Bortnovskiy [mailto:pbortnovskiy@jefferies.com]
Sent: Monday, February 13, 2012 8:45 AM
To: Derby Discussion (derby-user@db.apache.org)
Subject: "The selected victim is XID"

 

Hello:

 

Today my application threw and exception I haven’t seen before:

 

--REFDATA.INSTYPE in ('M','P','T','G','A') AND

                                BOOK.ID NOT LIKE 'MD%' AND

                                BOOK.ID NOT LIKE 'LC%' AND

                                BOOK.ID NOT LIKE 'P_%'

  Granted XID : {9778, X}

Lock : ROW, WINFITS_DB, (6,10742)

  Waiting XID : {9778, X} , APP, INSERT INTO A_DB (sec_id,val) VALUES (?,?)

  Granted XID : {9749, S}

. The selected victim is XID : 9749.

        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)

        at org.apache.derby.impl.services.locks.Deadlock.buildException(Unknown Source)

        at org.apache.derby.impl.services.locks.ConcurrentLockSet.lockObject(Unknown Source)

        at org.apache.derby.impl.services.locks.AbstractPool.lockObject(Unknown Source)

        at org.apache.derby.impl.services.locks.ConcurrentPool.lockObject(Unknown Source)

        at org.apache.derby.impl.store.raw.xact.RowLocking2.lockRecordForRead(Unknown Source)

        at org.apache.derby.impl.store.access.heap.HeapController.lockRow(Unknown Source)

        at org.apache.derby.impl.store.access.heap.HeapController.lockRow(Unknown Source)

        at org.apache.derby.impl.store.access.btree.index.B2IRowLocking3.lockRowOnPage(Unknown Source)

        at org.apache.derby.impl.store.access.btree.index.B2IRowLocking3._lockScanRow(Unknown Source)

        at org.apache.derby.impl.store.access.btree.index.B2IRowLockingRR.lockScanRow(Unknown Source)

        at org.apache.derby.impl.store.access.btree.BTreeForwardScan.fetchRows(Unknown Source)

        at org.apache.derby.impl.store.access.btree.BTreeScan.fetchNext(Unknown Source)

        at org.apache.derby.impl.sql.execute.TableScanResultSet.getNextRowCore(Unknown Source)

        at org.apache.derby.impl.sql.execute.IndexRowToBaseRowResultSet.getNextRowCore(Unknown Source)

        at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown Source)

        at org.apache.derby.impl.sql.execute.OnceResultSet.getNextRowCore(Unknown Source)

        at org.apache.derby.exe.acc934c123x0135x7687xc204x00001d890d611.g0(Unknown Source)

        at org.apache.derby.exe.acc934c123x0135x7687xc204x00001d890d611.e21(Unknown Source)

        at sun.reflect.GeneratedMethodAccessor32.invoke(Unknown Source)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

        at java.lang.reflect.Method.invoke(Method.java:597)

        at org.apache.derby.impl.services.reflect.ReflectMethod.invoke(Unknown Source)

        at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.doProjection(Unknown Source)

        at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown Source)

        at org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowFromSource(Unknown Source)

        at org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowCore(Unknown Source)

        at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown Source)

        ... 9 more

 

I did find the article on Debugging Locking Situations: http://wiki.apache.org/db-derby/LockDebugging

And I did understand the log file that an insert was being made into the same table on which select was being executed,

But my question is how to prevent these errors from happening? Also, if such an exception was thrown during an insert,

will the insert be attempted again?

 

Thanks,

Pavel.

Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.
Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.