db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Saurabh Singhi (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5234) Unable to insert data into table. Failed due be "ERROR XSDG0: Page Page(51919,Container(0, 1104)) could not be read from disk."
Date Tue, 22 May 2012 21:26:42 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5234?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13281224#comment-13281224
] 

Saurabh Singhi commented on DERBY-5234:
---------------------------------------

Hi Rick,
My program created a new 10.8.2.3 database along with the regular select, delete, lock, compress
and insert statements.
As per your suggestions, the program executed a LOCK statement (EXCLUSIVE lock)  before executing
the SYSCS_COMPRESS_TABLE call. 
I'm still getting the deadlock exception (while selecting and inserting) shown below:- 

***********************************************************************************************************************************************************
22 May 2012 13:37:32 ProcessDAO.getLastRecordFromIntervalTableShowGap = A lock could not be
obtained due to a deadlock, cycle of locks and waiters is:
Lock : TABLE, PROCESS_055234_HR, Tablelock
  Waiting XID : {104215, IS} , APP, SELECT  CpuNum,  SampleTimestamp AS TimeStamp,  ProcBusyPercent
AS BusyPercent FROM PROCESS_055234_HR WHERE SampleTimestamp >= ?  AND Volume=?  AND Subvol=?
 AND Filename=?  AND ProcessName=?  AND CpuNum=?  AND PIN=?  ORDER BY SampleTimestamp 
  Granted XID : {102377, X} 
Lock : ROW, SYSCONGLOMERATES, (7,9)
  Waiting XID : {102377, X} , APP, alter table "APP"."PROCESS_055234_HR" compress sequential
  Granted XID : {102377, S} , {104215, S} 
. The selected victim is XID : 104215.
java.sql.SQLTransactionRollbackException: A lock could not be obtained due to a deadlock,
cycle of locks and waiters is:
Lock : TABLE, PROCESS_055234_HR, Tablelock
  Waiting XID : {104215, IS} , APP, SELECT  CpuNum,  SampleTimestamp AS TimeStamp,  ProcBusyPercent
AS BusyPercent FROM PROCESS_055234_HR WHERE SampleTimestamp >= ?  AND Volume=?  AND Subvol=?
 AND Filename=?  AND ProcessName=?  AND CpuNum=?  AND PIN=?  ORDER BY SampleTimestamp 
  Granted XID : {102377, X} 
Lock : ROW, SYSCONGLOMERATES, (7,9)
  Waiting XID : {102377, X} , APP, alter table "APP"."PROCESS_055234_HR" compress sequential
  Granted XID : {102377, S} , {104215, S} 
. The selected victim is XID : 104215.
	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:89)
	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:256)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:424)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353)
	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2290)
	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:82)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(EmbedPreparedStatement.java:151)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(EmbedPreparedStatement20.java:82)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(EmbedPreparedStatement30.java:63)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(EmbedPreparedStatement40.java:40)
	at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Driver40.java:107)
	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1615)
	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1443)
	at com.idelji.operations.iasset.plugin.imon.dao.ProcessDAO.getLastRecordFromIntervalTableShowGap(ProcessDAO.java:503)
	at imon.utils.DataSourceRouter.getLatestProcessData(DataSourceRouter.java:608)
	at imon.db.ProcessDataProvider.getLatestData(ProcessDataProvider.java:131)
	at imon.charts.LineChartWithSlider.refreshChartData(LineChartWithSlider.java:558)
	at imon.charts.LineChartWithSlider$1.handle(LineChartWithSlider.java:327)
	at imon.charts.LineChartWithSlider$1.handle(LineChartWithSlider.java:322)
	at com.sun.scenario.animation.shared.TimelineClipCore.visitKeyFrame(TimelineClipCore.java:196)
	at com.sun.scenario.animation.shared.TimelineClipCore.playTo(TimelineClipCore.java:137)
	at javafx.animation.Timeline.impl_playTo(Timeline.java:163)
	at com.sun.scenario.animation.shared.InfiniteClipEnvelope.timePulse(InfiniteClipEnvelope.java:89)
	at javafx.animation.Animation.impl_timePulse(Animation.java:952)
	at com.sun.scenario.animation.shared.AnimationPulseReceiver.timePulse(AnimationPulseReceiver.java:75)
	at com.sun.scenario.animation.AbstractMasterTimer.timePulseImpl(AbstractMasterTimer.java:357)
	at com.sun.scenario.animation.AbstractMasterTimer$MainLoop.run(AbstractMasterTimer.java:280)
	at com.sun.javafx.tk.quantum.QuantumToolkit.pulse(QuantumToolkit.java:381)
	at com.sun.javafx.tk.quantum.QuantumToolkit$8.run(QuantumToolkit.java:311)
	at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
	at com.sun.glass.ui.win.WinApplication.access$100(WinApplication.java:29)
	at com.sun.glass.ui.win.WinApplication$2$1.run(WinApplication.java:62)
	at java.lang.Thread.run(Unknown Source)

22 May 2012 13:37:52 ProcessDAO.insertData = A lock could not be obtained due to a deadlock,
cycle of locks and waiters is:
Lock : ROW, SYSCONGLOMERATES, (7,9)
  Waiting XID : {104576, S} , APP, INSERT INTO PROCESS_055234_HR (SampleTimestamp,CpuNum,Pin,Priority,UserID,GroupID,ProcessName,Volume,SubVol,FileName,AncestorProcessName,ProcBusyPercent)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
  Granted XID : {102377, S} 
Lock : ROW, SYSCONGLOMERATES, (7,8)
  Waiting XID : {102377, X} , APP, alter table "APP"."PROCESS_055234_HR" compress sequential
  Granted XID : {102377, S} , {104576, S} 
. The selected victim is XID : 104576.
java.sql.SQLTransactionRollbackException: A lock could not be obtained due to a deadlock,
cycle of locks and waiters is:
Lock : ROW, SYSCONGLOMERATES, (7,9)
  Waiting XID : {104576, S} , APP, INSERT INTO PROCESS_055234_HR (SampleTimestamp,CpuNum,Pin,Priority,UserID,GroupID,ProcessName,Volume,SubVol,FileName,AncestorProcessName,ProcBusyPercent)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
  Granted XID : {102377, S} 
Lock : ROW, SYSCONGLOMERATES, (7,8)
  Waiting XID : {102377, X} , APP, alter table "APP"."PROCESS_055234_HR" compress sequential
  Granted XID : {102377, S} , {104576, S} 
. The selected victim is XID : 104576.
	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:89)
	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:256)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:424)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353)
	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2290)
	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:82)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(EmbedPreparedStatement.java:151)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(EmbedPreparedStatement20.java:82)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(EmbedPreparedStatement30.java:63)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(EmbedPreparedStatement40.java:40)
	at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Driver40.java:107)
	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1615)
	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1443)
	at com.idelji.operations.iasset.plugin.imon.dao.ProcessDAO.insertData(ProcessDAO.java:86)
	at com.idelji.operations.iasset.plugin.imon.comm.CPUProcessDataStoreWriter.onData(CPUProcessDataStoreWriter.java:56)
	at com.idelji.operations.iasset.plugin.imon.handler.CPUProcessDataHandler.gotData(CPUProcessDataHandler.java:205)
	at com.idelji.operations.ibase.core.comm.Connector.makeRequest(Connector.java:83)
	at com.idelji.operations.ibase.core.comm.EntityConnector.makeRequest(EntityConnector.java:36)
	at com.idelji.operations.iasset.plugin.imon.comm.CPUProcessCommunicator.run(CPUProcessCommunicator.java:98)

***********************************************************************************************************************************************************
                
> Unable to insert data into table. Failed due be "ERROR XSDG0: Page Page(51919,Container(0,
1104)) could not be read from disk."
> -------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-5234
>                 URL: https://issues.apache.org/jira/browse/DERBY-5234
>             Project: Derby
>          Issue Type: Bug
>          Components: Store
>    Affects Versions: 10.5.3.0
>         Environment: HP-UX 11iv2 in production environment with JDK1.6; Solaris 5/10
in test environment with JDK 1.6
>            Reporter: Varma R
>            Assignee: Rick Hillegas
>            Priority: Critical
>              Labels: ERROR, XSDG0, apache, corruption, data, derby, derby_triage10_9
>             Fix For: 10.8.2.3, 10.9.0.0
>
>         Attachments: 5234_alloc.out, 5234_page_10219.out, 5234_summary.out, DataFileReader_Output.zip,
DbCompressErrorTester.java, Derby5234.java, Derby5234.java, derby-5234-01-aa-emptyAllocPage.diff,
derby-5234-01-ab-emptyAllocPage.diff, derby-5234-02-aa-edgeCaseTests.diff, log191.dat, log85.dat
>
>
> One of the derby database table "gets corrupted"/"indicates connection not available"
during processing inserts from java client application as shown in the trace and the only
way to recover from this error is to rebuild the DB - by deleting the data and creating the
tables again. This happens once in a while (thrice in a span of two months) and the java application
(run in multiple servers), which updates the database, processes around 100 million transactions
per hour (in total and each transation results in 4-5 updates to the DB) 
> There are eight tables in the derby database.
>    TABLE NAME                           ROWS COUNT (at time of corruption)
> ---------------------------------------------------------------------------------
>    KPI.KPI_MERGEIN;                     362917
>    KPI.KPI_IN;                                 422508
>    KPI.KPI_DROPPED;                    53667
>    KPI.KPI_ERROR1;                       0
>    KPI.KPI_ERROR2;                       2686
>    KPI.KPI_ERRORMERGE;            0
>    KPI.KPI_MERGEOUT;                 362669
>    KPI.KPI_OUT;                             125873
> The derby database has been started with the following parameters 
> CMD="java -Dderby.system.home=$DERBY_OPTS -Dderby.locks.monitor=true -Dderby.locks.deadlockTrace=true
-Dderby.locks.escalationThreshold=50000 -Dderby.locks.waitTimeout=
> -1 -Dderby.storage.pageCacheSize=100000 -Xms512M -Xmx3072M -XX:NewSize=256M -classpath
$DERBY_CLASSPATH org.apache.derby.drda.NetworkServerControl start -h $KPIDERBYHOST -p $DERBY_KPI_PORT"
> The corrupted database tar (filesystem) in live environment was moved to a test system
(Solaris system) and few checks were run on the corrupted DB as part of analysis (DB does
start fine)
> While trying to insert a row in any table expect KPI.KPI_MERGEIN, it is successful. But
when a new row is inserted into KPI.KPI_MERGEIN table using command line tool it's throwing
below error message (the same message that appeared in live 
> ij> INSERT INTO KPI.KPI_MERGEIN (A0_TXN_ID, A1_NE_ID, A2_CHU_IP_ADDR, A3_BATCH_DATE,A5_CODE)
VALUES (-1, 'BMTDE', '192.2.1.3', 231456879, 'KSD');
> ERROR 08006: A network protocol error was encountered and the connection has been terminated:
the requested command encountered an unarchitected and implementation-specific condition for
which there was no architected message
> and in derby.log file it shows below error stacktrace.
> ERROR XSDG0: Page Page(51919,Container(0, 1104)) could not be read from disk.
>         at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
>         at org.apache.derby.impl.store.raw.data.CachedPage.readPage(Unknown Source)
>         at org.apache.derby.impl.store.raw.data.CachedPage.setIdentity(Unknown Source)
>         at org.apache.derby.impl.services.cache.ConcurrentCache.find(Unknown Source)
>         at org.apache.derby.impl.store.raw.data.FileContainer.initPage(Unknown Source)
>         at org.apache.derby.impl.store.raw.data.FileContainer.newPage(Unknown Source)
>         at org.apache.derby.impl.store.raw.data.BaseContainer.addPage(Unknown Source)
>         at org.apache.derby.impl.store.raw.data.BaseContainerHandle.addPage(Unknown Source)
>         at org.apache.derby.impl.store.access.heap.HeapController.doInsert(Unknown Source)
>         at org.apache.derby.impl.store.access.heap.HeapController.insertAndFetchLocation(Unknown
Source)
>         at org.apache.derby.impl.sql.execute.RowChangerImpl.insertRow(Unknown Source)
>         at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(Unknown
Source)
>         at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source)
>         at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
>         at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(Unknown Source)
>         at org.apache.derby.impl.drda.DRDAConnThread.parseEXCSQLIMM(Unknown Source)
>         at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown Source)
>         at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
> Caused by: java.io.EOFException: Reached end of file while attempting to read a whole
page.
>         at org.apache.derby.impl.store.raw.data.RAFContainer4.readFull(Unknown Source)
>         at org.apache.derby.impl.store.raw.data.RAFContainer4.readPage0(Unknown Source)
>         at org.apache.derby.impl.store.raw.data.RAFContainer4.readPage(Unknown Source)
>         ... 20 more
> ============= begin nested exception, level (1) ===========
> java.io.EOFException: Reached end of file while attempting to read a whole page.
>         at org.apache.derby.impl.store.raw.data.RAFContainer4.readFull(Unknown Source)
>         at org.apache.derby.impl.store.raw.data.RAFContainer4.readPage0(Unknown Source)
>         at org.apache.derby.impl.store.raw.data.RAFContainer4.readPage(Unknown Source)
>         at org.apache.derby.impl.store.raw.data.CachedPage.readPage(Unknown Source)
>         at org.apache.derby.impl.store.raw.data.CachedPage.setIdentity(Unknown Source)
>         at org.apache.derby.impl.services.cache.ConcurrentCache.find(Unknown Source)
>         at org.apache.derby.impl.store.raw.data.FileContainer.initPage(Unknown Source)
>         at org.apache.derby.impl.store.raw.data.FileContainer.newPage(Unknown Source)
>         at org.apache.derby.impl.store.raw.data.BaseContainer.addPage(Unknown Source)
>         at org.apache.derby.impl.store.raw.data.BaseContainerHandle.addPage(Unknown Source)
>         at org.apache.derby.impl.store.access.heap.HeapController.doInsert(Unknown Source)
>         at org.apache.derby.impl.store.access.heap.HeapController.insertAndFetchLocation(Unknown
Source)
>         at org.apache.derby.impl.sql.execute.RowChangerImpl.insertRow(Unknown Source)
>         at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(Unknown
Source)
>         at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source)
>         at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
>         at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(Unknown Source)
>         at org.apache.derby.impl.drda.DRDAConnThread.parseEXCSQLIMM(Unknown Source)
>         at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown Source)
>         at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
> ============= end nested exception, level (1) ===========
> 2011-05-16 10:37:21.392 GMT:
> Shutting down instance a816c00e-012f-f85f-7892-ffff874c3ff6
> ----------------------------------------------------------------
> Cleanup action completed
> The problem is only with INSERT statement. When i try SELECT statement on KPI.KPI_MERGEIN
table it is working well.The database file system size (in seg0) is 1.3 GB
> Can anyone help me out in identifying the problem that why for one table alone its throwing
the above error message ? Would upgrade to a new version help ? 

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message