MSSQL DB - EX09_1

--10 / 12
--Lock and Isolation Level ( Session 1 )
USE exampledb

--테이블 생성
CREATE TABLE bankaccount
(
 ac_owner nvarchar (10) not null primary key,
 balance money not null
)
INSERT INTO bankaccount VALUES('장동건', 1000)
INSERT INTO bankaccount VALUES('김윤석', 1000)

SELECT * FROM bankaccount

 

UPDATE bankaccount SET balance = 1000

--1.
DBCC USEROPTIONS

BEGIN TRANSACTION

UPDATE bankaccount
SET balance = balance - 500
WHERE ac_owner = '장동건'

UPDATE bankaccount
SET balance = balance + 500
WHERE ac_owner = '김윤석'

ROLLBACK TRANSACTION

--2.
BEGIN TRANSACTION

UPDATE bankaccount
SET balance = balance - 500
WHERE ac_owner = '장동건'

UPDATE bankaccount
SET balance = balance + 500
WHERE ac_owner = '김윤석'

ROLLBACK TRANSACTION

--3.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION

SELECT * FROM bankaccount

SELECT * FROM bankaccount

ROLLBACK TRANSACTION

--4.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DBCC USEROPTIONS

BEGIN TRANSACTION

UPDATE bankaccount
SET balance = balance - 500
WHERE ac_owner = '장동건'

UPDATE bankaccount
SET balance = balance + 500
WHERE ac_owner = '김윤석'

SELECT * FROM bankaccount

SELECT * FROM bankaccount

ROLLBACK TRANSACTION