Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

INSERT/UPDATE getting blocked immediately? Expand / Collapse
Author
Message
Posted Wednesday, July 30, 2014 1:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:40 PM
Points: 1,058, Visits: 3,008

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 = @SITE and 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


Post #1597605
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse