jdbc+mysql: "Deadlock found when trying to get lock; try restarting transaction"

Question

After recovering from a disk-full problem, I started getting these errors in my logs:

java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1684)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1108)
    at com.dappit.Dapper.adminer.pixelMaster.storage.RemotePixelStorage.flushBufferToDb(RemotePixelStorage.java:120)
    ...

The Java code (RemotePixelStorage.java:120):

(119) connection.setAutoCommit(false);
(120) int[] returnCodes = pstmt.executeBatch();
(121) connection.setAutoCommit(true);
(122) connection.commit();

Each statement is a call to the stored procedure add_pixel, which adds an element to a table and removes older elements if necessary.

What does this jdbc exception mean? How can I overcome this issue? Should I simply try to commit a few times with a catch block?

I don't think this is the cause of the problem, but why does setAutoCommit(true) come before commit()? — András Váczi, Dec 19 '12 at 12:19
@dezso - it could have been placed after the commit. If the transaction only contain a single operation there's no reason to turn auto-commit off anyway. This question is using executeBatch so it might contain several statements. — Trygve Laugstøl, Dec 19 '12 at 15:19
DISK QUESTION : What disk got full, datadir or /tmp? Are datadir and /tmp on the same mount point? DATA QUESTION : Is the table pixels InnoDB ??? — RolandoMySQLDBA, Dec 19 '12 at 15:59

score 1 · Accepted Answer · edited Jun 15 '20 at 09:05

Looking at the Stored Procedure, I see something rather unnatural.

DELIMITER $$
DROP PROCEDURE IF EXISTS adam_matan.AddPixel $$
CREATE PROCEDURE adam_matan.AddPixel
(
    GivenType VARCHAR(20),
    GivenPixelData BLOB
)
TheStoredProcedure:BEGIN
DECLARE KeepPixels,DeleteLimit,MaxID INT;
SET KeepPixels = 5;
  SET DeleteLimit = 100;
INSERT INTO pixels (type,pixel_data)
  VALUES (GivenType,GivenPixelData);
  SELECT MAX(ID) INTO MaxID FROM pixels;
  IF MOD(MaxID,DeleteLimit) > 0 THEN
      LEAVE TheStoredProcedure;
  END IF;
DROP TABLE IF EXISTS pixel_window;
  CREATE TEMPORARY TABLE pixel_window
  (id INT NOT NULL PRIMARY KEY) ENGINE=MyISAM;
SET @sqlstmt= CONCAT('INSERT INTO pixel_window ',
      'SELECT id FROM pixels WHERE type=''',GivenType,
      ''' ORDER BY id DESC LIMIT ',KeepPixels);
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;
SELECT * FROM pixels WHERE type=GivenType ORDER BY id; SELECT SLEEP(10);
  DELETE A.* FROM pixels A LEFT JOIN pixel_window B USING (id)
  WHERE A.type=GivenType AND B.id IS NULL;
  SELECT * FROM pixels WHERE type=GivenType ORDER BY id;
  DROP TABLE IF EXISTS pixel_window;
END $$
DELIMITER ;

The thing I see that is unnatural is the DELETE ... JOIN query that is mixing an InnoDB table and a MyISAM table. Such JOINs tend to be rather clumsy to mysqld.

Let's make the pixel_window table. InnoDB. In fact, let's not make it a temporary table. Of course, the hard part is to create a unique pixel_window table for the session. The pixel_window temp table will be the prefix 'pixel_window_' with the Connection ID appended.

DELIMITER $$
DROP PROCEDURE IF EXISTS adam_matan.AddPixel $$
CREATE PROCEDURE adam_matan.AddPixel
(
    GivenType VARCHAR(20),
    GivenPixelData BLOB
)
TheStoredProcedure:BEGIN
DECLARE KeepPixels,DeleteLimit,MaxID INT;
SET KeepPixels = 5;
  SET DeleteLimit = 100;
INSERT INTO pixels (type,pixel_data)
  VALUES (GivenType,GivenPixelData);
  SELECT MAX(ID) INTO MaxID FROM pixels;
  IF MOD(MaxID,DeleteLimit) > 0 THEN
      LEAVE TheStoredProcedure;
  END IF;
SET @pwtable = CONCAT('pixel_window_',CONNECTION_ID());

Create the pixel_window Table

SET @sqlstmt = CONCAT('CREATE TABLE ',pwtable,' (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB');
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

Load the pixel_window Table

SET @sqlstmt= CONCAT('INSERT INTO ',@pwtable,' ',
      'SELECT id FROM pixels WHERE type=''',GivenType,
      ''' ORDER BY id DESC LIMIT ',KeepPixels);
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;
SELECT * FROM pixels WHERE type=GivenType ORDER BY id; SELECT SLEEP(10);

Use the pixel_window Table to perform DELETE...JOIN

SET @sqlstmt = CONCAT('DELETE A.* FROM pixels A LEFT JOIN ',
  @pwtable,' B USING (id) WHERE A.type=GivenType AND B.id IS NULL');
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;
SELECT * FROM pixels WHERE type=GivenType ORDER BY id;

Drop the pixel_window Table

SET @sqlstmt = CONCAT('DROP TABLE IF EXISTS ',@pwtable);
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;
END $$
DELIMITER ;

Give it a Try !!!

CAVEAT : Since I created this monster, I needed to slay it as well.

score 0 · Answer 2 · answered Dec 19 '12 at 14:07

You say you just recovered from a 'disk full' condition.
You also never said what other processes/programs access this data.
We need an enumeration of other accessing programs because they could be the source of the deadlock.

Remember deadlock means competing access especially for writing.
Now ... consider your ACID qualities: Atomicity, Consistency, Durability & Isolation ....

jdbc+mysql: "Deadlock found when trying to get lock; try restarting transaction"

2 Answers2

Create the pixel_window Table

Load the pixel_window Table

Use the pixel_window Table to perform DELETE...JOIN

Drop the pixel_window Table

CAVEAT : Since I created this monster, I needed to slay it as well.