Please help me - Table Lock - 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

General SQL Server Forums

New to SQL Server Programming

Please help me - Table Lock

Author

Topic

binto
Yak Posting Veteran

59 Posts

Posted - 2010-04-21 : 03:06:14

I have a very complex issue.I was trying to solve the same since 4 days,but could not solve.

I am explaining with my procedure.I am using SQL Server 2008

ALTER PROCEDURE[dbo].[SL_SetSalesBills](
@BranchID tinyint, @YearID tinyint, @ActionFlag bit, @BillNo int, @SalesDate datetime, @EstimationNo int,
@Customer Varchar(30),@SalesPerson1 smallint,@SalesPerson2 smallint, @Address varchar(100),
@LoyaltyCardNo Varchar(30),@BillAmount numeric(15,2),
@DiscountPersentage numeric(15,2),@DiscountAmount numeric(15,2),@CashAmount numeric(15,2),@ModOfPayment tinyint,
@PaymentModeID int, @ReturnNo int, @ReturnAmount decimal(15,2),
@PaymentModeNumber varchar(20), @PaymentModeAmount numeric(15,2), @Authorized smallint,
@CounterNo smallint, @UserID smallint, @SalesValueDetails xml = NULL
)AS
BEGIN
SET NOCOUNT ON;
DECLARE @SalesItems TABLE(
BranchID tinyint,
YearId tinyint,
BillNo int,
SlNo smallint,
TagNo varchar(15),
ItemType varchar(10),
Nos tinyint,
Quantity numeric(10,2),
BillQuantity numeric(10,2),
Amount numeric(10,2),
Rate numeric(10,2),
Discount numeric(10,2),
TaxAmount numeric(10,2),
TaxRate numeric(10,2),
ItemId Smallint,
TaggingNo int,
CessAmount numeric(10,2),
NetAmount numeric(10,2)
)
BEGIN TRY
BEGIN TRAN
DECLARE @Message varchar(255) -- Variable for displaying Message
DECLARE @Status bit -- Variable for displaying result.
DECLARE @Settled bit --for bill settlement
SET @Status = 1
SET @Message = ''
--Store Salesbillitems in a temp table
INSERT INTO @SalesItems(BranchID, YearId, BillNo, SlNo, TagNo, ItemType,Nos, Quantity, Amount,
Rate, BillQuantity, Discount, TaxAmount, TaxRate, CessAmount, NetAmount, ItemId ,TaggingNo)
SELECT @BranchID BranchID, @YearId YearId, @BillNo BillNo
, ParamValues.ID.value('SlNo[1]', 'smallint') AS SlNo
, ParamValues.ID.value('TagNo[1]', 'varchar(15)') AS TagNo
, ParamValues.ID.value('Type[1]', 'varchar(10)') AS ItemType
, ParamValues.ID.value('Nos[1]','tinyint')AS Nos
, ParamValues.ID.value('Quantity[1]','numeric(10,2)')AS Quantity
, ParamValues.ID.value('Amount[1]','numeric(10,2)')AS Amount
, ParamValues.ID.value('Rate[1]','numeric(10,2)')AS Rate
, ParamValues.ID.value('BillQuantity[1]','numeric(10,2)')AS BillQuantity
, ParamValues.ID.value('Discount[1]','numeric(10,2)')AS Discount
, ParamValues.ID.value('TaxAmount[1]','numeric(10,2)')AS TaxAmount
, ParamValues.ID.value('TaxRate[1]','numeric(10,2)')AS TaxRate
, ParamValues.ID.value('CessAmount[1]','numeric(10,2)')AS CessAmount
, ParamValues.ID.value('NetAmount[1]','numeric(10,2)')AS NetAmount
, ParamValues.ID.value('ItemId[1]','smallint')AS ItemId
, ParamValues.ID.value('TaggingNo[1]','int')AS TaggingNo
FROM @SalesValueDetails.nodes('//SalesDetails/SalesItems') AS ParamValues(ID)

IF @ActionFlag = 0 -- 0 for Insert
BEGIN
DECLARE @DuplicateEstimation int
SELECT @DuplicateEstimation=Sbl_BillNo FROM SL_SalesBills WHERE Sbl_BranchID=@BranchID
AND Sbl_YearID=@YearID AND Sbl_EstimationNo=@EstimationNo AND Sbl_Cancelled=0
IF(ISNULL(@DuplicateEstimation,0)>0)
BEGIN
SELECT 0 Status, 'Est No:'+LTRIM(@EstimationNo)+'. already billed!' Message, @BillNo BillNo
RETURN
END
--Get Key value
EXEC GetKeyID @BranchID, 35, @YearId, @BillNo OUTPUT

--insert into Sales Bills
IF @ModOfPayment = 3 -- Credit Customer
SET @Settled=0
ELSE
SET @Settled=1
--inserting into SL_SALESBILLS
INSERT INTO SL_SalesBills (Sbl_BranchID, Sbl_YearID, Sbl_BillNo, Sbl_Date, Sbl_EstimationNo, Sbl_Customer,
Sbl_Address, Sbl_LoyaltyCardNo, Sbl_BillAmount, Sbl_DisPercentage, Sbl_DisAmount, Sbl_CashAmount,
Sbl_PaymentModeID, Sbl_ModeOfPayment, Sbl_ReturnNo, Sbl_ReturnAmount,
Sbl_PaymentModeNumber, Sbl_PaymentModeAmount, Sbl_CounterNo, Sbl_AuthorizedBy, Sbl_UserCreated,
Sbl_DateCreated, Sbl_Settled,Sbl_SalesPerson1,Sbl_SalesPerson2)
VALUES (@BranchID, @YearId, @BillNo, @SalesDate, @EstimationNo, @Customer, @Address, @LoyaltyCardNo, @BillAmount,
@DiscountPersentage, @DiscountAmount, @CashAmount, @PaymentModeID, @ModOfPayment, @ReturnNo, @ReturnAmount, @PaymentModeNumber,
@PaymentModeAmount, @CounterNo, @Authorized, @UserID, GETDATE(),@Settled,@SalesPerson1,@SalesPerson2)

--Update Estmation As billed
UPDATE SL_Estimations SET Ets_Billed = 1 WHERE Ets_BranchId = @BranchID
AND Ets_YearID=@YearID AND Ets_EstimationNo = @EstimationNo

IF( @ModOfPayment = 6)--Sales Advance
BEGIN
UPDATE SL_SalesAdvances SET
Adv_Balance =Adv_Balance - @PaymentModeAmount,Adv_BillDate=@SalesDate
FROM SL_SalesAdvances
INNER JOIN SL_SalesBills ON Adv_AdvanceNo = Sbl_PaymentModeId
AND Sbl_BranchID = @BranchID AND Sbl_YearID = @YearID
AND SL_SalesBills.Sbl_BillNo=@BillNo AND Adv_BranchID=@BranchID
END

--Insert into sales bill items
INSERT INTO SL_SalesBillItems (Sbi_BranchID, Sbi_YearID, Sbi_BillNo, Sbi_SlNo, Sbi_TagNo,
Sbi_Type, Sbi_Nos, Sbi_Qty, Sbi_BillQty, Sbi_Rate, Sbi_Amount, Sbi_Discount, Sbi_TaxRate,
Sbi_TaxAmount, Sbi_CessAmount, Sbi_NetAmount, Sbi_ItemID, Sbi_TaggingNo)
SELECT BranchID, YearId, @BillNo, SlNo, TagNo, ItemType, Nos, Quantity, BillQuantity,
Rate, Amount, Discount, TaxRate, TaxAmount, CessAmount, NetAmount, ItemId, TaggingNo
FROM @SalesItems

--Update each item as billed
UPDATE IN_TaggingAndPricingItemDetails SET Tpi_Billed = 1
FROM IN_TaggingAndPricingItemDetails
INNER JOIN @SalesItems ON Tpi_BranchID = @BranchID
AND Tpi_TagNo = TagNo AND Tpi_Estimated=1

SELECT @Message = DBO.TIMS_GetErrorMessage(2, 'Bill No ', @BillNo)
END
ELSE -- updation
BEGIN
--reset the Amount based on mode of payment
DECLARE @PaymentMode tinyInt;
DECLARE @ModePaymentNumber varchar(20)
SELECT @PaymentMode = Sbl_ModeOfPayment, @ModePaymentNumber = Sbl_PaymentModeNumber
FROM SL_SalesBills WHERE Sbl_BranchID = @BranchID AND Sbl_YearID=@YearID
AND SL_SalesBills.Sbl_BillNo = @BillNo

IF( @PaymentMode = 6)--Sales Advance
BEGIN
UPDATE SL_SalesAdvances SET
Adv_Balance = ISNULL(Adv_Balance,0) + Sbl_PaymentModeAmount,Adv_BillDate = null
FROM SL_SalesAdvances
INNER JOIN SL_SalesBills ON Adv_AdvanceNo = Sbl_PaymentModeId
AND Sbl_BranchID = @BranchID AND Sbl_YearID=@YearID
AND Adv_BranchID=Sbl_BranchID AND SL_SalesBills.Sbl_BillNo=@BillNo
END

--updating Sales bills header details
UPDATE SL_SALESBILLS SET Sbl_BranchID = @BranchID, Sbl_YearID = @YearId, Sbl_BillNo = @BillNo,
Sbl_Date=@SalesDate, Sbl_EstimationNo = @EstimationNo, Sbl_Customer = @Customer, Sbl_Address = @Address,
Sbl_LoyaltyCardNo=@LoyaltyCardNo, Sbl_BillAmount = @BillAmount, Sbl_DisPercentage = @DiscountPersentage,
Sbl_DisAmount = @DiscountAmount, Sbl_CashAmount = @CashAmount, Sbl_PaymentModeID = @PaymentModeID,
Sbl_ModeOfPayment = @ModOfPayment, Sbl_PaymentModeNumber = @PaymentModeNumber, Sbl_ReturnNo = @ReturnNo,
Sbl_ReturnAmount = @ReturnAmount, Sbl_PaymentModeAmount = @PaymentModeAmount, SBl_Locked = 1,
Sbl_CounterNo = @CounterNo, Sbl_AuthorizedBy = @Authorized, Sbl_UserModified=@UserID,Sbl_DateModified=GETDATE(), SBl_Cancelled = 0
WHERE Sbl_BranchID=@BranchID AND Sbl_YearID=@YearID AND Sbl_BillNo=@BillNo

--Delete all the Sales Bill items
DELETE FROM SL_SalesBillItems WHERE Sbi_BranchID=@BranchId AND Sbi_BillNo=@BillNo AND Sbi_YearID=@YearID

--insert Sales bill items details
INSERT INTO SL_SalesBillItems (Sbi_BranchID,Sbi_YearID,Sbi_BillNo,Sbi_SlNo,Sbi_TagNo,
Sbi_Type,Sbi_Nos,Sbi_Qty,Sbi_BillQty,Sbi_Rate,Sbi_Amount,Sbi_Discount,Sbi_TaxRate,
Sbi_TaxAmount,Sbi_CessAmount,Sbi_NetAmount,Sbi_ItemID,Sbi_TaggingNo)
SELECT BranchID,YearId,@BillNo,SlNo,TagNo,ItemType,
Nos,Quantity,BillQuantity,Rate,Amount,Discount,TaxRate,TaxAmount,CessAmount,NetAmount ,ItemId,TaggingNo
FROM @SalesItems

-- update each items as billed
UPDATE IN_TaggingAndPricingItemDetails SET Tpi_Billed = 1
FROM IN_TaggingAndPricingItemDetails
INNER JOIN @SalesItems ON Tpi_BranchID = @BranchID AND Tpi_Estimated=1
AND Tpi_TagNo = TagNo

IF( @ModOfPayment = 6)--Sales Advance
BEGIN
UPDATE SL_SalesAdvances
SET Adv_Balance =Adv_Balance - @PaymentModeAmount,Adv_BillDate=@SalesDate
FROM SL_SalesAdvances
INNER JOIN SL_SalesBills ON Sbl_BranchID=@BranchID
AND Sbl_YearID = @YearID AND Adv_AdvanceNo = Sbl_PaymentModeId
AND Adv_BranchID=Sbl_BranchID AND SL_SalesBills.Sbl_BillNo = @BillNo
END
SELECT @Message = DBO.TIMS_GetErrorMessage(3, 'Bill No', @BillNo)
END
COMMIT TRAN
SELECT @Status Status, @Message Message, @BillNo BillNo
END TRY
BEGIN CATCH
ROLLBACK
SELECT 0 Status, DBO.TIMS_GetErrorMessage(26, 'Bill', '') Message, @BillNo BillNo
END CATCH
SET NOCOUNT OFF;
END

In this query insertion/updation process may happen.
Table weight

SL_SalesBills --- 4.5m - Insertion
SL_SalesBillItems --- 14m - Insertion
SL_Estimations ---- 5m - Updation
IN_TaggingAndPricingItemDetails --- 12m - Updation

Insertion is going from 6 users through this procedure simultaneousely.sometimes 2 different users was trying to acess it in same time.

I am using procedure EXEC GetKeyID @BranchID, 35, @YearId, @BillNo OUTPUT for getting unique bill no.

Two days before,I found SL_SalesBills locked when trying to insert data.After that time no users can access the table due to this lock.
I used sp_lock and seen SalesBills locked.I killed the process
by KILL(SPID).SPID-got from SP_LOCK

How can I avoid this locking.Is there any solution.Please help me

Thanks & Regards
Binto Thomas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources