db lock 종류에 대해 설명하시오

MySQL 동시성 제어, 서버 튜닝, 파티션, SQL 활용

동시성 제어

• MySQL 은 Lock 과 트랜잭션으로 동시성을 제어
• MySQL Lock 매커니즘
  • 쓰레드가 데이터 집합을 요청할 때 Lock 설정
  • 데이터 집합은 테이블, 행, 페이지, 메타데이타
  • 쓰레드가 데이터 집합 처리 완료하면 Lock 해제
• MySQL 트랜잭션
  • 신뢰성 있게 처리되는 일의 단위

Lock

• 읽기 Lock
  • R 만 가능 CUD 불가
• 쓰기 Lock
  • CRUD 불가

Lock 종류

• Table Lock
  • 테이블 전체에 Lock을 걸기 때문에 다른 트랜잭션이 해당 테이블에 접근 불가
• Row Lock
  • 하나 이상의 개별적인 행에 Lock
  • Lock이 걸리지 않은 행에 접근 가능
• Page Lock
  • BDB 라는 스토리지 엔진에 적용.
  • 거의 사용되지 않음
• Metadata Lock
  • 쓰레드가 테이블을 사용할 때 테이블의 모든 메타데이터에 Lock
  • 메타테이터란, DDL 에 의해 변경되는 정보

테이블 Lock

• MyISAM 스토리지 엔진에서 사용
• 스토리지 엔진과 무관하게 LOCK TABLE 명령으로 Lock 가능
• v5.5 이상에서 DDL 실행 시 Lock 발생

행 Lock

• 테이블 전체가 아닌 일부 행에 lock
• InnoDB 는 행 lock

행 lock

create table my_inno_t( id int auto_increment primary key, name varchar(10) ); insert into my_inno_t(name) values('a'); insert into my_inno_t(name) values('b'); insert into my_inno_t(name) values('c'); select * from my_inno_t; update my_inno_t set name = sleep(200) where id = 1; # 200초 걸림

다른 세션 열고 select * from my_inno_t; 하면 가능 update my_inno_t set name='aaa' where id=1; 하면 불가능 행에 lock 이 걸려서

트랜잭션

• MySQL 은 스토리지 엔지 레벨으로 트랜잭션
  • select @@autocommit; 으로 트랜재션 사용 여부 확인
• MySQL 트랜잭션
  • start transaction; or begin; 으로 트랜잭션 시작
  • commit; 으로 수행 완료
  • rollback 으로 수행 취소