Concurrent execution in SQL Server

Question

Table schemas (SQL Server 2012)

Create Table InterestBuffer
(
    AccountNo CHAR(17) PRIMARY KEY,
    CalculatedInterest MONEY,
    ProvisionedInterest MONEY,
    AccomodatedInterest MONEY,
)

Create Table #tempInterestCalc
(
    AccountNo CHAR(17) PRIMARY KEY,
    CalculatedInterest MONEY
)

I am doing an upsert. Update rows those existed and insert others.

UPDATE A
SET A.CalculatedInterest = A.CalculatedInterest + B.CalculatedInterest
FROM InterestBuffer A
INNER JOIN #tempInterestCalc B ON A.AccountNo = B.AccountNo

INSERT INTO InterestBuffer
SELECT A.AccountNo, A.CalculatedInterest, 0, 0
FROM #tempInterestCalc A
LEFT JOIN InterestBuffer B ON A.AccountNo = B.AccountNo
WHERE B.AccountNo IS NULL

All is working fine. Problem occurs during concurrent executions. I am inserting data into #tempInterestCalc by joining other various tables including a left join with the InterestBuffer table and different set of data is inserted into #tempInterestCalc for each concurrent execution.

My problem is that sometimes executions become locked by another execution until I commit them in serial.

My question is as I am providing different set of data then it should not have any impact of row lock over other concurrent operation. Any suggestion will be appreciated.

UPDATE 1: I have used SP_LOCK for InterestBuffer table. It says IndId = 1, Type = KEY, Mode = X, Status = GRANT.

I think the update and insert blocks other transaction to make phantom reads.

UPDATE 2: Sorry! Previously I told that update is fine. But now I realized that first Transaction write is blocking second transactions write. In first transaction I run the update and insert. In second transaction, after I insert data in #tempInterestCalc table I just do as following and its just worked fine.

--INSERT DATA INTO #tempInterestCalc 

SELECT * FROM #tempInterestCalc 
RETURN

--UPDATE InterestBuffer

--INSERT InterestBuffer

UPDATE 3: I think my problem is to read data from InterestBuffer during update and insert into InterestBuffer.

UPDATE 4: My answer below is working sometimes if I REBUILD INDEX of BranchCode in InterestBuffer table. Is there any reason that batch insert/update make problem with index ???

UPDATE 5: I have read that if maximum rows of a page needs to be locked for batch update then SQL server may locked that page. Is there any way to see which row is containing by which page or which page is going to lock and release during execution??

UPDATE 6: I am providing my scenario.

CREATE TABLE [dbo].[Account](
        [AccountNo] [char](17) NOT NULL,
        [BranchCode] [char](4) NOT NULL,
     CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED 
    (
        [AccountNo] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

CREATE TABLE [dbo].[InterestBuffer](
    [AccountNo] [char](17) NOT NULL,
    [BranchCode] [char](4) NOT NULL,
    [CalculatedInterest] [money] NOT NULL,
 CONSTRAINT [PK_Buffer] PRIMARY KEY CLUSTERED 
(
    [AccountNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Query for Branch 0001:

BEGIN TRAN

Declare @BranchCode AS Char(4) = '0001'
Declare @CalculatedInterestNew MONEY = 10

CREATE TABLE #tempInterestCalc
(
    AccountNo Char(17),
    BranchCode Char(4),
    CalculatedInterestNew MONEY,
    CalculatedInterestOld MONEY
)

INSERT INTO #tempInterestCalc
SELECT A.AccountNo, A.BranchCode, ISNULL(B.CalculatedInterest, 0), B.CalculatedInterest
FROM Account A 
LEFT JOIN InterestBuffer B ON A.AccountNo = B.AccountNo AND A.BranchCode = B.BranchCode
WHERE A.BranchCode = @BranchCode

UPDATE A
SET A.CalculatedInterest = B.CalculatedInterestNew + @CalculatedInterestNew
FROM InterestBuffer A
INNER JOIN #tempInterestCalc B ON A.AccountNo = B.AccountNo AND A.BranchCode = B.BranchCode
WHERE A.BranchCode = @BranchCode

INSERT INTO InterestBuffer
SELECT A.AccountNo, A.BranchCode, A.CalculatedInterestNew + @CalculatedInterestNew
FROM #tempInterestCalc A
WHERE A.CalculatedInterestOld IS NULL

DROP TABLE #tempInterestCalc
--ROLLBACK
--COMMIT TRAN

For Branch 0002, 0003 just change the @BranchCode variable value to 0002 &0003 and Run them simultaneously.

Answer 1

You could have a potential deadlock problem because you are doing another read against the InterestBuffer table after a write. A transaction could deadlock if another has blocked part of the InterestBuffer table for the update and your transaction is trying to read from it again for the select needed to do the insert.

You said you are already left joining with InterestBuffer while calculating your #tempInterestCalc table... why not use it to cache some of the data needed from InterestBuffer so you don't have to read from it again?

Change your temp table to:

Create Table #tempInterestCalc
(
    AccountNo CHAR(17) PRIMARY KEY,
    CalculatedInterestNew MONEY,
    CalculatedInterestOld MONEY
)

You might possibly want to set repeatable read isolation level before beginning your transaction with:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

It's more restrictive locking, but will prevent other transactions from trying to process the same records at the same time, which you probably need because you are combining the old and new values. Consider this scenario:

• Transaction 1 reads data and wants to add 0.03 to existing CalculatedInterest of 5.0.
• Transaction 2 reads data and wants to add 0.02 to the 5.0.
• Transaction 1 updates CalculatedInterest to 5.03.
• Transaction 2's update overwrites the values from transaction one to 5.03 (instead of adding to it and coming up with 5.05).

Maybe you don't need this if your sure that transactions will never be touching the same records, but if so read committed won't let transaction 2 read the values until transaction 1 is finished with it.

Then separate your transaction to a distinct read phase first and then a write phase:

--insert data into #tempInterestCalc and include the previous interest value
insert into #tempInterestCalc
select AccountNo, 
    Query.CalculatedInterest CalculatedInterestNew, 
    InterestBuffer.CalculatedInterest CalculatedInterestOLD
from 
    (
    ...
    ) Query
left join InterestBuffer
on Query.AccountNo = InterestBuffer.AccountNo

UPDATE A
SET A.CalculatedInterest = B.CalculatedInterestNew + B.CalculatedInterestOld
FROM InterestBuffer A
INNER JOIN #tempInterestCalc B ON A.AccountNo = B.AccountNo

INSERT INTO InterestBuffer
SELECT A.AccountNo, A.CalculatedInterestNew, 0, 0
FROM #tempInterestCalc A
--no join here needed now to read from InterestBuffer
WHERE CalculatedInterestOld is null

This shouldn't deadlock... but you could see "unnecessary" blocking due to Lock Escalation, particularly if you are updating a large number of rows. Once there are more than 5000 locks on a table it will escalate to a table. No other transactions will then be able to continue until the transaction completes. This isn't necessarily a bad thing... you just want to make sure that your transactions are as short as possible so as to not lock other transactions for too long. If lock escalation is causing you problems, there are some things you can do to mitigate this such as:

• Breaking your transaction up to do smaller chunks of work so as to create fewer locks.
• Ensuring you have an efficient query plan.
• Making judicious use of lock hints.

Check your query plan and see if there are any table scan's of InterestBuffer in any statements... particularly with your initial population of #tempInterestCalc since you didn't show how you are building that.

If you will absolutely never be updating accounts in one branch at the same time, then you might consider keeping your primary key the same but changing your clustered index to Branch, Account number (order is significant). This will keep all your records of the same branch physically next to each other and will reduce the chance that your plan will do a table scan or lock pages that other transactions might need. You then can also use the PAGLOCK hints, which will encourage SQL Server to lock by page instead of row and prevent reaching the threshold to trigger lock escalation. To do this, modifying your code from UPDATE 6 in your question would look something like this:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN

Declare @BranchCode AS Char(4) = '0001'
Declare @CalculatedInterestNew MONEY = 10

CREATE TABLE #tempInterestCalc
(
    AccountNo Char(17),
    BranchCode Char(4),
    CalculatedInterestNew MONEY,
    CalculatedInterestOld MONEY
)

INSERT INTO #tempInterestCalc
SELECT A.AccountNo, A.BranchCode, ISNULL(B.CalculatedInterest, 0), B.CalculatedInterest
FROM Account A
LEFT JOIN InterestBuffer B
ON A.AccountNo = B.AccountNo AND A.BranchCode = B.BranchCode
WHERE A.BranchCode = @BranchCode

UPDATE A WITH (PAGLOCK)
SET A.CalculatedInterest = B.CalculatedInterestNew + @CalculatedInterestNew
FROM InterestBuffer A
INNER JOIN #tempInterestCalc B ON A.AccountNo = B.AccountNo AND A.BranchCode = B.BranchCode
WHERE A.BranchCode = @BranchCode

INSERT INTO InterestBuffer WITH (PAGLOCK)
SELECT A.AccountNo, A.BranchCode, A.CalculatedInterestNew + @CalculatedInterestNew
FROM #tempInterestCalc A
WHERE A.CalculatedInterestOld IS NULL

DROP TABLE #tempInterestCalc
--ROLLBACK
--COMMIT TRAN

Because the records are physically sorted together this should only lock a few pages... even when updating thousands of records. You could then run a transaction for branch 0003 at the same time as 0001 without any blocking issues. However you will probably have a blocking problem if you try to do an adjacent branch such as 0002 at the same time. This is because some records from branch 0001 and 0002 will probably share the same page.

If you really need to separate your branches you could look into using a Partitioned Table or Index. I don't know much about them, but it sounds like it might be useful to what you are trying to do, but it also probably comes with it's own set of complications.