mysql innodb table row rock에 대해 알아보자

우선 알아보기전에 InnoDB에 대해 알아보자

InnoDb란  스토리지 엔진이다. 검색해보니 MyISAM과 많이 비교하고 있다. 나도 비교하면서 보겠다.

MyISAM : MyISAM은 ISAM(indexed Sequential Access Method)의 업그레이드 버전으로 non-transactional-safe테이블을 관리

모델디자인이 단순하다는게 장점이라서 전체적인 속도가 InnoDB보다 빠른데 특히 select 속도가 빨라 읽기 작업에 적합하다.

하지만

-데이터 무결성이 보장되지 않는다.

-트랜잭션 지원이 없다. ( 문제가 생겨도 데이터 입력을 그냥 해버린다 ;; )

-table-level-lock을 지원해 insert update등의 쓰기 속도가 느리다.

결론적으로 MyISAM은 읽기만 필요한 작업일경우에 사용된다.

InnoDB : 트랜잭션을 지원해 transaction-safe테이블 관리

트랜잭션을 지원하기때문에 commit, rollback, 장애복구,row-level locking,외래키등 다양한 기능을 지원한다.

장점으로

-데이터 무결성이 보장된다.

-동시성 제어가 가능하다

-제약조건,외래키 생성이 가능하다

-row-level lock을 사용해 변경작업이 속도가 빠르다

하지만

-여러기능이 있다보니 모델디자인 시간이 많이 걸린다

-시스템 자원을 많이 잡아먹는다

결론적으로 InnoDB는 데이터 변경이 빈번하고 대용량 데이터일때 사용된다.

lock

위의 글을 읽다보니 든 생각은 그렇다면 InnoDB는 자동으로 레코드접근시 다른 사용자의 접근으로부터 자동 lock이라는걸까?

기본적인 lock의 개념을 알아보자

기본적으로 lock설정이라하면 특정 table을 사용하고 있는 상황에서 다른 접속자의 접근 차단기능이다.

-read lock은 다른사용자는 읽기만할 수 있다.

-write lock은 다른사용자는 접근도 못한다.

결론적으로 InnoDB는

-row 레벨 lock을 사용하며 쿼리 수행시 락을 유발하지 않는 consistent읽기 방식을 이용한다.

-락 정보는 매우 공간 효율적으로 저장되기 때문에 lock 에스컬레이션이 필요없다.

여기 확실한 예제가 있다.

InnoDB는 두가지의 표중 row-level rock을 구현하고 있다.

-Shared lock     :트랜잭션이 row 읽기를 허용

-Exclusive lock  :트랜잭션이 update,delte 허용

상황 : 트랜잭션 T1과 T2가 있다. 만약에 T1이 row r에 대해서 Shared lock을 가지고 있다

요청: T2가 row r에 대해 shared lock을 요청했다.

결과 : 즉시 락을 얻었다.

결론 : T1,T2  모두 shared lock을 가지고있다.

요청: T2가 row r에 Exclusive lock을 요청했다.

결과 : 즉시 락을 얻지 못했다.

결론 : T2는 대기한다.

요청: T1이 row r에 대해 Exclusive lcok을 얻었다.

결과: T2는 shared,Exclusive lock 둘 다 즉시 얻을 수 없다.

결론: T2는 대기한다.

더 확실한 예제가 있다.

1.table을 하나 생성했다.

create table messages( id int auto_increment primary key, message varchar(140)) engine=InnoDB

2. lock을 걸어둔뒤 다른 곳에서 쿼리를 날려보면 안되는걸 볼 수 있다.

프로세스 상태를 확인할 수 있다.

이제 첫번째 세션에서 두번째 세션에서 하려던 쿼리를 날려보자 대기하려던 세션2는 없는걸 삭제하려했기때문에  dead lcok이 걸린다.

결론적으로 어떤 스토리지 엔진을 사용하느냐에 따라  lock이 설정된다.

출처url

http://ojava.tistory.com/25

https://m.blog.naver.com/PostView.nhn?blogId=istarkmh&logNo=80147796919&proxyReferer=https%3A%2F%2Fwww.google.co.kr%2F

https://wwarchblog.wordpress.com/2016/04/06/ch14-3-innodb-transaction-model-and-locking/

http://blog.saltfactory.net/introduce-mysql-lock/