1. MSSQL(REPEATABLEREAD) -> For Update 와 같다.
SESSION 01 SESSION 02 (같은 Server Management Studio에서 별도의 세션으로 연결된다.)
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
drop table test;
create table test select * from test; <-- 전체 조회 된다.
(id varchar(10)
,name varchar(10)); update test set name = 'change02' where id = 'A'; <-- Blocking
update test set name = 'change02' where id = 'B'; <-- update 처리 됨
delete from test;
insert into test values ('A', 'a name'); -- dbcc inputbuffer(93)
insert into test values ('B', 'b name');
select * from test;
begin tran
select * from test with (REPEATABLEREAD) where id = 'A';
rollback tran;
commit tran;
2. MSSQL(UPDLOCK) -> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 와 같다.
SESSION 01 SESSION 02 (같은 Server Management Studio에서 별도의 세션으로 연결된다.)
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
drop table test;
create table test select * from test; <-- 전체 조회 된다.
(id varchar(10)
,name varchar(10)); update test set name = 'change02' where id = 'A'; <-- Blocking
update test set name = 'change02' where id = 'B'; <-- Blocking
delete from test;
insert into test values ('A', 'a name'); -- dbcc inputbuffer(93)
insert into test values ('B', 'b name');
select * from test;
begin tran
select * from test with (UPDLOCK) where id = 'A';
rollback tran;
commit tran;
SP_LOCK UPDLOCK REPEATABLEREAD
------- ------- --------------
RID U S
PAG IU IS
TAB IX IS
3. ORACLE
SESSION 01 SESSION 02 (새 SQL DEVELOPER 실행해야 별도의 세션으로 연결된다.)
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
drop table test;
create table test select * from test; <-- 전체 조회 된다.
(id varchar(10)
,name varchar(10)); update test set name = 'change02' where id = 'A'; <-- Blocking
update test set name = 'change02' where id = 'B'; <-- update 처리 됨
delete from test;
insert into test values ('A', 'a name'); commit;
insert into test values ('B', 'b name');
select * from test;
select * from test where id = 'A' for update;
rollback;
commit;
4. MSSQL(1), ORACLE(3) 에서 조회결과 같다.
SESSION 01 SESSION 02
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
id name id name
---------- ---------- ---------- ----------
A a name A a name
B change02 B change02
5. 결과
---------------------------------------------------------------------------
MSSQL의 REPEATABLEREAD 힌트가 ORACLE의 for update 구문과 기능이 같다.
https://blog.daonelab.com/post/24/1544/