INSERT/UPDATE getting blocked immediately?

• SQL Galaxy

  One Orange Chip

  Points: 29478

  More actions

  July 30, 2014 at 1:41 am

  #310412

  Hi,

  For resolving blocking issues, I have mention SET TRANSACTION ISOLATION LEVEL SNAPSHOT in that SP. Pls. confirm & suggest , Is it resolve the blocking issues during transaction? As below SP content

  USE [OMPM]

  GO

  CREATE PROCEDURE [dbo].[usp_APPROVAL_SAVE]

  (@TRAN_ID varchar(50),

  @EMP_NO varchar(50),

  @STATUS varchar(50),

  @REMARK varchar(50),

  @ROLE_CODE varchar(50),

  @CONTRACT_TYPE varchar(50),

  @SITE varchar(50)

  )

  AS

  BEGIN

  delete from CMM_EP_APPR_DETAIL where site = @SITE

  and PASS_TYPE = @CONTRACT_TYPE

  and APP_TYPE = 'NEW'

  and level_no in (select level_no from CMM_EP_APPR_LEVEL

  where site = @SITEand PASS_TYPE = @CONTRACT_TYPE

  and APP_TYPE = 'NEW'and ROLE_CODE = @ROLE_CODE)

  and tran_id=@TRAN_ID

  and STATUS='PENDING'

  insert into CMM_EP_APPR_DETAIL (tran_id,pass_type,app_type,level_no,appr_by,appr_dt,remark,status,site)

  select @TRAN_ID, PASS_TYPE, APP_TYPE, LEVEL_NO,

  @EMP_NO, getdate(), @REMARK, @STATUS, @SITE

  from CMM_EP_APPR_LEVEL

  where site = @SITE

  and PASS_TYPE = @CONTRACT_TYPE

  and APP_TYPE = 'NEW'

  and ROLE_CODE = @ROLE_CODE

  --remove role from cmm_new_ep_pending

  if @STATUS = 'APPROVE'

  --set transaction isolation level snapshot

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT

  begin

  --insert into tmp_hari values(@TRAN_ID,@ROLE_CODE + '_1')

  update cmm_new_ep_pending set pending_with=replace(pending_with,'

  '+@ROLE_CODE+'

  ','

  ')

  ,approved_by=isnull(approved_by,'')+@ROLE_CODE+','

  WHERE SITE=@SITE AND TRAN_ID=@TRAN_ID

  --insert into tmp_hari values(@TRAN_ID,@ROLE_CODE)

  end

  --remove role from cmm_new_ep_pending

  if @role_code='EIC'

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT

  begin

  /*

  update CMM_EP_MASTER set eic_no=@EMP_NO,eic_name=(select empname from empmst_h where emp_no=@EMP_NO)

  where site = @SITE and TRAN_ID= @TRAN_ID

  */

  update CMM_EP_MASTER set eic_no=@EMP_NO,eic_name=h.empname

  from EMPMST_H h

  where CMM_EP_MASTER.site = @SITE

  and CMM_EP_MASTER.TRAN_ID= @TRAN_ID

  and h.emp_no=@EMP_NO

  end

  if not exists(select 1 from (

  select d.STATUS

  from CMM_EP_APPR_LEVEL m

  left outer join CMM_EP_APPR_DETAIL d

  on d.TRAN_ID = @TRAN_ID

  and d.LEVEL_NO = m.LEVEL_NO

  and d.PASS_TYPE = m.PASS_TYPE

  and d.APP_TYPE = m.APP_TYPE

  and d.SITE = m.site

  AND d.STATUS != 'REJECT'

  where m.site = @SITE

  and m.APP_TYPE = 'NEW'

  and m.ROLE_CODE != 'SECURITY'

  and m.PASS_TYPE = @CONTRACT_TYPE) v

  where (STATUS is null OR status='PENDING')

  )

  begin

  update CMM_EP_MASTER

  set EP_STATUS = 'APPROVE'

  where site = @SITE

  and TRAN_ID= @TRAN_ID

  end

  if @STATUS = 'REJECT'

  begin

  update cmm_ep_master set ep_status='REJECT' where TRAN_ID= @TRAN_ID and site=@site

  delete from cmm_new_ep_pending where TRAN_ID= @TRAN_ID and site=@site

  end

  if @STATUS = 'APPROVE' and @ROLE_CODE = 'MEDICAL'

  begin

  update cmm_ep_master

  set medical_validity = dateadd(year,1,getdate())

  where TRAN_ID= @TRAN_ID

  and site=@site

  end

  if @STATUS = 'APPROVE' and @ROLE_CODE = 'SAFETY'

  begin

  update cmm_ep_master

  set safety_validity = dateadd(year,1,getdate())

  where TRAN_ID= @TRAN_ID

  and site=@site

  end

  end

  GO