Tables get locked - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Tables get locked

Author

Topic

Raj_Mahendran
Starting Member

14 Posts

Posted - 2009-01-29 : 12:16:53

Hi,

I'm importing data into a database using cursor as multiple table inserts are to be done. (I have removed mulitple table insertion from the code so as to not confuse you). The problem i face is, when i run this SP, the table is locked till the SP completes importing data. How can i not lock the table while inserting or updating.

/****** Object: StoredProcedure [dbo].[LOAD_DIV13] Script Date: 01/29/2009 12:04:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[LOAD_DIV13]

@dummy char(1)

as

Begin

--MARKS PREEXISTING POLICIES AS UPDATES (ONLY POLICIES WITH ITEM_DESC AS Policies - SWC Div13)
UPDATE TACC_DIV13_IMPORT
SET INS_UPD_IND = 'U'
FROM TACC_DIV13_IMPORT T1
INNER JOIN
(SELECT DISTINCT ITEM_REF FROM ITEM WHERE LTRIM(RTRIM(MASTER_ITEM_DESC)) = 'Policies - SWC Div13') T2
ON T1.POLICY_NO = T2.ITEM_REF

--MARKS NON-EXISTING POLICIES AS NEW RECORDS/ INSERTS
UPDATE TACC_DIV13_IMPORT
SET INS_UPD_IND = 'I'
WHERE POLICY_NO IN (SELECT POLICY_NO FROM TACC_DIV13_IMPORT
LEFT JOIN ITEM ON POLICY_NO = ITEM_REF
WHERE ITEM_REF IS NULL)

declare

@Item_type VARCHAR (50),
@Classification VARCHAR (50),
@Policy_number VARCHAR (50),
@AISLIC VARCHAR (255),
@Tab_no INT,
@RefEntity_ID INT,
@Eff_DT datetime,
@Exp_Dt datetime,
@Create_Dt datetime,
@Insured_NM VARCHAR (255),
@Service_Center VARCHAR (255),
@CLocation VARCHAR (255),
@PLocation VARCHAR (255),
@PREV_Location VARCHAR (255),
@master_item_id INT,
@status_id_active INT,
@cl_id INT,
@current_date_clarion INT,
@Service_Center_ID INT,
@CLocation_ID INT,
@PREV_CLOC_ID INT,
@NEW_REC_CNT INT,
@PLocation_ID INT,
@START_BARCODE INT,
@END_BARCODE INT,
@NEW_ITEM_ID INT,
@UPD_ITEM_ID INT,
@barcode_int INT,
@Contract_No VARCHAR (50),
@Premium_Amt INT,
@Prior_policy_no VARCHAR (255),
@Plan_cd VARCHAR(100),
@State VARCHAR (100),
@record_id INT,
@INS_UPD_FLAG VARCHAR (1),
@ADD_USER_NO VARCHAR(8),
@NEXT_BARCODE VARCHAR(9)

Declare c1 cursor FAST_FORWARD for

select
[Item_Type],
[Classification],
[Policy_no],
[AISLIC],
[Tab_no],
[Eff_Dt],
[Exp_Dt],
[Create_Dt],
[Insured_NM],
[Service_Center],
[CLocation],
[PLocation],
CASE WHEN ISNUMERIC([State_Plan]) = 0 THEN [State_Plan] ELSE NULL END as State,
CASE WHEN ISNUMERIC([State_Plan]) = 1 THEN [State_Plan] ELSE NULL END as Plan_cd,
[Premium] ,
[Prior_policy],
[INS_UPD_IND]

from TACC_DIV13_IMPORT

for read only

set nocount OFF

SELECT @START_BARCODE = BARCODE FROM SYSACCT

UPDATE SYSACCT SET BARCODE = (BARCODE+1) + (@NEW_REC_CNT+1)

SELECT @master_item_id = item_type_id
FROM item_type
WHERE item_type_desc = 'Policies - SWC Div13'

SELECT @RefEntity_ID = REFENTITY_ID
FROM REFENTITY WHERE ENTITY_REF = 013

SELECT @status_id_active = ltable_id
FROM ltable
WHERE ltype_desc = 'Status'
AND ltable_desc = 'ACTIVE'

SELECT @cl_id = ltable_id
FROM ltable
WHERE ltype_desc = 'Classification'
AND ltable_desc = 'Policy Folder'

SELECT @add_user_no = no
FROM ssecuser
WHERE user_code = 'ETL_PROCES'

SET @current_date_clarion = dbo.fn_GetClarionDate(GETDATE())

open c1

fetch c1 into

@Item_type,
@Classification,
@Policy_number ,
@AISLIC ,
@Tab_no ,
@Eff_DT ,
@Exp_Dt ,
@Create_Dt,
@Insured_NM,
@Service_Center ,
@CLocation,
@PLocation,
@State,
@Plan_cd,
@Premium_Amt,
@Prior_policy_no,
@INS_UPD_FLAG

WHILE @@fetch_status = 0

BEGIN

-- Get ID for Service Center
Select @Service_Center_ID=Office_ID
from Office where Office_DESC=@Service_Center

-- Get ID for Permanent Location
select @PLocation_ID = no
from SSECUSER
where USER_DESC = @PLocation

-- Get ID for Current Location
select @CLocation_ID = no
from SSECUSER
where USER_DESC = @CLocation

IF @INS_UPD_FLAG = 'I'

BEGIN

SET @NEXT_BARCODE = @START_BARCODE + 1
SET @START_BARCODE = @START_BARCODE + 1

select @CLocation_ID = no
from SSECUSER
where USER_DESC = 'Unknown Location'

INSERT INTO item
(
MASTER_ITEM_ID,
MASTER_ITEM_DESC,
BARCODE,
ITEM_REF,
ITEM_DESC,
COPY_NMBR,
DATE_FROM,
DATE_TO,
DATE_CREATION,
DATE_INACTIVE,
CL_ID,
CL_DESCRIPTION,
PLOC_ID,
PERMANENTLOCATION,
CLOC_ID,
CURRENTLOCATION,
OFFICE_ID,
OFFICE_DESC,
STATUS_ID,
STATUS_DESC,
ALLOW_CHECKOUT,
ADDSTAMPFIELD,
ADD_USER_NO,
ORIGINAL_FLAG,
SAVED,
AUTHOR,
ITEM_SUBJECT,
VENDOR_BARCODE

)
VALUES
(
@master_item_id, --MASTER_ITEM_ID
'Policies - SWC Div13', --MASTER_ITEM_DESC
@NEXT_BARCODE, --BARCODE IS THE POLICY NO AS PER BUSINESS REQ
@policy_number, --ITEM_REF
@insured_nm, --ITEM_DESC
@Plan_CD, --COPY_NMBR (As per mail from Steve. Previously vol no was used)
dbo.fn_GetClarionDate(@eff_dt), --DATE_FROM
dbo.fn_GetClarionDate(@exp_dt), --DATE_TO
dbo.fn_GetClarionDate(getdate()), --DATE_CREATION
dbo.fn_GetClarionDate(@Create_Dt), --DATE_INACTIVE
@cl_id, --CL_ID
'Policy Folder', --CL_DESCRIPTION
@PLocation_ID, --PLOC_ID
@PLocation, --PERMANENTLOCATION
@CLocation_ID, --CLOC_ID
'Unknown Location', --CURRENTLOCATION
@Service_Center_ID, --OFFICE_ID
@Service_Center, --OFFICE_DESC
@STATUS_ID_ACTIVE, --STATUS_ID
'ACTIVE', --STATUS_DESC
1, --ALLOW_CHECKOUT
CONVERT(VARCHAR(32),GETDATE(),101) + ' ' + convert(varchar(12),getdate(),108), --ADDSTAMPFIELD
@add_user_no, --ADD_USER_NO
@AISLIC, --ORIGINAL_FLAG
1, --SAVED
@STATE, --AUTHOR
@Premium_Amt, --ITEM_SUBJECT
dbo.DIV13_CHECKDIGIT(@policy_number) --TERTIARY BARCODE
)

END

IF @INS_UPD_FLAG = 'U'

BEGIN

select @UPD_ITEM_ID = item_id from item WHERE ITEM_REF = @policy_number
AND LTRIM(RTRIM(MASTER_ITEM_DESC)) = 'Policies - SWC Div13'

UPDATE ITEM
SET
ITEM_DESC = @insured_nm, --ITEM_DESC
COPY_NMBR = @Plan_CD, --COPY_NMBR
DATE_FROM = dbo.fn_GetClarionDate(@eff_dt), --DATE_FROM
DATE_TO = dbo.fn_GetClarionDate(@exp_dt), --DATE_TO
OFFICE_ID = @Service_Center_ID, --OFFICE_ID
OFFICE_DESC = @Service_Center, --OFFICE_DESC
STATUS_ID = @STATUS_ID_ACTIVE, --STATUS_ID
ORIGINAL_FLAG = @AISLIC, --ORIGINAL_FLAG
AUTHOR = @STATE, --AUTHOR
ITEM_SUBJECT = @Premium_Amt, --ITEM_SUBJECT
EDITSTAMPFIELD = GetDate(),
EDIT_USER_NO = @add_user_no

WHERE ITEM_REF = @policy_number
AND LTRIM(RTRIM(MASTER_ITEM_DESC)) = 'Policies - SWC Div13'

-- UPDATES COMPANY AND DIVISION
UPDATE ITEM_ENTITY
SET REFENTITY_ID = @RefEntity_ID,
ADDSTAMPFIELD = GETDATE(),
ADD_USER_NO = @ADD_USER_NO
WHERE ITEM_ID = @UPD_ITEM_ID

END

fetch c1 into

@Item_type,
@Classification,
@Policy_number ,
@AISLIC ,
@Tab_no ,
@Eff_DT ,
@Exp_Dt ,
@Create_Dt,
@Insured_NM,
@Service_Center ,
@CLocation,
@PLocation,
@State,
@Plan_cd,
@Premium_Amt,
@Prior_policy_no,
@INS_UPD_FLAG

END /** END OF WHILE c1 cursor **/

CLOSE c1
DEALLOCATE c1

SET NOCOUNT OFF

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 12:22:34

you dont require this cursor at all. why cant you think of set based solution?
re. table locks you need to use NOLOCK hint to get table data while its locked for operations, provided you dont mind dirty reads.

Raj_Mahendran
Starting Member

14 Posts

Posted - 2009-01-29 : 12:47:38

Visakh, as stated i made the code simpler to understand.. under the if loop, ihad to insert the values into multiple tables. So had to use cursors. I'm looking for some syntax - instead of locking the whole table, only the required records need to be locked.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 12:52:18

ok.. then see this

http://articles.techrepublic.com.com/5100-10878_11-5181472.html

Raj_Mahendran
Starting Member

14 Posts

Posted - 2009-01-29 : 23:57:30

Visakh, this was very helpful. Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources