with SQL Server

오늘 얘기할 부분은 Truncate Table 시 롤백이 가능할까? 입니다.

어떻게 생각하시나요? 가능할까요? 아님 안될까요?

보통 Big테이블 전체를 싹 날려버릴 때 Delete문을 이용하는 것보다

속도가 훨~씬 빠르기 때문에 가끔 쓰실 겁니다. 물론 그만큼 주의하셔서 쓰시겠지만요…

그럼 Delete문은 왜 Truncate Table 보다 속도가 확연히 느릴까요?

그렇습니다. Delete문은 행 마다(Single-row) 트랜잭션 로그에 기록을 하면서 삭제되기 때문에 느리죠.

반면 Truncate Table은 행 마다 트랜잭션을 로그에 기록하지 않습니다.

Delete 시 row-lock과 Truncate Table 시 page-lock의 영향도 있지만 이번 주제와 관계없기 때문에 넘어가죠~

어쨌든 Truncate Table은 트랜잭션 로그에 기록하지 않기 때문에 롤백이 불가능하다고 생각됩니다.

과연 그럴까요? 본론으로 돌아와서 결론을 말씀 드리면 롤백이 가능합니다.

트랜잭션 로그에 기록되지 않는데 어떻게 롤백이 가능할까? 라고 생각되기도 하지만

Truncate Table 역시 트랜잭션 로그에 최소한 로그(minimally logged)을 기록합니다.

- 최소한 로그? 직역하니 좀 이상하네요…ㅎ

여기서 최소한 로그(minimally logged)는 Truncate Table 시 할당이 취소된(deallocated) pages와 extents 입니다.

           * page(SQL Server 최소 단위 8KB) * 8 = 1 Extent

보지 않고는 믿을 수 없다!라고 생각하시는 분들을 위해서 간단한 테스트를…ㅎ

--1. AdventureWorks DB에서 Product 테이블을 Products로 복사 합니다.

USE AdventureWorks

GO

IF EXISTS (SELECT * FROM sys.tables  WHERE name = 'Products' AND schema_id = 1)

    DROP TABLE dbo.Products;

GO

SELECT * INTO dbo.Products

FROM Production.Product;

GO

--2. Products 테이블의 카운트와 할당 된 page를 확인 합니다.

SELECT COUNT(*) FROM dbo.Products;

GO

DBCC IND('AdventureWorks', 'Products', -1);

GO

- 504건과 할당된 페이지들이 보이는군요.

--3. 트랜잭션을 걸고 Products 테이블을 Truncate 한 후

--   count(*), page, extent 정보와 AdventureWorks DB의 lock 정보를 한번에 볼까요?

BEGIN TRAN

TRUNCATE TABLE dbo.Products;

SELECT COUNT(*) FROM dbo.Products;

DBCC IND('AdventureWorks', 'Products', -1);

DBCC EXTENTINFO('AdventureWorks', 'Products', -1);

SELECT resource_type, resource_description,

        request_mode FROM sys.dm_tran_locks

WHERE  resource_type IN ('EXTENT', 'PAGE')

AND   resource_database_id = DB_ID('AdventureWorks');

- Truncate Table 후 0건이고, page와 extent는 할당이 취소되어서 정보가 없습니다.

   (여기서 할당 취소한 최소한 로그(minimally logged)를 트랜잭션 로그에 기록합니다.)

   하지만 해당 page와 extent 재사용 하지 못하게 배타적 잠금(eXclusive lock) X을 요청하고 있군요.

--4. 롤백합니다.

ROLLBACK TRAN;

GO

SELECT COUNT(*) FROM dbo.Products;

DBCC IND('AdventureWorks', 'Products', -1);

 - Check result yourself :)

끝~

Reference : http://sqlblog.com/blogs/kalen_delaney/default.aspx?p=2