[2k5] 잠금 힌트(Lock Hint) 설정 및 확인

1. /**********************************************************************************************
   -- Title : [2k5] 잠금 힌트(Lock Hint) 설정 및 확인
   -- Reference : hanbitbook.co.kr
   -- Key word : 잠금 힌트 lock hint repeatable read tablock xlock sys.dm_tran_locks
   **********************************************************************************************/
   use master;
   go
2. restore database sqldb from disk ='d:\sqldb.bak' with replace;
   go
3. use sqldb;
   go
4. select * from usertbl;
   go
5. select resource_type, resource_database_id, resource_associated_entity_id, request_mode
    from sys.dm_tran_locks;
   go
6. begin tran;
    select * from usertbl where name = '김남일';
   rollback tran;
   go
7. begin tran;
    select * from usertbl with (repeatableread) where name = '김남일';
       go
8. select resource_type, resource_database_id, resource_associated_entity_id, request_mode
    from sys.dm_tran_locks;
   go
9. select object_name(2073058421);
   select xact_state(), @@trancount;
   go
10. /* 새 쿼리창 시작 */
    use sqldb;
    go
11. begin tran;
     select * from usertbl;
     go
12. update usertbl set addr = '제주' where name = '김남일'; -- 대기
    go                                                      -- 중지 버튼으로 취소
13. insert into usertbl(userid,name,birthyear,addr) values ('dti','당탕이',2003,'서울')
    go
14. commit tran;
    go
    /* 새 쿼리창 끝 */
15. begin tran;
     select * from usertbl with (tablock, xlock) where name = '김남일';
    go
16. select object_name(2073058421);
    select xact_state(), @@trancount;
    select resource_type, resource_database_id, resource_associated_entity_id, request_mode
     from sys.dm_tran_locks;
    go
17. /* 새 쿼리창 시작 */
    begin tran;
     select * from usertbl; -- 대기
        -- 중지 버튼으로 취소
    /* 새 쿼리창 끝 */
    rollback tran;
    go
18. select @@trancount;
    select xact_state();