I am experiencing some performance issues with locks/deadlocks in a very simple statement executed by hundreds of executions in parallel each second...
Basically we have two tables: counter( id, category_id, name, date, counter ) and category( category_id, name )
And each click on our web-site (using asp, asp.net and IIS), should/must be logged into the DB. Some home-pages can log several counters at once, usually at different categories. Counters can be daily, weekly, monthly or whatever.
We have 1 database server: 64bit, 4 QuadCore processors, 64Gb ram, running sql server 2005 on windows 2003, and 16 IIS version 6.0 (virtual) Servers: 4Gb each.
Except for this bottleneck, the system runs pretty well, under 75% of the hardware capacity ( this include disk, net, cpu and memory )
Todays implementation can be simplified to something like:
create table category(
category_id int not null primary key identity(1,1)
, name nvarchar(255) not null
)
go
create table [counter] (
id int not null primary key identity(1,1)
, category_id int not null references category(category_id)
, name nvarchar(255) not null
, [date] date null
, [counter] bigint not null default 1
)
go
CREATE PROCEDURE pr_update_counter ( @category_name nvarchar(255), @counter_name nvarchar(255), @date date = null, @inc int)
AS BEGIN
DECLARE @category_id int
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT @category_id = category_id FROM category WHERE name = @category_name
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO category(name) values ( @category_name )
SELECT @category_id = scope_Identity()
END
UPDATE [counter] SET counter = counter + @inc
WHERE category_id = @category_id AND name = @counter_name AND (@date is null or [date] = @date)
IF @@ROWCOUNT = 0
INSERT counter(category_id, name, [date]) values ( @category_id, @counter_name, @date )
COMMIT
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
END
GO
Execution examples:
exec pr_update_counter @category_name = 'main', @counter_name = 'home', @date = null, @inc = 1
Some extra information:
High execution concurrency: 500+ executions/second ( 5K exec/sec peak )
under lower isolation level, we missed some "counts", meaning we had "ghost reads" and probably "ghost updates".
under repeatable read or serializable isolation levels some resources lock and sometimes deadlock themselfs.
Locks/Deadlocks and timeouts make the web app slower then it should be, and we also miss some "counts" ( those that timedout or was deadlocked ).
How to reproduce the error:
Execute hundreds/thousands of times the procedure, preferably at the same exact time and check if "N" executions increased the counter by exact "N".
Ex: Create a RUN.BAT file with the following code ( change the execution time to a near future to avoid locking connctions on the server for some time ):
FOR /L %%i in (1,1,500) do start /b osql -S -U -P -q"waitfor time 'hh:mm:ss'; exec pr_update_counter @category_name = 'main' , @counter_name = 'home' , @date = null , @inc = 1 " < EXIT.TXT EXIT
Further create a EXIT.TXT file with a single line of the code "EXIT" ( this was necessarry to close each of the 'cmd' started individually ).
Questions:
How to avoid deadlocks and "ghost reads" without slowing down the application neither losing "counts" ?
Is there a way to enhance the code, making it faster and more reliable ?
Does Snapshot Isolation, Table Split/Partition or Service Broker could help me solve this issue ?
Does IIS or any asp/asp.net DLL can perform this counting better than SQL Server ?
Does Try Catch can capture and "correct" deadlocks, or using Try Catch can help me in any way ?
The system have indexes optimized for this query, but what could be the ideal index to use in this scenario ?
We cant use Google Analytics because some client's firewall could be blocking it.
Thanks.
Edit:
Please comment this changes to the code. I priorized the update, since it happens a lot more than inserts, included TRY/CATCH to avoid losing "counts". But i still have the problem that the application must wait too long ( sometimes ) for this execution to finish.
CREATE Procedure [dbo].pr_update_counter2
(@category_name nvarchar(255), @counter_name nvarchar(255), @date date = null, @inc int)
As
SET NOCOUNT ON; SET XACT_ABORT ON;
WHILE ( @@TRANCOUNT > 0 ) BEGIN
WAITFOR DELAY '00:00:01' COMMIT
END
SET DEADLOCK_PRIORITY LOW -- <-- not so sure about this
DECLARE @counter_id INT, @category_id INT
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRY
BEGIN TRAN T1
UPDATE [counter] set [counter] = [counter] + @Inc
, @counter_id = [counter].id
, @category_id = [category].category_id
FROM [counter]
LEFT JOIN category
on [counter].category_id = category.category_id
AND category.name = @category_name
WHERE isnull([counter].date,'19000101') = isnull(@date,'19000101')
AND [counter].name = @counter_name
COMMIT TRAN T1
END TRY
BEGIN CATCH
ROLLBACK TRAN T1
EXEC dbo.pr_update_counter2 @category_name, @counter_name, @date, @inc
RETURN
END CATCH
IF ( @category_id IS NOT NULL AND @counter_id IS NOT NULL)
RETURN
IF ( @category_id IS NULL AND @counter_id IS NULL)
BEGIN
BEGIN TRY
BEGIN TRAN T2
INSERT INTO [category](name)
VALUES (@category_name);
SET @category_id = SCOPE_IDENTITY();
INSERT INTO [counter] (category_id, name, [date], [counter])
VALUES (@category_id, @counter_name, @date, @inc);
COMMIT TRAN T2
END TRY
BEGIN CATCH
ROLLBACK TRAN T2
EXEC pr_update_counter2 @category_name, @counter_name, @date, @inc
END CATCH
RETURN
END
IF ( @counter_id IS NULL AND @category_id IS NOT NULL )
BEGIN
BEGIN TRY
BEGIN TRAN T3
INSERT INTO [counter] (category_id, name, [date], [counter])
VALUES (@category_id, @counter_name, @date, @inc);
COMMIT TRAN T3
END TRY
BEGIN CATCH
ROLLBACK TRAN T3
EXEC pr_update_counter2 @category_name, @counter_name, @date, @inc
END CATCH
RETURN
END
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
RETURN;
