[DERBY-3683] SYSCS_COMPRESS_TABLE gets deadlock while executing concurrently with other user threads

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 10.4.1.3
• Component/s: SQL, Tools
• Labels: derby_triage10_5_2
• Environment: Embedded Derby, Sun JDK 1.6.0_06-b02, Linux 2.6.9-67.0.15.EL i686
• Urgency: Normal
• Issue & fix info: Workaround attached
• Assignee: Unassigned
• Reporter: Juha Heljoranta
• Created: 21/May/08 10:10
• Updated: 01/Mar/21 11:20

Description

Derby documentation about SYSCS_COMPRESS_TABLE says: "...procedure acquires an exclusive table lock on the table being compressed." However, I get:

java.sql.SQLException: The exception 'java.sql.SQLException: A lock could not be obtained due to a deadlock, cycle of locks and waiters is:
Lock : ROW, SYSCONGLOMERATES, (7,16)
Waiting XID :

{13091, X} , APP, alter table "APP"."XYZ" compress sequential
Granted XID : {13091, S} , {13087, S}
Lock : TABLE, XYZ, Tablelock
Waiting XID : {13087, IX} , APP, update XYZ set FOO = ? where BAR = ? AND ID = ?
Granted XID : {13091, X}

. The selected victim is XID : 13091.' was thrown while evaluating an expression.

if another thread is updating the same table while another thread executes:

CallableStatement cs = conn.prepareCall("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)");
cs.setString(1, "APP");
cs.setString(2, "XYZ");
cs.setShort(3, (short) 1);
cs.execute();
conn.commit();

Problem goes away if I acquire exclusive table lock manually right before the compress table statement:

conn.prepareStatement("LOCK TABLE XYZ IN EXCLUSIVE MODE").executeUpdate();

Issue Links

• is duplicated by: DERBY-6907 ERROR 40XL1: A lock could not be obtained within the time requested
• is related to: 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."
• is related to: DERBY-4275 Query executions fail when compressing a table using SYSCS_UTIL.SYSCS_COMPRESS_TABLE
• is related to: DERBY-5876 Overhaul table compression code