May 14, 2023 at 4:20 pm
Hi,
following is code of one of my proc . This proc is updating user balance and creating some rows in tables.This will invoke for only one record. it means single row insert and update.
Problem statement is to make user balance update for concurrent users. as many(even one user can send several request) can hit db, so I used updlock,rowslock to make transaction atomic. but such hints cause locking that leads to deallock.
so how can I improve my code to suppress locks or what method you suggest I should look for possible solution?
ALTER PROCEDURE [dbo].[spCreateNewBalance]
(
@kode_produk VARCHAR(20), --product code
@tujuan VARCHAR(50), --account number
@ResellerId VARCHAR(20),
@pengirim VARCHAR(255),
@TBalance DECIMAL(18,4),
@qty DECIMAL (18,4),
@ref_id VARCHAR(50),
@TBalance_beli DECIMAL(18,4),
@TBalance_beli2 DECIMAL(18,4),
@keterangan VARCHAR(255) --Remarks
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TransactionCodeINT,
@CurrentDateTIme Datetime = getdate(),
@Balance_akhir decimal (18,4) ,
@AvailableBalance decimal (18,4)
Declare @BalanceAkhir table (id decimal (18,4));
BEGIN TRY
BEGIN TRANSACTION mytran;-- start transaction control
--==================================================================
-- checking user’s balance
--==================================================================
SELECT
@AvailableBalance = Balance - Balance_minimal
FROM dbo.reseller WITH(UPDLOCK,ROWLOCK,READPAST)
WHERE kode = @ResellerId ;
-- If insufficient balance , exit
IF @AvailableBalance < @TBalance
BEGIN
SELECT 'Insufficient Balance';
ROLLBACK TRANSACTION mytran;
RETURN
END
--==================================================================
-- Update user’s balance
--==================================================================
UPDATE dbo.reseller --WITH (ROWLOCK)
SET Balance = Balance - @TBalance,
tgl_aktivitas = @CurrentDateTIme,
tgl_data = @CurrentDateTIme
OUTPUT INSERTED.Balance INTO @BalanceAkhir
WHERE kode = @ResellerId ;
SELECT TOP 1
@Balance_akhir=ID
FROM @BalanceAkhir;
--==================================================================
-- Creating the transaction in transaksi table:
--==================================================================
INSERT INTO dbo.Transaction (
columns
)
VALUES ( values
)
-- Getting last inserted identity value
SELECT @TransactionCode= SCOPE_IDENTITY();
--==================================================================
-- Create user’s balance log
--==================================================================
INSERT INTO mutasi (
columns
)
VALUES (
values
);
-- COMMIT IF DML are successfull
COMMIT TRANSACTION mytran;
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRANSACTION mytran;-- if any error occur rollback all
SELECT -1
END
END CATCH
-- output inserted kode
SELECT @TransactionCode
END
May 15, 2023 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply