主页 / dba / 问题 / 29243
Accepted
Adam Matan
Asked: 2012-11-26 01:46:18 +0800 CST

jdbc+mysql：“尝试获取锁时发现死锁；尝试重新启动事务”

  • 772

从磁盘已满问题中恢复后，我的日志中开始出现以下错误：

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

Java 代码（RemotePixelStorage.java:120）：

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

每个语句都是对存储过程add_pixel的调用，它将向表中添加一个元素并在必要时删除旧元素。

这个jdbc异常是什么意思？我该如何克服这个问题？我是否应该尝试使用 catch 块提交几次？

mysql innodb

2 个回答

  1. Best Answer

    查看存储过程，我看到了一些相当不自然的东西。

    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 ;

    我看到的不自然的事情DELETE ... JOIN是混合 InnoDB 表和 MyISAM 表的查询。这样的 JOIN 对 mysqld 来说往往比较笨拙。

    让我们做pixel_window桌子。创新数据库。事实上，我们不要让它成为一个临时表。当然，困难的部分是pixel_window为会话创建一个唯一的表。pixel_window 临时表的前缀为“pixel_window_”，并附加了连接 ID。

    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 ;

    试试看 ！！！

    CAVEAT：既然我创造了这个怪物，我也需要杀死它。

    • 1

  2. 您说您刚刚从“磁盘已满”状态中恢复过来。
    您也从未说过其他哪些进程/程序访问这些数据。
    我们需要枚举其他访问程序，因为它们可能是死锁的根源。

    请记住，死锁意味着竞争访问，尤其是对于写入。
    现在……考虑一下您的ACID品质：原子性、一致性、耐用性和隔离性……

    • 0

相关问题