[mySQL5.5] 08장. Transaction & Locking

n  Lock Conflict 관리

 

1)    먼저 실습을 위한 account hr과 scott을 생성합니다.

 

t1>grant select on world_innodb.* to 'hr'@'localhost' identified by 'oracle';

 

t1>grant select on world_innodb.* to 'scott'@'localhost' identified by 'oracle';

 

2) 첫번째 터미널은 hr로 접속합니다.

$mysql –uhr -poracle

mysql>prompt hr>

 

1. trnsaction을 시작합니다.

hr>start transaction;

 

2. select …for update로 exclusive lock을 획득합니다.

hr>select id from City where id=1 for update;

+------+

| id   |

+------+

| 4080 |

+------+

1 row in set (0.00 sec)

 

hr>select sleep(50);

+-----------+

| sleep(20) |

+-----------+

|         0 |

+-----------+

1 row in set (20.00 sec)

 

 

3)  2번째 터미널은 scott계정으로 접속합니다.

1. 접속합니다.

$mysql –uscott -poracle

mysql> prompt scott>

 

scott>show variables like '%lock%';

+-----------------------------------------+------------+

| Variable_name                           | Value      |

+-----------------------------------------+------------+

| innodb_autoinc_lock_mode                | 1          |

| innodb_lock_wait_timeout                | 50         |

 

2. 세션 1 과 동일한 SQL문을 실행합니다.

scott>select id from City where id=1 for update;

 

: 이 세션은 hang상태가 됩니다. 왜냐하면, 현재 세션 1이 lock을 보유중이기 떄문입니다.

 

4) 이제 lock정보를 monitor하기 위한 3번째 터미널을 시작합니다.
1. root로 접속합니다.
$mysql –uroot –poracle

2. 현재 대기중인 세션을 조회합니다.

mysql> SELECT r.trx_id waiting_trx_id,         

r.trx_mysql_thread_id waiting_thread,       

r.trx_query waiting_query,       

b.trx_id blocking_trx_id,        

b.trx_mysql_thread_id blocking_thread,       

b.trx_query blocking_query   

FROM  information_schema.innodb_lock_waits w  

INNER JOIN information_schema.innodb_trx b 

ON   b.trx_id = w.blocking_trx_id  

INNER JOIN information_schema.innodb_trx r 

ON     r.trx_id = w.requesting_trx_id;

+----------------+----------------+----------------------------------------------+-----------------+-----------------+----------------+

| waiting_trx_id | waiting_thread | waiting_query                                | blocking_trx_id | blocking_thread | blocking_query |

+----------------+----------------+----------------------------------------------+-----------------+-----------------+----------------+

| 1C0E           |              5 | select id from City where id=1 for update | 1C09            |               4 | NULL           |

+----------------+----------------+----------------------------------------------+-----------------+-----------------+----------------+

1 row in set (0.00 sec)

 

3. 현재 lock정보를 조회합니다.

mysql> select trx_id,trx_state,

trx_requested_lock_id,t

rx_mysql_thread_id,

trx_query 

from INFORMATION_SCHEMA.INNODB_TRX;

+--------+-----------+-----------------------+---------------------+----------------------------------------------+

| trx_id | trx_state | trx_requested_lock_id | trx_mysql_thread_id | trx_query                                    |

+--------+-----------+-----------------------+---------------------+----------------------------------------------+

| 1C0E   | LOCK WAIT | 1C0E:0:465:31         |                   5 | select id from City where id=1 for update |

| 1C09   | RUNNING   | NULL                  |                   4 | NULL                                         |

+--------+-----------+-----------------------+---------------------+----------------------------------------------+

2 rows in set (0.00 sec)

 

4. 대기중인 세션을 kill합니다.

mysql> kill 5;

Query OK, 0 rows affected (0.00 sec)

 

 

5. scott계정으로 시작한 2번째 터미널 화면으로 이동합니다.

scott>select id from City where id=1 for update;

ERROR 2013 (HY000): Lost connection to MySQL server during query

 

6. 새로운 SQL을 실행하면, 오류가 발생함을 확인할 수 있습니다.

scott>select id from City where id=1 for update;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...