Post reply

exec sp_getapplock - Different behaviour using go or without go

  • June 6, 2014 at 7:41 am

    #307973

    Hi everybody,

    we have a strange behaviour while using sp_getapplock (we tried to use this to solve a problem with a deadlock involving some insert while another session was trying to execute a ALTER SWITCH command).

    Assuming having two sessions this is the first that start

    [font="System"]use SKY_RGT

    begin transaction

    declare @ResCode int

    exec @ResCode = sp_getapplock

    @Resource = 'SEMAFORO',

    @LockOwner = 'Session',

    @LockMode = 'Exclusive'

    alter table TABLE_1 switch partition 501

    to TABLE_2 partition 501[/font]

    here is the situation that we have after the execution (result are coming from what we see querying sys.dm_tran_locks)

    then Session 2 start using this command

    [font="System"]use SKY_RGT

    begin transaction

    declare @ResCode int

    exec @ResCode = sp_getapplock

    @Resource = 'SEMAFORO',

    @LockOwner = 'Session',

    @LockMode = 'Exclusive'

    insert into TABLE_2

    select TIT.*

    from TABLE_3 TIT

    where TIT.SCE_COD = 501[/font]

    and here is the lock situation after this

    as you can see it seems to ignore the application lock.

    Different situation if we use the "GO" command after the lock. Here is Session 1 using the GO (but in session 1 using or not the go is invariant because the application lock is acquired anyway)

    [font="System"]use SKY_RGT

    begin transaction

    declare @ResCode int

    exec @ResCode = sp_getapplock

    @Resource = 'SEMAFORO',

    @LockOwner = 'Session',

    @LockMode = 'Exclusive'

    go

    alter table TABLE_1 switch partition 501

    to TABLE_2 partition 501[/font]

    and here the lock situation after this

    Then it comes session 2 using GO command

    [font="System"]use SKY_RGT

    begin transaction

    declare @ResCode int

    exec @ResCode = sp_getapplock

    @Resource = 'SEMAFORO',

    @LockOwner = 'Session',

    @LockMode = 'Exclusive'

    go

    insert into TABLE_2

    select TIT.*

    from TABLE_3 TIT

    where TIT.SCE_COD = 501[/font]

    and here the lock situation after this

    the last one is the situation that we expected but why is working only using the go? The problem is that we have to transfer this kind of pattern inside stored procedures and inside SP is not possible to use the GO.

    Any advice on this?

    Thanks in advance.

    Leonardo

  • June 11, 2014 at 1:21 am

    #1720646

    Any advice?

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply