"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.acc934c123x0135x7687xc204x1d890d611.g0(Unknown Source) at org.apache.derby.exe.acc934c123x0135x7687xc204x1d890d611.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.
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:pbortnovs...@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.acc934c123x0135x7687xc204x1d890d611.g0(Unknown Source) at org.apache.derby.exe.acc934c123x0135x7687xc204x1d890d611.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;
Re: Why does the query optimiser not use the tables index?
On 2/12/12 9:10 AM, anthonyri wrote: I have a table with and index and yet the optimiser chooses a full table scan instead of using it - why does this happen? Can I force it to use the index? create table test(id smallint, seq smallint, type smallint); select id, seq, type from test where type< 1000; Hi Anthony, It is hard to say without knowing what the index looks like and without seeing the query plan for this SELECT statement. Two frequent causes of this problem are: 1) The optimizer statistics on the table are stale. You can refresh the statistics on a table by calling the SYSCS_UTIL.SYSCS_UPDATE_STATISTICS procedure. See http://db.apache.org/derby/docs/10.8/ref/ref-single.html#rrefupdatestatsproc 2) The index does not carry all of the columns needed to evaluate the query, requiring a probe of the base table in order to pick up the other columns. Based on the table statistics, the optimizer may decide it's cheaper to just scan the whole base table end to end. It sounds like you have seen the query plan, so you are probably familiar with the optimizer material in the Derby Tuning Guide: http://db.apache.org/derby/docs/10.8/tuning/ Hope this helps, -Rick
Re: Encrypted db
jesusaplsoft writes: > Is there any way to unencrypt a working encrypted db? As far as I know, the only way to do this currently is to create a new, unencrypted database and manually migrate the data from the encrypted database. Maybe you can use DdlUtils (http://db.apache.org/ddlutils/) to do the migration. There's an example of how to use DdlUtils to export the contents of one Derby database and import them into another Derby database at the Derby web site: http://db.apache.org/derby/integrate/db_ddlutils.html -- Knut Anders
Derby Embedded losing connection ?
I am happy using Derby Embedded DB. Currently we are on 10.5.3.0 no issues, but ... from time to time (currently not reproduceable) we get reports (and also felt it our self) that our app simply is losing its connection to the DB. We are using standard JDBC to get a connection and hold this guy as a hard ref. We connect to a project, sometimes we disconnect and connect to a different project. All this works in 99% of the time. Is there any known issue/situations anyone know of (could not find any on JIRA) ? Any hints how to start tracking down this ? (nothing in the log file of a projectdb). Any soft or weak references build into Derby DB embedded which could cause this ? Anyhow - thanx for all the good work out there - its a wonderful product. Bernd
Re: Derby Embedded losing connection ?
get reports (and also felt it our self) that our app simply is losing its connection to the DB. What are the symptoms, exactly? That is, what is it that makes you think you are losing your connection? One thing that occurs to me is a bit of a long-shot: are you using a connection pooling framework of some sort? If so, is it possible that the connection pooling framework is making a mistake and closing a connection inappropriately? thanks, bryan
RE: Derby Embedded losing connection ?
Which derby version? Can you provide some stacktrace? .. Regards / Pozdrowienia Jan Bartosz My contact info in Phonebook Click to call me by using Softphone -Original Message- From: ext Bryan Pendleton [mailto:bpendleton.de...@gmail.com] Sent: Tuesday, February 14, 2012 3:19 AM To: Derby Discussion Subject: Re: Derby Embedded losing connection ? > get reports (and also felt it our self) that our app simply is losing > its connection to the DB. What are the symptoms, exactly? That is, what is it that makes you think you are losing your connection? One thing that occurs to me is a bit of a long-shot: are you using a connection pooling framework of some sort? If so, is it possible that the connection pooling framework is making a mistake and closing a connection inappropriately? thanks, bryan