[
https://issues.apache.org/jira/browse/DERBY-4274?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen closed DERBY-4274.
-------------------------------------
Resolution: Fixed
Fix Version/s: 10.5.1.2
Merged to 10.5 and committed revision 785828.
> SYSCS_UPDATE_STATISTICS takes unnecessary table lock
> ----------------------------------------------------
>
> Key: DERBY-4274
> URL: https://issues.apache.org/jira/browse/DERBY-4274
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.1
> Reporter: Knut Anders Hatlen
> Assignee: Knut Anders Hatlen
> Fix For: 10.5.1.2, 10.6.0.0
>
> Attachments: d4274-1a.diff
>
>
> I'm using SYSCS_UTIL.SYSCS_UPDATE_STATISTICS in an application and see
> deadlocks similar to this one from time to time:
> Caused by: java.sql.SQLTransactionRollbackException: A lock could not be
> obtained due to a deadlock, cycle of locks and waiters is:
> Lock : ROW, SYSSTATISTICS, (2,20)
> Waiting XID : {20137, S} , APP, SELECT CS.REVISION, A.NAME, CS.TIME,
> CS.MESSAGE, F2.PATH FROM CHANGESETS CS, FILECHANGES FC, REPOSITORIES R, FILES
> F, AUTHORS A, FILECHANGES FC2, FILES F2 WHERE R.PATH = ? AND F.PATH LIKE ?
> ESCAPE '#' AND F.REPOSITORY = R.ID AND A.REPOSITORY = R.ID AND CS.ID =
> FC.CHANGESET AND R.ID = CS.REPOSITORY AND FC.FILE = F.ID AND A.ID = CS.AUTHOR
> AND CS.ID = FC2.CHANGESET AND FC2.FILE = F2.ID ORDER BY CS.ID DESC
> Granted XID : {20134, X}
> Lock : TABLE, CHANGESETS, Tablelock
> Waiting XID : {20134, X} , APP, alter table "APP"."CHANGESETS" all update
> statistics
> Granted XID : {20137, IS}
> . The selected victim is XID : 20137.
> Here, a select statement is being re-prepared because update statistics has
> invalidated it, but it has to wait for update statistics to finish in order
> to read the new statistics from SYSSTATISTICS. Then update statistics
> attempts to obtain an exclusive lock on the table whose indexes are being
> updated, but it has to wait because the select statement is holding an
> intention lock on the table. Both transactions wait for each other, so we
> have a deadlock.
> Since SYSCS_UPDATE_STATISTICS does not update the table, only its entries in
> SYSSTATISTICS, there is no need to take an exclusive table lock. If it didn't
> lock the table exclusively, this deadlock situation would go away.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
