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

    2017. 8. 11. 11:49

    by. 위지원

    우선 알아보기전에 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/

    대화의 장 💬