MSSQL(4)

트랜잭션
먼저 계속 말하더라. 데이터베이스의 일관성은 매우 중요하다. 항상 동기화, 락 이런 개념이 나오면 어려워지더라. 근데 그만큼 중요하니까..
드가보자..

먼저 트랜잭션부터 살펴보자.
트랜잭션은 디비에서 엄청 중요중요한 개념으로 하나의 논리적인 작업 단위를 말한다.
보통 ACID 특성이라고 하는데 이걸 잘 생각하면서 날려야 한다.
기억이 가물가물한데 ATOMY, CONSISTENCY, ISOLATION, DE... 기억안난다..
DURABILITY라고 한다 (역시 GPT)
한번 살펴보자.
1. 원자성
A. 트랜잭션 내의 모든 작업은 한번에 처리되어야 한다.
B. 모두 성공적으로 실행되거나 아니면 아예 100% 실행되지 말아야 한다.
2. 일관성
A. 트랜잭션이 성공적으로 완료되면, 데이터베이스는 하나의 일관된 상태에서 다른 일관된 상태로 전환되어야 한다.
3. 고립성
A. 동시에 실행되는 여러 트랜잭션들은 서로 영향을 주어서는 안된다.
B. 각 트랜잭션은 독립적으로 실행되어야 한다.
4. 지속성
A. 트랜잭션이 성공적으로 완료되면, 그 결과는 시슽엠의 장애가 발생하더라도 영구적으로 반영되어야 한다.

• 예시 쿼리문
  BEGIN TRANSACTION; -- 트랜잭션 시작

-- 데이터베이스 작업 수행
INSERT INTO myTable (Column1) VALUES ('Value1');
UPDATE myTable SET Column1 = 'Value2' WHERE Column2 = 'Condition';
DELETE FROM myTable WHERE Column3 = 'Condition';

-- 모든 작업이 성공적으로 완료되면 트랜잭션을 커밋합니다.
COMMIT TRANSACTION;

-- 만약 오류가 발생하면 트랜잭션을 롤백합니다.
ROLLBACK TRANSACTION;

• 트랜잭션은 BEGIN TRANSACTION으로 시작하고 모든 작업이 성공적으로 완료되면
• COMMIT TRANSACTION으로 커밋하여서 변경사항을 데이터베이스에 영구적으로 반영한다.
   만약에 작업 중에 오류가 발생하거나 일관성을 유지할 수 없는 상황이 발생하면 ROLLBACK TRANSACTION을 사용하여 변경사항을 취소하고 트랜잭션 시작 이전의 상태로 되돌린다.
• SQL SERVER는 일정한 체크포인트마다 트랜잭션을 실제 데이터 파일에 반영한다.

1. 자동 커밋 트랜잭션
   A. 개발 INSERT UPDATE DELETE (DML) 문이 성공적으로 끝나면 자동으로 커밋되고, 실패하면 자동으로 롤백된다.
   B. 커밋과 롤백은 사용자가 결정할 수 없다.
   i. 그럼 누가? SQL SERVER가 알아서 자동으로 결정한다.
   C. 이것이 SQL SERVER의 기본 트랜잭션 모드이다.
   D. 삭제는 기본적으로 취소될 수 없다.
   i. 내가 제일 주의해야할것 ㅠㅠㅠㅠ .. 삭제하면 수습사원에서 역적사원이 되어버리는거야..
   ii. 코드 예시를 볼까?
   DELETE dbo.Employee
   WHERE EmpID = ‘S0020’
   ROLLBACK TRANSACTION
   GO
   이렇게 하면 트랜잭션 롤백 안된다. 이미 자동으로 커밋해버렸다고~~
2. 명시적 트랜잭션
   A. 트랜잭션의 시작과 끝을 사용자가 직접 결정한다.
   B. 여기서는 사용자가 직접결정하는 거기 때문에 방지할 수 있는 것들이 많다. 코드를 보자
   BEGIN TRANSACTION
   UPDATE dbo.Employee
   SET Salary = Salary – 1000
   WHERE EmpID = ‘S0001’
   UPDATE dbo.Employee
   SET Salary = Salary + 1000
   WHERE EmpID = ‘S0002’
   COMMIT TRANSACTION
   그러니까 이게 어떤 코드냐면 S0001 사원이 그냥 연봉에서 떼서 S0002 사원에게 돈을 빌려준거라고 하자.
   당연히 둘다 실행되거나 둘다 실행 안되거나 둘중 하나여야 한다.

다음으로 이런 경우도 가능해졌다.
BEGIN TRANSACTION
DELETE dbo.Employee
WHERE EmpID = ‘S0002’
ROLLBACK TRANSACTION
GO
아까는 비슷한 사례가 롤백이 안되었었는데 이제는 롤백 트랜잭션 ( 취소 ) 가 가능하다.

3. 명시적 트랜잭션에서의 주의점
   A. 명시적 트랜잭션을 한다고 해서 모든 경우에서 그 롤백이 보장되는 것은 아니다.
   B. 명시적 트랜잭션은 기본적으로 시스템 정지와 같은 상황에서 일관성이 보장된다.
   i. 만약에 쿼리 중에 하나라도 에러가 났을때 취소가 가능하게 하려면 옵션을 선택해야한다
   ii. SET XACT_ABORT ON

• XACT ABORT 옵션에 대해서
   명시적 트랜잭션에서 필수로 달고 있어야 하는 것 같다.
   주로 DML문에서 많이 쓴다.
   대부분의 오류가 트랜잭션을 자동으로 롤백하게 하지만, 모든 종류의 오류가 트랜잭션을 롤백하는 것은 아니다.
   예를 들어서 구문오류 같은 일부 오류는 트랜잭션을 롤백하지 않는다.
   트랜잭션 중에 발생하는 모든 오류가 트랜잭션을 자동으로 롤백하게 한다.
   오류가 발생하면 SQL 서버가 현재 실행 중인 트랜잭션을 즉시 종료하고 이전 상태로 되돌린다.
   중요성
   오류 처리 간소화
   이 옵션을 사용하면 복잡한 오류 처리 로직을 작성할 필요가 없다.
   트랜잭션 중 발생하는 어떠한 오류도 자동으로 트랜잭션을 롤백하기 때문이다.
   이것은 특히 또또또 데이터 무결성을 유지하는 것에 중요하다.
   데이터 무결성 보장
   쿼리 오류가 발생하면 모든 변경사항이 롤백되므로, 데이터 베이스는 일관된 상태를 유지한다.

• 트랜잭션이 발생하는 과정을 살펴보면,
   먼저, SQL 서버는 트랜잭션 로그를 남긴다.
   다음에 체크포인트마다 실제 데이터에 반영을 한다.
   근데! 여기서 롤 포워드 / 롤 백 개념이 나오는데 함 보자
   예를 들어서
   트랜잭션이 일어났고, 체크포인트를 지나서 별 오류 없이 잘 끝났다.
   그러면 체크포인트에서 저장된 이후의 내용을 트랜잭션 로그를 참고해서 마저 진행한다.
   트랜잭션이 일어났고 체크포인트를 지났는데 갑자기 시스템 정지와 같은 상황이 벌어져서 잘 안끝났다.
   그렇게 되면 체크포인트 이후의 반영은 안된 것이기 때문에, 트랜잭션 로그를 참고해서 다시 롤백한다.

지금 트랜잭션 모드가 3개 나왔자나
1. 자동 커밋 트랜잭션 ( default )
A. SQL SERVER가 알아서..
B. DELETE 특히 취소 안댐
2. 명시적 트랜잭션
A. SET XECT_ABORT ON은 거의 여기선 필수인듯
3. 묵시적 트랜잭션
A. 자동 커밋 트랜잭션과 정반대 되는 개념이다.
B. 자동 커밋 트랜잭션 모드에서 DML 문을 수행하면 SQL SERVER가 자동으로 커밋해버려서 취소가 안된다.
C. 하지만! 묵시적 트랜잭션 모드에서는 DML문을 수행한 이후에 커밋과 롤백을 사용자가 결정한다!

4. 그러면 여기서 의문!!!!!
   A. SET XECT_ABORT ON이 켜진 명시적 트랜잭션 모드와 묵시적 트랜잭션 모드는 대체 모가 다르냐 말이당
   i. 두 트랜잭션 모드 방식의 차이점은 트랜잭션의 시작과 종료를 어떻게 제어하느냐에 있다.
   모드 명시적트랜잭션 ( 대부분 이거 사용 ) 묵시적 트랜잭션 ( 거의 안사용함, 왜 마지막에 알려줌? )
   정의 개발자가 직접 트랜잭션의 시작과 종료를
   BEGIN TRANSACTION / COMMIT TRANSACTION / ROLLBACK TRANSACTION 을 사용하여 명시적으로 제어한다 SET IMPLICITY_TRANSACTIONS ON을 사용하면, SQL SERVER가 자동으로 각 SQL 명령 후에 트랜잭션을 시작한다.
   트랜잭션은 COMMIT 또는 ROLLBACK 명령이 실행될 때까지 열려있다.
   특징 개발자가 트랜잭션의 범위를 정확히 제어할 수 있다.
   복잡한 비즈니스 로직에 대한 정밀한 트랜잭션 관리가 가능하다. 개별 SQL 명령마다 자동으로 트랜잭션이 시작되므로,
   별도로 BEGIN TRANSACTION 을 사용할 필요가 없다.
   하지만 커밋 또는 롤백을 명시적으로 호출해야 한다.
   코드 BEGIN TRANSACTION; -- 트랜잭션 시작
   INSERT INTO Employees (Name, Position) VALUES ('John Doe', 'Manager');
   UPDATE Departments SET ManagerID = 1 WHERE DepartmentID = 5;
   COMMIT TRANSACTION; -- 트랜잭션 커밋 SET IMPLICIT_TRANSACTIONS ON; -- 묵시적 트랜잭션 활성화
   INSERT INTO Employees (Name, Position) VALUES ('Jane Doe', 'Developer');
   -- 트랜잭션이 자동으로 시작됨
   UPDATE Departments SET ManagerID = 2 WHERE DepartmentID = 3;
   COMMIT TRANSACTION; -- 트랜잭션 커밋

트랜잭션과 락은 깊은 연관 관계를 가진다.
기본적으로 운영체제든 뭐던 간에 항상!! 항상!!! 동기화가 어려웠다. 스핀락이었나 그것도 그런 내용 같았는데 뭐 어쨋든..
ㄴ 맞다 히힣.. 기억이 좀 남아있긴한가보네
어쨋든 다시 돌아가서
이번에 잠금에 대해서 알아보게따.

요약)
공유잠금 S는 읽을때
단독잠금 X는 변경할때
공유잠금(S)이란 누가 읽고 있을 때 그 내용에 대해서 단독 잠금(X)를 못걸게 한다. 근데 공유잠금은 걸 수 있다.. 읽을 수 있다는 것이당
말로 좀 풀어서 상식적으로 생각해보면, 읽고 있는데 읽고 있는 내용을 누가 변경은 못한다는 것이다.

그 다음은 단독잠금 (X) 인데 단독 잠금은 데이터를 변경할때 거는 것이다.
1. 일단 단독잠금이 걸린 데이터는 다른 트랜잭션이 공유잠금을 걸 수 없다!!
2. 단독 잠금도 못건다!!! ( 오직 트랜잭션 하나만이 데이터에 대한 단독 잠금을 걸 수 있다 )
그라믄 좀 너무하지 않는가 바꾸고 있는거 읽을 수는 있게 해줄 수 있지 않는가
그래서 준비했다!
1. 잠금 힌트
2. 트랜잭션 격리수준으로 그걸 가능케!
그래서 어떻게 하는지 책에 안나와있어서 GPT로 좀 파보자.
1. 잠금힌트
A. 잠금힌트를 사용하면 SQL서버의 기본 잠금 동작을 오버라이드 하는 것이다.
B. 예를 들어서 NOLOCK 힌트를 사용하면 ( 이게 힌트라기 보다는 무슨 꼬리표 같은 느낌이네 ) 다른 트랜잭션이 단독 잠금을 가지 ㄴ데이터를 읽을 수 있다. 하지만 이 방법은 ‘더러운 읽기’(Dirty Read)를 발생시킬 수 있기 때문에 주의가 필요하다.
C. 어떻게 쓰는지 함 보자
i. SELECT * FROM Employees WITH (NOLOCK)
D. 아아 그 더러운 읽기 먼저 알아보자
E. 더티 읽기는 데이터 베이스에서 트랜잭션 처리과정 중에 발생할 수 있는 현상인데, 한 트랜잭션이 아직 커밋되지 않은 데이터를 다른 트랜잭션이 읽는 경우에 발생한다. 근데 더티까지는 아닌 것 같은데 더티라고 부르는 이유는 읽은 데C774ㅓ가 최종적으로 데이터 베이스에 반영되지 않을 수도 있기 때문이다.
F. 더러운 읽기의 예시를 알아보자
트랜잭션 A : 얘는 데이터를 수정하는중
BEGIN TRANSACTION
UPDATE Employees SET Salary = Salary + 5000 WHERE EmployeeID = 1;
( 아직 커밋 안함 )
트랜잭션 B : 얘는 동시에 실행중인 트랜잭션인데 읽으려나보다
SELECT Salary FROM Employees WHERE EmployeeID = 1;

이 경우에 트랜잭션 B는 아직 커밋을 안한 A의 데이터를 읽는 거기 때문에 (더러운) 걸로 치자.
아아아아아아ㅏㅇ아ㅏ아아 생각도 못한게 있어따.
생각해보니까 트랜잭션 A는 롤백될 수도 있다. 근데 롤백될 수 도 있는데 바꾸고 있는걸 읽게 되면 완전히 낙동강 오리알 될 수 있는거
( 이제 더럽다고 한 게 이해 되따 )
2. 트랜잭션 격리수준
3. 트랜잭션 중요도 수준
A. DEADLOCK_PRIORITY -10 ~ 10
B. DEADLOCK_PRIORITY LOW / NORMAL(default) / HIGH (5)
4. 근데 이것들이 왜 필요할까?
A. 데드락 상태 때문이다.

• GO에 대해서 한번 그냥 궁금해져서
   SQL은 배치batch를 하나의 단위로 해서 컴파일한다.
   배치 내에서 선언된 로컬 변수는 해당 배치 안에서만 유효하다.
   스코프 같은 느낌
   각 배치는 서로 독립적으로 실행되어 한 배치의 실행이 다른 배치에 영향을 주지 않는다.
   그래서 배치가 독립적으로 실행된다는게 병렬로 cpu가 실행하는건지가 궁금해져따
   근데 그건 아니라고 하고
   데이터베이스 안에서 별도의 실행단위로 처리된다는 것을 나타낸다고 한다.
   하나의 배치가 완전히 실행되고 나면 다음 배치가 실행되며
   각 배치는 서로 독립적인 컴파일과 실행 컨텍스트를 가진다.
   SQL SERVER에서 배치 간의 독립적 실행이 의미하는 것은, 각 배치가 데이터베이스 엔진에 의해서 별도로 컴파일되고 실행된다는 것을 의미한다.
   하나의 배치 안에서 실행된 명령은 다른 배치의 실행과는 별개로 관리된다.
   이것은 배치 내에서 선언된 변수나 임시 테이블 등이 다른 배치와 공유되지 않음을 의미한다.
   즉! 내가 생각했던 병렬 프로그래밍을 SQL SERVER에서는 사용하지 않는다!
   다만, 쿼리 실행계획의 일부로 병렬처리를 사용할 수 있다고 한다.

• 데드락

• 세션
   개요
   SQL SERVER에서의 세션은 클라이언트 어플리케이션과 데이터베이스 서버 간의 연결을 의미함
   세션은 사용자의 로그인부터 로그아웃까지, 또는 연결이 시작되어서 종료될 때까지의 지속되는 대화나 상호작용의 기간을 나타낸다.
   각 세션은 고유한 세션 ID로 식별되며, SQL SERVER는 이를 통해서 동시에 여러 클라이언트의 요청을 처리할 수 있다.
   세션의 특징 및 고유성
   고유성
   각 세션은 고유한 세션 ID ( @@SPID ) 를 가지며, 이를 통해 개별 클라이언트와의 상호작용을 관리한다.
   상태유지성
   세션은 클라이언트와 SQL SERVER 간의 상태를 유지한다. 예를 들어서, 세션 내에서 선언도니 변수나 트랜잭션 상태는 해당 세션에서만 유효
   리소스관리
   SQL SERVER는 각 세션에 할당된 리소스 ( 메모리, 잠금 등 )을 관리한다.
   세션 종료시에 해당 세션에 할당된 모든 리소스가 자동으로 해제된다.
   병렬처리
   동시에 여러 세션을 처리함으로써, SQL SERVER는 다중 사용자 환경에서의 병렬 처리와 고성능을 지원한다.
   SQL SERVER에서의 세션관리
   세션 생성
   클라이언트 어플리케이션이 SQL SERVER에 연결을 요청할 때 세션이 생성된다. 이 과정에서 로그인, 인증, 및 세션 초기화가 수행된다.
   세션은 T-SQL 명령을 실행하고 결과를 클라이언트에 반환한다.
   세션동안 여러 T-SQL 명령을 실행할 수 있으며, 각 명령은 독립적으로 또는 트랜잭션의 일부로 처리될 수 있다.
   세션종료
   클라이언트 어플리케이션이 연결을 종료하거나, 네트워크 오류 등으로 세션이 끊어지면, SQL Server는 세션을 종료하고, 해당 세션에 할당된 모든 리소스를 정리한다.
   세션과 관련된 T-SQL 명령
   @@ SPID : 시스템 전역변수 : 현재 세션의 세션 ID
   KILL 세션 아이디 : 지정된 세션 ID의 세션을 강제로 종료함

• 트랜잭션 격리수준 ( Transaction Isolation Level )
  SET TRANSACTION ISOLATION LEVEL ~~~
  BEGIN TRANSACTION
  END TRANSACTION
   이 단어 잘 살펴보면 ACID 성질의
   I가 들어가 있다. 그만큼 중요하다는거.
   일단 SQL SERVER에서의 트랜잭션 격리수준이라는게 뭘 말하는 걸까.
   트랜잭션의 실행 중 다른 트랜잭션으로부터 데이터를 어느정도로 보호할지 결정하는 설정이다.
   격리수준은 데이터의 정확성과 동시성 사이의 균형을 조정하는데 사용된다.
   다중 트랜잭션 중 발생할 수 있는 다양한 사고들
   더티읽기
   비반복읽기
   팬텀읽기
   SQL SERVER는 다섯가지 트랜잭션 격리수준을 지원한다.
  SET TRANSACTION ISOLATION LEVEL
  READ UNCOMMITTED
  GO
  SELECT * FROM dbo.Employee
  WHERE EmpID = ‘S0001’
  GO
  트랜잭션 격리수준을 설정할건데, READ COMMITTED로 설정할거야.
  가장 낮은 격리수준이고, 더티리드가 가능해. 보고 있는 와중에 롤백하게 되버리면 다른 결과를 보는거야

1. READ UNCOMMITTED
   A. 가장 낮은 격리수준
   B. 다른 트랜잭션에 의해 커밋되지 않은 변경내용도 조회할 수 있다. => 이게 더티 읽기임
   C. 이 수준에서 성능은 높지만, 데이터 일관성이 보장되지 않기에 데이터의 정확성이 보장되지 않는다.
   D. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2. READ COMMITED
   A. SQL SERVER의 기본 격리수준
   B. 커밋된 데이터만 읽을 수 있기 때문에 더티 읽기는 방지된다.
   C. 그러나, 같은 데이터를 여러 번 읽을 때 다른 결과를 볼 수 있는 비반복 읽기가 발생할 수 있다.
   i. 같은 데이터를 여러 번 읽는 그 트랜잭션 사이에 다른 트랜잭션이 해당 데이터를 수정하고 커밋할 수 있음
   D. SET TRANSACTION ISOLATION LEVEL READ COMMITTED
3. REPEATABLE READ
   A. 트랜잭션이 완료될때까지 읽은 데이터에 대한 공유잠금 S를 유지한다.
   i. 동일한 데이터에 대한 비반복 읽기를 방지한다.
   B. 그러나 다른 트랜잭션이 새로운 데이터를 삽입하는 것을 막지 못하기에 팬팀 읽기는 가능하다.
4. SNAPSHOT
   A. 트랜잭션이 시작될 때의 데이터 스냅샷을 기반으로 작업을 수행한다.
   B. 이 격리 수준은 동시성을 높이면서도 , 더티 읽기 , 비반복 읽기 , 팬텀 읽기를 모두 방지함
5. SERIALIZABLE
   A. 가장 높은 격리 수준
   B. 트랜잭션이 읽거나 수정한 모든 데이터에 잠금을 걸어 다른 트랜잭션의 접근을 완전히 차단한다.
   i. 이 격리 수준에서는 동시성이 가장 낮지만, 데이터의 일관성이 가장 높게 유지 된다.
   원래 격리 수준도 그렇고 어떤 기술도 성능과 데이터 일관성 등 다른 요소와 트레이드 오프관계이다.
   다 좋을 수 없다.

• 잠금힌트는 어려운 개념이다. SQL SERVER가 자동적으로 하니까, 이해가 좀 부족하다면 함부로 건들지 말자.

트랜잭션은 확실히 락에 들어오니까 어렵다.
한번 다시 정리해보자.

일단 락은 읽기 - 공유락S, 쓰기 - 단독락X이 있고, IX, IS, SIX를 적절히 활용해서 잠금된 데이터에 대한 식별 속도를 높인다.
알아두어야 할 것은
1. 트랜잭션 격리수준
SET TRANSACTION ISOLATION LEVEL ~~ 격리수준
A. READ UNCOMMITTED
i. Dirty Read 가능
ii. non-repeatable read 가능
iii. phontom read 가능
B. READ COMMITTED ( default )
i. Dirty Read 불가능 (당연히)
ii. non-Repeatable Read 불가능
iii. phantom read 가능
C. REPEATABLE READ
i. Phantom Read 가능
ii. non-Repeatable Read 불가능
D. SNAPSHOT
i. 트랜잭션이 시작될 때의 데이터 스냅샷을 기반으로 작업을 수행함
ii. 비반복 읽기와 팬텀읽기를 모두 방지하고, 동시에 동시성을 높인다.
E. SERIALIZABLE
i. 가장 높은 격리수준
ii. 트랜잭션이 읽거나 수정한 모든 데이터에 잠금을 걸어 다른 트랜잭션의 접근을 완전히 차단
iii. 동시성이 가장 낮지만, 데이터의 일관성과 정확성이 가장 높게 유지된다.
2. 테이블 힌트
쿼리문을 작성할때 FROM 절에 오는 테이블 이름 다음에 WITH 구문으로 옵션을 설정한다.
원래 쿼리 최적화기가 자동으로 하는 기능인데, 사용자가 강제로 원하는 형태로 하겠다는 것이다.
A. 잠금힌트
B. .... 이것저것 있는데 여기서는 잠금 힌트만 다룬다.
3. 좋지 않은 것들
A. Dirty Read
i. 커밋되지 않은 내용을 읽는 것
ii. 성능은 높으나, 단점은 당연히 위험하겠지. 불확실한 데이터인데
B. Phantom Read
i. 한 트랜잭션 내에서 쿼리를 두번 실행했을 때 첫번째 쿼리 실행과 두번째 쿼리 실행 사이에 다른 트랜잭션이 새로운 데이터를 삽입하거나 삭제하여 두 쿼리 결과가 다르게 나타내는 현상
ii. 예시
1. 트랜잭션 a가 특정 조건을 만족하는 모든 행을 조회한다.
2. 트랜잭션 b가 같은 조건을 만족하는 새로운 행을 삽입한 후 커밋한다.
3. 트랜잭션 a가 그 다음에 다시 같은 조건으로 쿼리를 실행했을 때, 새로 삽입된 행이 포함된 결과를 얻게 된다.
4. 결론적으로 같은 쿼리인데 다른 결과가 나오는거.
C. Non-Repeatable read
i. 일어나는 이유는 같은 데이터 행의 값 변경으로 인해서 발생한다.
4. 팬텀(유령)읽기와 비반복읽기가 헷깔린다.
A. 공통점은 두 현상모두 한 트랜잭션 내에서 데이터의 일관성을 유지하는데 문제를 일으킬 수 있으며, 다중 트랜잭션 환경에서 발생한다는 것
B. 차이점은
i. 비반복 읽기는 같은 데이터 행의 값 변경으로 인해서 발생하는 반면에
ii. 팬텀읽기는 쿼리 결과집합의 행 자체가 변경(추가나 삭제) 되어 발생한다.
iii. 정리하면 비반복 읽기는 데이터 값의 변경에 초점을 맞추고
iv. 팬텀읽기는 결과 집합의 구성 변경에 초점을 맞춘다.