트랜잭션 격리 수준 / lock / select 문
트랜잭션 격리 수준 변경 및 확인
SET autocommit = 0; -- 자동 commit 비활성화
트랜잭션 격리 수준 확인
방법 01.
SELECT @@GLOBAL.transaction_isolation; -- GLOBAL 정보 확인
방법 02.
SELECT @@SESSION.transaction_isolation; -- SESSION 정보 확인
REPEATABLE-READ– 기본값
READ UNCOMMITTED
- READ UNCOMMITTED 수준에서는 트랜잭션에서의 변경내용이 COMMIT이나 ROLLBACK 여부에 상관ㅇ 벗이 다른 트랜잭션에서 보여진다.
- 그리고 이러한 현상때문에
DIRTY READ가 발생한다. - DIRTY READ 현상은 데이터가 나타났다가 사라졌다가 하는 현상을 초래하므로 애플리케이션 개발자와 사용자를 상당히 혼란스럽게 만든다.
기본 구성
drop database db01;
create database db01;
use db01;
create table t1(id int);
create table t2(id int);
create user user1@'%' identified by '1234';
grant all privileges on DB01.* to user1@'%';
flush privileges;
Ex. 01
============================================================
ROOT 세션(세션 레벨 수정) USER1 세션
============================================================
SET autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @@SESSION.transaction_isolation;
-----------------------------------------------------------
use db01;
begin;
select * from t1;
->결과1<-
-----------------------------------------------------------
use db01;
begin;
insert into t1 values (10);
-----------------------------------------------------------
use db01;
select * from t1;
->결과2<-
------------------------------------------------------------
rollback;
------------------------------------------------------------
select * from t1;
->결과1<-
------------------------------------------------------------
commit; commit;
============================================================
READ COMMITED
- 이 레벨에서는 DIRTY READ와 같은 현상은 발생하지 않는다.
- 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문이다.
- READ COMMITTED은 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때 항상 같은 결과를 가져와야 한다는
REPEATABLE READ정합성에 어긋난다.
Ex.
=================================================================
ROOT 세션(세션 레벨 수정) USER1 세션
=================================================================
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@SESSION.transaction_isolation;
SET autocommit = 0;
-----------------------------------------------------------------
use db01;
begin;
select * from t1;
->결과1<-
-----------------------------------------------------------------
use db01;
begin;
insert into t1 values(50);
-----------------------------------------------------------------
select * from t1;
->결과1<-
-----------------------------------------------------------------
commit;
-----------------------------------------------------------------
select * from t1;
->결과2<-
----------------------------------------------------------------
REPEATABLE READ
- 이 격리 수준에서는 위에서 설명한
NON-REPEABLE READ이 발생하지 않는다. - NON-REPEABLE READ가 발생하지 않는 이유는 트랜잭션을 생성할 때 트랜잭션 번호를 부여받게 되는데 그때부터 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 작은 트랜잭션 번호에서 변경한 것만 보이게 되기 때문이다.
Ex.
=================================================================
ROOT 세션(세션 레벨 수정) USER1 세션
=================================================================
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@SESSION.transaction_isolation;
SET autocommit = 0;
------------------------------------------------------------------
use db01;
begin;
select * from t1;
->결과1<-
-----------------------------------------------------------------
use db01;
begin;
insert into t1 values(60);
-----------------------------------------------------------------
select * from t1;
->결과1<-
-----------------------------------------------------------------
commit;
-----------------------------------------------------------------
select * from t1; select * from t1;
->결과1<- ->결과2<-
----------------------------------------------------------------
commit;
-----------------------------------------------------------------
use db01;
begin;
select * from t1;
->결과2<-
=================================================================
SERIALIZABLE
- 트랜잭션의 격리 수준이 SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경할 수 없다.
- 즉 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없다.
Ex.
=================================================================
ROOT 세션(세션 레벨 수정) USER1 세션
=================================================================
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@SESSION.transaction_isolation;
SET autocommit = 0;
-----------------------------------------------------------------
use db01;
begin;
select * from t1;
->결과1<-
-----------------------------------------------------------------
use db01;
begin;
insert into t1 values(60); //대기->에러
-----------------------------------------------------------------
select * from t1;
->결과1<-
-----------------------------------------------------------------
commit;
-----------------------------------------------------------------
select * from t1; select * from t1;
->결과1<- ->결과1<-
-----------------------------------------------------------------
MySQL의 잠금(lock)
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 db01;
use db01;
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 DB01.* to user1@'%';
create user user2@'%' identified by '1234';
grant all privileges on DB01.* to user2@'%';
flush privileges;
Ex. READ LOCK(SHARED LOCK)
==========================================================================================
ROOT 세션 USER1 세션 USER2 세션
==========================================================================================
lock tables t1 read;
update t1 set id=11 where id=1;
>-에러-<
------------------------------------------------------------------------------------------
insert into t1 values(2); insert into t2 values(1);
->대기<- >-실행-<
------------------------------------------------------------------------------------------
unlock tables;
------------------------------------------------------------------------------------------
->대기.자동실행<-
==========================================================================================
Ex. WRITE LOCK(EXCLUSIVE LOCK)
=========================================================================================
ROOT 세션 USER1 세션 USER2 세션
=========================================================================================
lock tables t1 write;
update t1 set id=11 where id=1;
>-실행-<
----------------------------------------------------------------------------------------
select * from t1; insert into t1 values(100);
->대기<- ->대기<-
----------------------------------------------------------------------------------------
unlock tables;
----------------------------------------------------------------------------------------
->대기.자동실행<- ->대기.자동실행<-
========================================================================================
데드락(Deadlock)
- 둘 이상의 프로세스가 다른 프로세스가 점유하고 있는 자원을 서로 기다릴 때 무한 대기에 빠지는 상황을 말함.
- 두 개 이상의 작업이 서로 상대방의 작업이 끝나기만을 기다리고 있기 때문에 결과적으로 아무것도 완료되지 못하는 상태를 가리킨다.
- 데드락은 트랜잭션을 지원하는 데이터베이스에서는 자주 발생하는 문제이다.
Ex. 기본 구성
- 프로세스1은 자원1을 먼저 점유합니다.
- 프로세스2는 자원2를 먼저 점유합니다.
- 프로세스1은 자원2를 사용할 수 있을 때까지 대기합니다.
- 프로세스2는 자원1을 사용할 수 있을 때까지 대기합니다.
- 프로세스1, 프로세스2는 서로 자원을 얻기 위해 대기하면서 교착 상태에 빠집니다.
- 외부의 개입이 없다면 두 프로세스는 서로 종료되지 못합니다.
drop database db02;
create database db02;
use db02;
create table t1(id int, name varchar(30));
insert into t1 values (10,'홍길동'),(20,'서길동');
grant all privileges on DB02.* to user1@'%';
방법.
=================================================================================
트랜잭션 1 (root 사용자) 트랜잭션 2 (user1 사용자)
=================================================================================
SET SQL_SAFE_UPDATES=0;
SET autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @@SESSION.transaction_isolation;
---------------------------------------------------------------------------------------------------
use db02;
BEGIN;
---------------------------------------------------------------------------------------------------
use db02;
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='홍길동';
====== 데드락 발생 지점 ========
ERROR 1213 (40001): Deadlock found
when trying to get lock;
try restarting transaction
----------------------------------------------------------------------------------------------------
COMMIT;
----------------------------------------------------------------------------------------------------
COMMIT;
==========================================================
SELECT 문에서 where 절
비교 연산자
= 같음
!= 같지 않음
<> 같지 않음 (ISO 표준)
> 초과(크다)
>= 이상(같거나 크다)
< 미만(작다)
<= 이하(같거나 작다)
논리 연산자
AND 앞에 있는 조건과 뒤에 있는 조건이 모두 참 값
OR 앞에 있는 조건이나 뒤에 있는 조건 중 하나라도 참 값
NOT 뒤에 있는 조건의 결과를 반대로 변경 (참 값일 경우, 거짓 값으로 변경)
범위 연산자
A BETWEEN B AND A와 B 사이의 값 ( A와 B를 모두 포함, A <= Value <= B )
집합 연산자
IN (A, B, C …) 범위 내의 값을 하나라도 만족 ( A OR B OR C … )
NOT IN (A, B, C …) 범위 내의 값을 하나라도 만족X
속성 확인 연산자
IS NULL NULL 값을 갖는 데이터
IS NOT NULL NULL 값을 갖지 않는 데이터
LIKE ‘패턴 문자열’ 패턴 문자열을 만족하는 값
_ 한 글자
% 모든 문자
값의 존재 여부 확인 연산자
EXIST 서브쿼리의 값이 있을 경우 반환함
NOT EXIST 서브쿼리의 값이 없는 경우 반환함
Ex. 기본 연산자
- 조건이 문자열인 경우
select * from usertbl where name = '김경호';
- 조건이 상수인 경우
select * from usertbl where birthyear= 1987;
- 조건 두 개를 모두 만족하는 데이터를 출력할 때 and
select userid, name from usertbl where birthyear >= 1970 and height >= 182;
- 조건 두 개 중에 하나라도 만족하는 데이터를 출력할 때 or
select userid, name from usertbl where birthyear >= 1970 or height >= 182;
- between 사용
select userid, name from usertbl where height between 180 and 183;
- 여러 개를 만족하는 데이터 출력 in
select userid, name, addr from usertbl where addr in('경남','전남', '경북','전북');
- 제외하고 출력 not in
select userid, name, addr from usertbl where addr not in('경남');
- 포함하는(포함하지 않는) 문자 출력 like
- like와 %구문은 통상 검색할 때 이런 형태로 많이 쓰인다.
select userid, name, addr from usertbl where name like('김%');
select userid, name, addr from usertbl where name not like('김%');
select userid, name, addr from usertbl where name like '_종신';
- null인 경우 is null이며, null이 아닌 경우는 is not null
select userid, name, addr from usertbl where name is null;
select userid, name, addr from usertbl where name is not null;
ORDER BY 절
- 오름차순 정렬 asc
select * from usertbl order by mdate (asc);
- 내림차순 desc
select * from usertbl order by mdate desc;
- 여러 개 컬럼 정렬
select * from usertbl order by height desc,name asc;
집계 함수와 GROUP BY, HAVING 절
- GROUP BY에 정의한 내용(컬럼 또는 변형된 컬럼)만 SELECT절에 그대로 사용할 수 있다.
- GROUP BY에 정의하지 않은 컬럼을 SELECT절에서 사용하려면 반드시 집계함수 처리를 해야 한다.
where절에는 집계함수를 쓸 수 없기 때문에, having절에서 그룹함수를 이용해 조건을 사용한다.
- MySQL : from -> where -> select -> group by -> having -> order by
- Oracle : from -> where -> group by -> having -> select -> order by
집계 함수
- sum()은 합계를 구한다.
- avg()은 평균을 구한다.
- min()은 최소값을 구한다.
- max()은 최대값을 구한다.
- count()은 행의 개수를 구한다.
- count(distinct 칼럼이름)은 행의 유일한 개수를 구한다.
Ex. 01
- userid별로 구매한 건수를 userid오름차순으로 출력하라
select userid as '사용자 아이디', sum(amount) as '구매한 건수'
from buytbl
group by userid
order by userid;
Ex. 02
- 사용자 아이디 별 평균 구매 건수를 내림 차순으로 출력하라
select userid as '사용자 아이디' , avg(amount) as '평균 구매 건수'
from buytbl
group by userid
order by avg(amount) desc;
Ex. 03
- usertbl에서 최대 키와 최소 키, 이름을 출력해라
select name, height from usertbl
where height = ( select max(height) from usertbl ) or height = ( select min(height) from usertbl );
Ex. 04
- 총 구매액이 1000만원 초과인(sum(price * amount)>1000) 사람의 사용자 아이디와 총 구매액을 사용자 아이디 별로 구하는데 총 구매액 내림차순으로 출력하라
select userid as '사용자 아이디' , sum(price * amount) as '총 구매액'
from buytbl
group by userid
having sum(price * amount)>1000
order by sum(price * amount) desc;
Ex. 05
- groupname별로 소합계를 내어주는 with rollup 사용하기
select groupname, sum(price* amount)
from buytbl
group by groupname
with rollup;