[MYSQL /Database] Mysql 잠금(lock)- Read lock, Write lock,dead lock

 Mysql 잠금

- SHARED LOCK(S) = READ

. TRANSACTION 1에서(S)LOCK을 얻은 상태에서 TRANSACTION 2에서(S)LOCK을 요청하는 경우 [허용] 된다.

. TRANSACTION 1에서(S)LOCK을 얻은 상태에서 TRANSACTION 2에서(X)LOCK을 요청하는 경우 [거부] 된다.

 

- EXCLUSIVE LOCK(X) = WRITE

. TRANSACTION 1에서(X)LOCK을 얻은 상태에서 TRANSACTION 2에서(S)LOCK을 요청하는 경우 [거부] 된다.

. TRANSACTION 1에서(X)LOCK을 얻은 상태에서 TRANSACTION 2에서(X)LOCK을 요청하는 경우 [거부] 된다.

 

/연습용 데이터베이스 생성/

 create database db02;

   use db02;
   create table t1(id int);
   create table t2(id int);

   insert into t1 values(1);
   insert into t2 values(2);

   create user user1@'%' identified by '1234';
   grant all privileges on DB02.* to user1@'%';

   create user user2@'%' identified by '1234';
   grant all privileges on DB02.* to user2@'%';

   flush privileges;

Read Lock(shared lock)

Root session	user1 session	user2 session
lock table t1 read; update t1 set id=11 where id=1; => 에러
	insert into t1 values(2);- => 대기	insert into t1 values(100); ->대기
unlock tables;
	-> 대기중인것 자동 실행

그림 1. ROOT  / USER1 에서 확인

USER2에서는 INSERT가 실행됩니다. 

 

그림2

그림2에서 ROOT 창에서 UNLOCK TABLE; 명령어를 적어 줌과 동시에 USER1 SESSION에서 대기상태에 걸려있던 INSERT구문이 바로 실행되는 것을 확인할 수 있다. 

 

WRITE LOCK(EXCLUSIVE LOCK)

Root session	user1 session	user2 session
lock table t1 write; update t1 set id=11 where id=1; => 실행
	insert into t1 values(2);- => 대기	insert into t1 values(100); -> 대기
unlock tables;
	-> 대기중인것 자동 실행

 

그림3. lock걸린 상태

그림4 .  lock이 풀린 후 상태

 데드락 Deadlock

- 둘 이상의 프로세스가 다른 프로세스가 점유하고 있는 자원을 서로 기다릴 때 무한 대기에 빠지는 상황을 일컫습니다.

- 두 개 이상의 작업이 서로 상대방의 작업이 끝나기만을 기다리고 있기 때문에  결과적으로 아무것도 완료되지 못하는 상태를 가리킨다.

- 데드락은 트랜잭션을 지원하는 데이터베이스에서는 자주 발생하는 문제이다.

 

그림5 . dead lock

<연습>

1. 프로세스1은 자원1을 먼저 점유합니다.
2. 프로세스2는 자원2를 먼저 점유합니다.
3. 프로세스1은 자원2를 사용할 수 있을 때까지 대기합니다.
4. 프로세스2는 자원1을 사용할 수 있을 때까지 대기합니다.
5. 프로세스1, 프로세스2는 서로 자원을 얻기 위해 대기하면서 교착 상태에 빠집니다.
6. 외부의 개입이 없다면 두 프로세스는 서로 종료되지 못합니다.

 

/실습용 데이터 베이스 생성/

   drop database db03;
   create database db03;

   use db03;
   create table t1(id int, name varchar(30));
   insert into t1 values (10,'홍길동'),(20,'서길동');

   grant all privileges on DB03.* to user1@'%';

 

Root session	user1 session
SET SQL_SAFE_UPDATES=0;   SET autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT @@SESSION.transaction_isolation;
use db03; BEGIN;
	use db03; BEGIN;
UPDATE t1   SET id = id-10 WHERE name='홍길동';
	UPDATE t1 SET id = id-10 WHERE name='서길동';
UPDATE t1   SET id = id+10 WHERE name ='서길동';	UPDATE t1 SET id = id+10 WHERE name='홍길동';

그림6.  error 40001  deadlock