Mssql 이란 - mssql ilan

[트랜잭션(Transaction) 개념]

트랜잭션은 "데이터베이스 관리 시스템에서 논리적 작업 단위로 수행되는 작업 단위"이다.

여기서 말하는 "데이터베이스 관리 시스템"이란 트랜잭션의 성공과 실패가 분명하고 상호 독립적이며, 일관되고 믿을 수 있는 시스템을 의미한다.

일상에서 만나볼 수 있는 트랜잭션은 은행에서 계좌이체 시스템이 있다.

트랜잭션을 사용하지 않을 경우 어떤 일이 발생할 수 있는지 확인해보자.

예를 들어 A, B가 동시에 잔고가 0원인 한 계좌로 계좌이체를 했을 시 A의 100원 계좌이체 작업이 종료되지 않았을 땐 아직 잔고가 0원이기 때문에 0원->100원으로 변경하는 작업을 수행할 것이다. A의 계좌이체가 종료되기 전 B의 100원 계좌이체 작업이 진행된다면 해당 계좌의 잔고는 아직 0원으로 기록되어 있기 때문에 A와 동일하게 0원->100원으로 변경시킨다.

100원씩 두 곳에서 계좌이체를 했지만 잔고는 100원으로 남게 될 것이다. 혹은 동시에 실행을 하면서 충돌이 발생해 데이터가 날아가 버리는 결과로 이어질 수 있다.

그렇기 때문에 데이터베이스의 트랜잭션은 어떠한 경우에도 데이터가 온전한 상태로 유지되어야 하며 이러한 기능을 하기 위해 반드시 지켜져야 할 4가지 특징이 있다.

[트랜잭션(Transaction) 특징(ACID)]

4가지 특징의 첫 글자를 따서 ACID라 칭한다.

원자성(Atomicity)
일관성(Consistency)
독립성(Isolation)
지속성(Durability)

원자성(Atomicity)

트랜잭션 안에 있는 작업은 모두 데이터베이스에 반영되던가 아예 반영되지 않아야 하며 오류, 실패 발생 시 모든 작업은 취소되어야 한다.

예를 들어 계좌이체시 오류가 발생하면 반드시 원래 상태로 되돌려야 한다. 중간단계는 존재하면 안 됨.

일관성(Consistency)

트랜잭션이 실행을 완료하면 일관성 있는 데이트베이스 상태를 유지한다.

시스템의 고정 요소는 수행 전과 수행 완료 후의 상태가 같아야 한다.

예를 들어 계좌이체 종료 후 이체받은 계좌의 총액과 이체한 계좌의 총액의 합은 계좌이체 하기 전과 동일해야 한다.

고립성(Isolation)

하나의 트랜잭션이 실행 중일 땐 다른 트랜잭션은 대기하여야 하며 실행 중인 트랜잭션이 완전히 종료될 때까지 다른 트랜잭션에서 수행 결과를 참조할 수 없다.

개념 설명시 들었던 예 참조.

지속성(Durability)

트랜잭션이 완료되면 주기억장치가 아닌 디스크와 같은 보조기억장치에 저장되거나 그렇지 않더라도 영구적으로 반영되야 한다. (트랜잭션이 실행되면 *.ldf파일에, 트랜잭션이 완료(COMMIT)되면 *.mdf파일에 변경이 적용된다)

[트랜잭션(Transaction)의 상태]

활동(Active)	트랜잭션이 실행 중에 있는 상태.
장애(Failed)	트랜잭션 실행에 오류가 발생하여 중단된 상태.
철회(Aborted)	트랜잭션이 비정상적으로 종료되어 Rollback 연산을 수행한 상태.
부분 완료(Partially Committed)	트랜잭션이 정상 실행 되고 Commit 연산이 실행되기 직전의 상태.
완료(Committed)	트랜잭션이 정상 종료되고 Commit 연산을 실행한 상태.

MS-SQL 에는 파일 그룹이라는 개념이 있습니다.

논리적인 관점으로 특정 데이터베이스에는 PRIMARY 라는 하나의 기본 그룹이 있고 그 안에 하나의 데이터 파일이 있습니다.

그리고 트랜잭션 로그 파일도 있는데, 이는 파일 그룹과 관련이 없습니다.

즉, 파일 2개와 그룹 1개인 최소한의 구조로 구성되며, 기본적인 파일 그룹 PRIMARY 의 이름은 변경하거나 제거할 수 없습니다.

물리적인 관점에서 본다면 생성된 데이터베이스는 하나의 하드디스크에 데이터 파일과 트랜잭션 로그 파일로 구성됩니다.

하지만 동시다발적으로 데이터가 대규모로 입력될 때 하나의 디스크에 동시에 접근한다면 성능은 느려질 수 밖에 없습니다.

따라서 데이터 파일과 트랜잭션 로그 파일은 기본적으로 서로 다른 디스크에 분리해서 관리해야 합니다.

하나의 그룹에 여러 개의 데이터 파일을 사용하는 것 보다는, 파일을 별도의 그룹으로 분리하는 것이 바람직합니다.

또 분리된 그룹의 파일을 서로 다른 디스크에 배치한다면 디스크의 입출력 속도가 향상되며 SQL Server 성능이 향상됩니다.

예를 들면, A 라는 테이블은 PRIMARY 파일 그룹에 생성하고, 인덱스는 SECONDARY 파일 그룹에 생성한다면, 이 테이블을 검색할 때 성능 향상을 꽤할 수 있습니다.

왜냐하면 테이블을 검색할 때는 대개 인덱스도 동시에 접근하게 되므로, 테이블과 인덱스를 서로 다른 파일에 저장했을 경우에는 서로 다른 디스크에 동시에 접근하게 되는 효과를 내게 되기 때문입니다.

만약 하나의 파일 그룹에 여러 개의 데이터 파일이 있을 경우에는 RAID0 방식과 비슷하게 동시에 나눠서 저장됩니다.

PRIMARY 그룹에 A01_DB 데이터 파일과, A02_DB 데이터 파일이 있다면 공평하게 나눠서 동시에 저장됩니다.

따라서 하나의 그룹에 소속된 여러 개의 파일을 각기 다른 디스크에 저장하면 저장 성능이 높아집니다.

파일 그룹에 관한 쿼리는 아래 url 을 참고해 주세요.

https://docs.microsoft.com/ko-kr/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-2017

USEmaster; GO CREATEDATABASE MyDB ON PRIMARY ( NAME='MyDB_Primary', FILENAME= 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_Prm.mdf', SIZE=4MB, MAXSIZE=10MB, FILEGROWTH=1MB), FILEGROUP MyDB_FG1 ( NAME = 'MyDB_FG1_Dat1', FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_1.ndf', SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=1MB), ( NAME = 'MyDB_FG1_Dat2', FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_2.ndf', SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=1MB), FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM ( NAME = 'MyDB_FG_FS', FILENAME = 'c:\Data\filestream1') LOGON ( NAME='MyDB_log', FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB.ldf', SIZE=1MB, MAXSIZE=10MB, FILEGROWTH=1MB); GO ALTERDATABASE MyDB MODIFY FILEGROUP MyDB_FG1 DEFAULT; GO USE MyDB; CREATETABLE MyTable ( cola int PRIMARY KEY, colb char(8) ) ON MyDB_FG1; GO CREATETABLE MyFSTable ( cola int PRIMARY KEY, colb VARBINARY(MAX) FILESTREAM NULL )