db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4274) SYSCS_UPDATE_STATISTICS takes unnecessary table lock
Date Tue, 16 Jun 2009 09:20:07 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4274?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Knut Anders Hatlen updated DERBY-4274:
--------------------------------------

    Attachment: d4274-1a.diff

The exclusive lock is obtained in AlterTableConstantAction.executeConstantAction():

		// now do the real work

		// get an exclusive lock of the heap, to avoid deadlock on rows of
		// SYSCOLUMNS etc datadictionary tables and phantom table
		// descriptor, in which case table shape could be changed by a
		// concurrent thread doing add/drop column.
.
.
.
		lockTableForDDL(tc, tableConglomerateId, true);

When this code is called, the index cardinality statistics have already been updated, just
not committed, so there's no need for the lock. It looks to me as if the update statistics
work is done, and it goes on to doing "normal" alter table work, which is nothing in the case
of update statistics. Looking at the code for in-place compress, it returns from executeConstantAction()
before it gets to this point, and I believe that update statistics should do the same.

The attached patch changes AlterTableConstantAction.executeConstantAction() so that it returns
immediately when it has finished updating the statistics. It also adds a test case to UpdateStatisticsTest
where SYSCS_UPDATE_STATISTICS is called on a table locked by another transaction in shared
mode. It fails with a timeout without the fix and passes with the fix.

Running the full regression test suite now.

> 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
>         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.


Mime
View raw message