MySQL 교착 상태 사용법과 탐지 및 회피 방법에 대한 자세한 설명

이 글은 mysql에 대한 관련 지식을 제공하며, 주로 MySQL 교착 상태 감지 및 회피 방법에 대한 자세한 설명을 소개합니다. 이 글은 특정 참고 가치가 있는 주제와 관련된 자세한 내용을 소개합니다. 보세요, 모두에게 도움이 되길 바랍니다.

추천 학습: mysql 비디오 튜토리얼

우리가 잠금을 사용할 때 주의하고 피해야 할 문제가 있습니다. 우리는 배타적 잠금이 상호 배타적인 특성을 가지고 있다는 것을 알고 있습니다. 트랜잭션이나 스레드가 잠금을 보유하면 다른 스레드가 잠금을 획득하지 못하게 됩니다. 이로 인해 루프에서 기다리면 교착 상태가 발생할 수 있습니다.

이 문제를 여러 측면에서 분석해야 합니다. 하나는 잠금이 해제되지 않는 이유이고, 두 번째는 차단된 경우 어떻게 해야 하는지, 세 번째는 교착 상태가 발생하는 방식과 이를 방지하는 방법입니다.

잠금 해제 및 차단

검토: 잠금은 언제 해제되나요?
트랜잭션 종료(커밋, 롤백)﹔
클라이언트 연결이 끊어졌습니다.

한 트랜잭션이 잠금을 해제하지 않은 경우 다른 트랜잭션은 얼마나 오랫동안 차단되나요?
그렇다면 동시 액세스가 상대적으로 높을 때 많은 수의 트랜잭션이 필요한 잠금을 즉시 획득할 수 없으면 정지됩니다. 많은 컴퓨터 리소스를 차지하고, 심각한 성능 문제를 일으키며, 심지어 데이터베이스 전체에 드래그가 발생하기도 합니다.

온라인에서 이런 실수가 두렵나요?

[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

MySQL에는 잠금을 획득하기 위한 대기 시간을 제어하는 ​​매개변수가 있으며 기본값은 50초입니다.

show VARIABLES like "innodb_lock_wait_timeout";

교착상태는 아무리 기다려도 잠금을 획득할 수 없습니다. 이 경우 50초를 기다려야 하는 것은 헛된 50초 아닌가요?

교착상태 발생 및 감지

시연하고 두 개의 세션을 엽니다.

타임라인 철회를 용이하게 하기 위해 여기에 사진이 사용됩니다. 관심이 있는 경우 모방할 수 있습니다.

밤나무 1:

밤나무 2 :

첫 번째 트랜잭션에서 교착 상태가 감지되어 즉시 종료되었습니다. 두 번째 트랜잭션에서는 50초도 기다리지 않고 잠금을 획득했습니다.

[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

교착 상태가 발생했기 때문에 바로 감지할 수 있습니다. 특정 조건이 충족되어야 하는데, 우리 프로그래머에게 있어 조건이 명확하다는 것은 교착 상태가 발생하면 일반적으로 알고리즘(대기 그래프)을 통해 이를 자동으로 감지할 수 있음을 의미합니다.

교착 상태가 발생하려면 어떤 조건을 충족해야 할까요? 잠금 자체가 상호 배타적이기 때문에 교착 상태가 발생하는 조건은 다음과 같습니다.

• (1) 하나의 트랜잭션만 동시에 이 잠금을 유지할 수 있습니다. 2) 이 트랜잭션이 잠금을 해제한 후 다른 트랜잭션은 잠금을 획득해야 하며 강제로 잠금을 해제할 수 없습니다.
• (3) 여러 트랜잭션이 대기 루프를 형성하면 교착 상태가 발생합니다.
• 이발소에는 이사가 두 명 있어요. 머리 자르기를 담당하는 토니 선생님과 머리 감는 일을 담당하시는 켈빈 선생님이 계십니다. 토니 선생님은 동시에 두 사람의 머리를 자를 수 없습니다. 이를 상호 배타적이라고 합니다.

토니가 다른 사람의 머리를 자를 때 멈춰달라고 부탁할 수 없고, 이를 강제로 빼앗을 수 없습니다라고 합니다. 互斥。

Tony在给别人在剪头的时候，你不能让他停下来帮你剪头，这个叫不能强行剥夺。
如果Tony的客户对Kelvin说:你不帮我洗头我怎么剪头? Kelvin 的客户对Tony说:你不帮我剪头我怎么洗头?这个就叫形成等待环路토니의 고객이 켈빈에게: 머리를 감아주지 않으면 어떻게 해야 합니까? 켈빈의 고객이 토니에게: 머리를 자르지 않으면 머리를 어떻게 감을까요?라고 말합니다. >대기 루프를 형성 code>합니다.
실제로 교착상태가 발생하는 상황은 많이 있는데, 모두 위의 세 가지 조건을 충족합니다.
이것이 테이블 잠금의 리소스가 한 번에 획득되므로 테이블 잠금이 교착 상태를 일으키지 않는 이유이기도 합니다.

Lock이 해제되지 않은 경우 Blocking이나 교착상태가 많이 발생하여 시스템 처리량이 감소할 수 있습니다. 이때 어떤 트랜잭션이 Lock을 보유하고 있는지 확인해야 합니다.

잠금 정보 보기(로그)

우선 SHow STATUS 명령에는 일부 행 잠금 정보가 포함됩니다.

show status like 'innodb_row_lock_%';

lnnodb_row_lock_current_waits: 현재 잠금을 기다리는 잠금 수
lnnodb_row_lock_time: 시스템 시작 시; 현재 잠금 시간의 총 길이(ms)
Innodb_row_lock_time_avg: 매번 대기하는 데 소요된 평균 시간
Innodb_row_lock_time_max: 시스템 시작부터 지금까지 가장 긴 시간을 기다리는 데 소요된 시간
lnnodb_row_lock_waits: 시스템 시작부터 총 대기 시간 이제 빈도로.

SHOW 명령은 요약 정보입니다. InnoDB는 또한 트랜잭션과 잠금을 분석하는 세 가지 테이블을 제공합니다.

select * from information_schema.INNODB_TRX; --当前运行的所有事务﹐还有具体的语句

select* from information_schema.INNODB_LOCKS; --当前出现的锁

select * from information_schema.INNODB_LOCK_WAITS; --锁等待的对应关系

更加详细的锁信息，开启标准监控和锁监控:

额外的监控肯定会消耗额外的性能

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

通过分析锁日志，找出持有锁的事务之后呢?
如果一个事务长时间持有锁不释放，可以kill事务对应的线程ID，也就是INNODB_TRX表中的trx_mysql_thread_id，例如执行kill 4，kill 7， kill 8。
当然，死锁的问题不能每次都靠kill线程来解决，这是治标不治本的行为。我们应该尽量在应用端，也就是在编码的过程中避免。
有哪些可以避免死锁的方法呢?

死锁的避免

• 1、在程序中，操作多张表时，尽量以相同的顺序来访问（避免形成等待环路)
• 2、批量操作单张表数据的时候，先对数据进行排序(避免形成等待环路);
• 3、申请足够级别的锁，如果要操作数据，就申请排它锁;
• 4、尽量使用索引访问数据，避免没有where条件的操作，避免锁表;
• 5、如果可以，大事务化成小事务;
• 6、使用等值查询而不是范围查询查询数据，命中记录，避免间隙锁对并发的影响。

推荐学习：mysql视频教程

위 내용은 MySQL 교착 상태 사용법과 탐지 및 회피 방법에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!