MySQL 오류 해결 "잠금을 시도할 때 데드락이 발견되었습니다. 트랜잭션 재시작 시도"

MySQL 오류 해결 "잠금을 시도할 때 데드락이 발견되었습니다. 트랜잭션 재시작 시도"

DBI를 통해 연결되는 병렬 Perl 프로세스에 의해 작은 방식으로 지속적으로 업데이트되는 약 5,000,000개의 행이 있는 MySQL 테이블을 가지고 있습니다.테이블에는 열이 10개 정도 있고 인덱스도 여러 개 있습니다.

상당히 일반적인 작업은 때때로 다음과 같은 오류를 발생시킵니다.

DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at Db.pm line 276.

오류를 트리거하는 SQL 문은 다음과 같습니다.

UPDATE file_table SET a_lock = 'process-1234' WHERE param1 = 'X' AND param2 = 'Y' AND param3 = 'Z' LIMIT 47

가끔만 오류가 발생합니다.통화량의 1% 이내로 예상합니다.그러나 작은 테이블에서는 그런 일이 발생하지 않았고 데이터베이스가 증가함에 따라 더 일반적이 되었습니다.

제가 실행하고 있는 4개의 거의 동일한 프로세스가 동일한 행에서 시도되지 않도록 하기 위해 file_table의 a_lock 필드를 사용하고 있습니다.이 한계는 그들의 작품을 작은 덩어리로 쪼개기 위해 고안된 것입니다.

MySQL 또는 DBD::mysql에 대한 튜닝을 많이 하지 않았습니다.MySQL은 표준 Solaris 배포 환경이며 데이터베이스 연결은 다음과 같이 설정됩니다.

my $dsn = "DBI:mysql:database=" . $DbConfig::database . ";host=${DbConfig::hostname};port=${DbConfig::port}";
my $dbh = DBI->connect($dsn, $DbConfig::username, $DbConfig::password, { RaiseError => 1, AutoCommit => 1 }) or die $DBI::errstr;

저는 온라인에서 몇몇 다른 사람들이 비슷한 오류를 보고했고 이것이 진정한 교착상태일 수도 있다는 것을 보았습니다.

두 가지 질문이 있습니다.

1. 위의 오류가 발생하고 있는 제 상황은 정확히 무엇인가요?

2. 이를 해결하거나 빈도를 줄일 수 있는 간단한 방법이 있습니까?예를 들어, "Db.pm line 276의 restart 트랜잭션"에 대해서는 정확히 어떻게 해야 합니까?

미리 감사드립니다.

InnoDB 또는 행 수준 트랜잭션 RDBMS를 사용하는 경우 완벽하게 정상적인 상황에서도 쓰기 트랜잭션이 교착 상태를 야기할 수 있습니다.테이블이 크고 쓰기가 크며 트랜잭션 블록이 길수록 데드락이 발생할 가능성이 높아집니다.당신의 상황에서는 아마도 이것들이 합쳐진 것일 겁니다.

교착상태를 진정으로 해결할 수 있는 유일한 방법은 코드를 작성하여 예상하는 것입니다.데이터베이스 코드가 잘 작성되어 있으면 일반적으로 이 작업은 그리 어렵지 않습니다. 둘 수 있습니다.try/catch쿼리 실행 로직을 중심으로 오류가 발생할 때 교착 상태를 찾습니다.하나를 잡으면 실패한 쿼리를 다시 실행하는 것이 일반적인 작업입니다.

MySQL 매뉴얼에 있는 이 페이지를 읽어보시기를 강력히 권합니다.데드락에 대처하고 빈도를 줄이기 위해 해야 할 일들의 목록이 있습니다.

정답은 맞지만 데드락을 처리하는 방법에 대한 펄 문서는 약간 희박하며 PrintError, RaiseError 및 HandleError 옵션과 혼동될 수 있습니다.HandleError를 사용하는 대신 Print and Raise를 사용한 다음 Try와 같은 방법을 사용하는 것 같습니다.코드를 포장하고 오류를 확인하기에는 아주 작습니다.아래 코드는 db 코드가 3초마다 오류가 발생한 sql 문을 다시 실행하는 while loop 내부에 있는 예를 보여줍니다.캐치 블록은 특정 오류 메시지인 $_를 얻습니다.나는 이것을 핸들러 함수 "dbi_err_handler"에게 전달합니다. 이 함수는 $_를 여러 오류에 대해 확인하고 코드가 계속되어야 할 경우 1을 반환하거나(따라서 루프를 끊어야 함) 교착 상태에 있고 다시 시도해야 할 경우 0을 반환합니다.

$sth = $dbh->prepare($strsql);
my $db_res=0;
while($db_res==0)
{
   $db_res=1;
   try{$sth->execute($param1,$param2);}
   catch
   {
       print "caught $_ in insertion to hd_item_upc for upc $upc\n";
       $db_res=dbi_err_handler($_); 
       if($db_res==0){sleep 3;}
   }
}

dbi_err_handler에는 적어도 다음이 있어야 합니다.

sub dbi_err_handler
{
    my($message) = @_;
    if($message=~ m/DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction/)
    {
       $caught=1;
       $retval=0; # we'll check this value and sleep/re-execute if necessary
    }
    return $retval;
}

처리하려는 기타 오류를 포함하고 다시 실행할지 또는 계속할지에 따라 $retval을 설정해야 합니다.

누군가에게 도움이 되길 바랍니다.

다음을 사용하는 경우SELECT FOR UPDATE삽입 전에 고유성 검사를 수행하려면 다음을 활성화하지 않으면 모든 레이스 조건에 대해 교착 상태가 발생합니다.innodb_locks_unsafe_for_binlog선택.고유성을 검사하는 교착 상태가 없는 방법은 다음을 사용하여 고유한 인덱스를 가진 테이블에 행을 맹목적으로 삽입하는 것입니다.INSERT IGNORE, 그런 다음 영향을 받는 행 수를 확인합니다.

밑줄을 더하다my.cnf파일

innodb_locks_unsafe_for_binlog = 1

#

1 - 켜짐
0 - 꺼짐

#

교착 상태 예외의 경우 쿼리를 재시도하는 아이디어는 좋지만, mysql 쿼리는 잠금이 해제될 때까지 계속 대기하기 때문에 매우 느릴 수 있습니다.그리고 교착상태에 대비해서 mysql은 교착상태가 있는지를 파악하고 있고, 교착상태가 있다는 것을 파악한 후에도 교착상태에서 벗어나기 위해 실을 걷어내기 전에 잠시 기다립니다.

내가 이런 상황에 직면했을 때 한 일은 당신의 코드에 locking을 구현하는 것인데, 그것은 mysql의 locking mechanism이 버그로 인해 실패하고 있기 때문입니다.그래서 저는 자바 코드에 저만의 행 레벨 잠금을 구현했습니다.

private HashMap<String, Object> rowIdToRowLockMap = new HashMap<String, Object>();
private final Object hashmapLock = new Object();
public void handleShortCode(Integer rowId)
{
    Object lock = null;
    synchronized(hashmapLock)
    {
      lock = rowIdToRowLockMap.get(rowId);
      if (lock == null)
      {
          rowIdToRowLockMap.put(rowId, lock = new Object());
      }
    }
    synchronized (lock)
    {
        // Execute your queries on row by row id
    }
}

언급URL : https://stackoverflow.com/questions/2596005/working-around-mysql-error-deadlock-found-when-trying-to-get-lock-try-restarti