Transaction deadlock on inserting into a table

[fniles]

Thank you.
Does this Stored procedure look ok ?

DECLARE @Success int, @TryNr int, @ErrMsg varchar(500), @ErrNumber
int, @expected_id int;
SET @Success = 0;
SET @TryNr = 1;
SET @ErrMsg = ''
WHILE @Success = 0 AND @TryNr <= 3 and @ErrMsg = ''
BEGIN;
BEGIN TRY; BEGIN TRANSACTION;
retry:
BEGIN TRY;
SELECT @expected_id = ident_current('HistTradesOrig') + 1
SELECT @order = @account + '-' + CAST(@expected_id AS varchar(50))
insert into HistTradesOrig
([Floor],[Order],...)
values
(@Floor,@Order...)
END TRY
BEGIN CATCH;
IF error_message() = 2627 GOTO retry
ELSE
BEGIN;
SET @ErrMsg = ERROR_MESSAGE();
RAISERROR (@ErrMsg, 16, 1);
END;
END CATCH

select @ID = SCOPE_IDENTITY()
IF @id <> @expected_id
BEGIN
UPDATE HistTradesOrig
SET [Order] = @ACCOUNT + '-' + CAST(@ID AS varchar(50))
WHERE ID = @ID
END
SET @Success = 1
COMMIT TRANSACTION;
END TRY
BEGIN CATCH;
ROLLBACK TRANSACTION;
SELECT @ErrNumber = ERROR_NUMBER()
IF @ErrNumber = 1205
BEGIN;
SET @TryNr = @TryNr + 1;
IF @TryNr > 3
RAISERROR ('Giving up after 3 consecutive deadlocks for %s',
16, 1,@ACCOUNT);
END;
ELSE
BEGIN;
SET @ErrMsg = ERROR_MESSAGE();
RAISERROR (@ErrMsg, 16, 1);
END;
END CATCH;
END;

Thank you again for your reply.

If some of the data in the other columns violates another UNIQUE constraint, then it will most certainly go on forever.

If there is only 1 Primary Key in the table (which is currently
[Order]), then does it mean that I don't have to worry about it go on
forever ?
So I don't need to limit the retry to a maximum number of times ?

Hi fniles,

As far as I see it, this is correct.

Is CAST a better/faster way to use than CONVERT ?

CAST is ANSI-compliant syntax; reason for me to use it when possible.
But CONVERT has the style parameter that gives more control than CASE
gives you - so when I need to have more control, I use CONVERT.

 

[Hugo Kornelis]

Thank you.
Does this Stored procedure look ok ?

Based on visual inspection, I'd say yes.
You'll need to do the testing, though. Be sure to include a test with
two or more connections repeatedly calling the stored procedure at
high speed, to simulate heavy concurrency.

 

[Hugo Kornelis]

Hi fniles,

Lots of questions. Here is some specific info to answer most of the
questions.

1. You don't reorganize or rebuild tables or views, but indexes.

2. To learn more about the various options, check the documentation on
ALTER INDEX in Books Online.

3. The frequency of index maintenance depends on how frequent your
data changes. In a mostly stale database, you hardly ever need to
rebuild or reorganize your indexes.

4. For the reorganize/rebuild question, the common advise is to
rebuild when fragmentation exceeds 30%, reorganize when it exceeds
10%. You can find the current fragmentation by querying the dynamic
management function sys.dm_db_index_physical_stats.
(And if you check the documentation on this function in Books Online,
you'll also find a script you can use in a scheduled job to reorganize
or rebuild all indexes in a database, based on current fragmentation).

 

[fniles]

Sorry for the many questions, and thanks again.

But they will become less frequent the size of an index is less than the size of the table, so the number of pages is lower; this automatically decreases the number >of page splits.

So, if I make [ID] as the Primary Key, the # of page split will be
fewer than if [Order] is the Primary Key ?

1. You don't reorganize or rebuild tables or views, but indexes.

Yes, but when I go to "Rebuild Index Task" (under Maintenance Plan
tasks), under "Object" we need to select "Table" or "View" or "Tables
and Views".
In this case, shall I select "Table" ?

I execute the query rebuilding
ALTER INDEX [IX_HistTradesOrig] ON [dbo].[HistTradesOrig] REBUILD WITH
( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS =
ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY =
OFF, ONLINE = ON )
but the results afterward look the same as before

This query returns the same result before and after rebuilding the
index above
SELECT a.index_id, name,
avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats (DB_ID(N'DeskDemo'),
OBJECT_ID(N'HisttradesOrig'), NULL, NULL, NULL) AS a JOIN sys.indexes
AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
index_id name
avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages
1 PK__HistTradesOrig__52593CB8 68.8564476885645
289 1.42214532871972
2 IX_HistTradesOrig
33.3333333333333 12 2.25

Did I do something wrong ?

 

[fniles]

I tested it with 3 different clients sending hundreds of orders each.
I got this error twice, and then I couldn't duplicate it anymore.

Conversion failed when converting the nvarchar value 'Cannot insert
duplicate key row in object 'dbo.HistTradesOrig' with unique index
'IX_HistTradesOrig_1'.' to data type int.

If the error is trying to insert duplicate key, shouldn't it go to
this section of code ?
IF error_message() = 2627 GOTO retry

Can I include more info on the error like this ?
BEGIN;
SET @ErrMsg = '1.' + ERROR_MESSAGE() + ' ' + @order
RAISERROR (@ErrMsg, 16, 1);
END;

Thank you

 

[fniles]

What are the cases where the order is deduced in a different way? And how is it deduced in those cases?
There is another case where [ORDER] is supplied by the VB.NET program
to the stored procedure (a different stored procedure) and it will be
[ORDER] = {Account} & "-" & Date.Now.ToString("MMddHHmmss") &
Date.Now.Millisecond & {a counter from 1 to 99}

In this case the stored procedure only has 1 line (INSERT line):
CREATE PROCEDURE [dbo].[INSERT_INTO_HistTradesOrig_WithOrder]
@Floor varchar(50) = NULL,
@Order varchar(50) = NULL,
:
as
insert into HistTradesOrig
([Floor],[Order],,...)
values
(@Floor,@Order,...)

Erland mentioned this in the earlier posting:

But your UPDATE is changing the clustered index, which means that the entire row will be moved. This means then you first insert a row in one place, you
can cause a page split (unless @order is always increasing), and then you will cause another page split when you move the row.

Does it mean that eventually the database will be slow because of all
the page split ?
If I now change [Order] to be NON Clustered Index and [ID] as Primary
key, will the created page split still be there ?

Hi fniles,

Page splits incur a little overhead while inserting or updating the
data, and they cause indexes to become fragmented over time, which
decreases read performance.

Your proposed change will not completely eliminate the page splits;
the nonclustered index on [Order] still will suffer them. But they
will become less frequent  the size of an index is less than the size
of the table, so the number of pages is lower; this automatically
decreases the number of page splits.

In any case you'll have to schedule index maintenance; if you
reorganize or rebuild your index at regular intervals, the amount of
fragmentation will remain controlled.

Regarding Hugo's suggestion of creating the order numbers without
using IDENTITY, is this what you are referring to ?
CREATE TABLE dbo.HistTradesOrig
   (ID int IDENTITY(1,1) NOT NULL,
    Account varchar(10) NOT NULL,
    OrderNo int NOT NULL,
    order AS Account + CAST(OrderNo AS char(6)),
--   Other columns,
    PRIMARY KEY (ID),
    UNIQUE (Account, OrderNo)
   );

The problem with that is although in most cases order AS Account +
CAST(OrderNo AS char(6)), but in some cases [Order] is not Account +
CAST(OrderNo AS char(6)).

Yes, that was what I was referring to. What are the cases where the
order is deduced in a different way? And how is it deduced in those
cases?

 

[Erland Sommarskog]

I tested it with 3 different clients sending hundreds of orders each.
I got this error twice, and then I couldn't duplicate it anymore.

Conversion failed when converting the nvarchar value 'Cannot insert
duplicate key row in object 'dbo.HistTradesOrig' with unique index
'IX_HistTradesOrig_1'.' to data type int.

This means that you try to use an error message as a number.

If the error is trying to insert duplicate key, shouldn't it go to
this section of code ?
IF error_message() = 2627 GOTO retry

And sure enough. I tell you what: while that was surely was a slip on my
part, my idea of helping people on newsgroups is not to spoonfeed them,
but to encourage people to learn new things. So if you something in a
post you have not seen before, look it up in Books Online. Then you
would have realised that you should not take everything you read here
at face value.

And, yes, you need to test.



--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

 

[Erland Sommarskog]

So, if I make [ID] as the Primary Key, the # of page split will be
fewer than if [Order] is the Primary Key ?

No. But if you make the index on ID clustered, rather than the index on
Order, you will get fewer page splits.

Will database backup reorganize/rebuild indexes ?
No.

Can I reorganize or rebuild indexes during weekdays when records are
inserted ? Or, shall I do it in the weekends, when there is no records
inserted ?
How often do you suggest to reorganize or rebuild indexes ?

As often that is needed. Which may be very often with tables that gets
fragmented quickly. And very rarely for tables that are not touched, or
for tables with monotically increasing keys.

It is not uncommon to rebuild everything during the weekends, but
it can cause unnecessary log bloat. Better is to rebuild smarter. See
http://ola.hallengren.com/ for tools for this.



--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

 

[Erland Sommarskog]

Yes, but when I go to "Rebuild Index Task" (under Maintenance Plan
tasks), under "Object" we need to select "Table" or "View" or "Tables
and Views".
In this case, shall I select "Table" ?

Do you have any indexed views?

This query returns the same result before and after rebuilding the
index above

How many rows are there in the table?



--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

 

[fniles]

Ah, Yes, you are correct, my mistake.
I have changed the code to be like this:
retry:
BEGIN TRY;
SELECT @expected_id = ident_current('HistTradesOrig') + 1
SELECT @order = @account + '-' + CAST(@expected_id AS varchar(50))
insert into HistTradesOrig
([Floor],[Order],,...)
values
(@Floor,@Order,...)
END TRY
BEGIN CATCH;
SELECT @ErrNumber = ERROR_NUMBER()
IF @ErrNumber = 2627
BEGIN;
GOTO retry
END;
ELSE
BEGIN;
SET @ErrMsg = '1.' + ERROR_MESSAGE() + ' ' + @order
RAISERROR (@ErrMsg, 16, 1);
END;
END CATCH

 

[fniles]

So, if I make [ID] as the Primary Key, the # of page split will be fewer than if [Order] is the Primary Key ?

No. But if you make the index on ID clustered, rather than the index on Order, you will get fewer page splits.

So, leave the Primary key as Order, but make Order's primary key index
as NON Clustered and make ID index as clustered, is that correct ?

So, if I make [ID] as the Primary Key, the # of page split will be
fewer than if [Order] is the Primary Key ?

No. But if you make the index on ID clustered, rather than the index on
Order, you will get fewer page splits.

Will database backup reorganize/rebuild indexes ?
No.

Can I reorganize or rebuild indexes during weekdays when records are
inserted ? Or, shall I do it in the weekends, when there is no records
inserted ?
How often do you suggest to reorganize or rebuild indexes ?

As often that is needed. Which may be very often with tables that gets
fragmented quickly. And very rarely for tables that are not touched, or
for tables with monotically increasing keys.

It is not uncommon to rebuild everything during the weekends, but
it can cause unnecessary log bloat. Better is to rebuild smarter. Seehttp://ola.hallengren.com/for tools for this.

--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

 

[fniles]

Do you have any indexed views?
I have views, but they are not indexed

How many rows are there in the table?

20,557 rows

 

[Erland Sommarskog]

So, leave the Primary key as Order, but make Order's primary key index
as NON Clustered and make ID index as clustered, is that correct ?

No, I did not say that. I only pointed out that just because you make
something a primary key, does not have anything to do with fragmentation.

A primary key is a *logical concept*. Clustered/non-clustered is a
*physical concept*.

As it happens, I would make ID the PK as well, as I would use it as FK
in other tables. But that's strictly an esthetic question. You can make
FKs to UNIQUE constraints as well.




--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

 

[Erland Sommarskog]

20,557 rows

In that case, I would expect fragmentation to decrease. I cannot say
why that did not happen.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

 

[Hugo Kornelis]

Yes, but when I go to "Rebuild Index Task" (under Maintenance Plan
tasks), under "Object" we need to select "Table" or "View" or "Tables
and Views".
In this case, shall I select "Table" ?

Hi fniles,

I never used that wizard (I used the SQL code found in BOL, slightly
modified, in a T-SQL task in the maintenance plan). But my guess is
that the "views" choice selects indexes on views. If you don't have
any indexed views, then "Tables and Views" is the same as "Tables",
and "Views" does nothing but waste a few CPU cycles.

I execute the query rebuilding
ALTER INDEX [IX_HistTradesOrig] ON [dbo].[HistTradesOrig] REBUILD WITH
( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS =
ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY =
OFF, ONLINE = ON )
but the results afterward look the same as before

This query returns the same result before and after rebuilding the
index above
SELECT a.index_id, name,
avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats (DB_ID(N'DeskDemo'),
OBJECT_ID(N'HisttradesOrig'), NULL, NULL, NULL) AS a JOIN sys.indexes
AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
index_id name
avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages
1 PK__HistTradesOrig__52593CB8 68.8564476885645
289 1.42214532871972
2 IX_HistTradesOrig
33.3333333333333 12 2.25

Did I do something wrong ?

I don't see anything to explain why nothing changed. Except for the
change between uppercase and lowercase T in HistTradesOrg /
HistttradesOrg - if your database has a case sensitive collation, that
could refer to two different tables. Also, did you execute the ALTER
INDEX in the correct database, and did it finish without errors? (As
you see, I'm clutching at straws).

I'm also surprised to see two indexes - based on the table design you
posted earlier in this topic, I had expected to see one. Or did you
already implement some of the recommendations? Anyway, for the index
that is based on the order column, which will have out-of-order
inserts, I recommend specifying the FILLFACTOR option as well. This
reserves some free space on each page; this increases the size of the
index, but reduces the amount of page splits. The best value to use is
hard to say without knowing anything about frequency of data
modifications and index maintenance, but you might want to start with
a fill factor 75 or so.

Finally, why rebuild the nonclustered index with a fragmentation of
33%, but leave the much more heavily fragmented (69%) clustered index
untouched?

 

[Hugo Kornelis]

What are the cases where the order is deduced in a different way? And how is it deduced in those cases?

There is another case where [ORDER] is supplied by the VB.NET program
to the stored procedure (a different stored procedure) and it will be
[ORDER] = {Account} & "-" & Date.Now.ToString("MMddHHmmss") &
Date.Now.Millisecond & {a counter from 1 to 99}

(snip)

Hi fniles,

Hmmm, tricky design. Why do users always come up with those silly
requirements, eh?

I'm not sure if it's really wise, but you can still do this with a
variation of the idea I gave earlier:

CREATE TABLE dbo.HistTradesOrig
(ID int IDENTITY(1,1) NOT NULL,
Account varchar(10) NOT NULL,
OrderCount int NULL,
OrderVBMoment char(15) NULL,
OrderUniq AS COALESCE('-' + OrderVBMoment,
CAST(OrderCount AS char(6))) PERSISTED,
[Order] AS Account + OrderUniq,
-- Other columns,
CONSTRAINT PK_HistTradesOrig PRIMARY KEY (ID),
CONSTRAINT UQ_HistTradesOrig UNIQUE (Account, OrderUniq)
);
CREATE INDEX IX_HistTradesOrig
ON dbo.HistTradesOrig(Account, OrderCount);

OrderVBMoment stores the date supplied by the VB program (MMddHHmmss +
millisecond + counter); this stored proc leaves OrderCount empty; the
other stored proc calculates OrderCount as mentioned in my previous
post, and leaves OrderVBMoment empty. The extra (nonunique) index is
created to speed up this calculation - it should have been a UNIQUE
constraint, but because of the non-ANSI compliant way SQL Server
handles null values, this is not possible. If you're on SQL Server
2008 or above, you can also consider using a filtered unique index.

 

[fniles]

Thank you.

Also, did you execute the ALTER INDEX in the correct database, and did it finish without errors? (As you see, I'm clutching at straws).

Yes, my mistake. I did it on the wrong database.
After I did it on the correct database, the numbers did change. Sorry.

Or did you already implement some of the recommendations?

Yes, I did. I now have NON Clustered UNIQUE Primary Key index on
[Order] and Clustered UNIQUE index on ID.

Finally, why rebuild the nonclustered index with a fragmentation of 33%, but leave the much more heavily fragmented (69%) clustered index untouched?

What did you mean by this ?

Thank you very much, Hugo.

Yes, but when I go to "Rebuild Index Task" (under Maintenance Plan
tasks), under "Object" we need to select "Table" or "View" or "Tables
and Views".
In this case, shall I select "Table" ?

Hi fniles,

I never used that wizard (I used the SQL code found in BOL, slightly
modified, in a T-SQL task in the maintenance plan). But my guess is
that the "views" choice selects indexes on views. If you don't have
any indexed views, then "Tables and Views" is the same as "Tables",
and "Views" does nothing but waste a few CPU cycles.

I execute the query rebuilding
ALTER INDEX [IX_HistTradesOrig] ON [dbo].[HistTradesOrig] REBUILD WITH
( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  =
ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY  =
OFF, ONLINE = ON )
but the results afterward look the same as before

This query returns the same result before and after rebuilding the
index above
SELECT a.index_id, name,
avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats (DB_ID(N'DeskDemo'),
OBJECT_ID(N'HisttradesOrig'), NULL, NULL, NULL) AS a  JOIN sys.indexes
AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
index_id    name
avg_fragmentation_in_percent        fragment_count  avg_fragment_size_in_pages
1   PK__HistTradesOrig__52593CB8    68.8564476885645
289                 1.42214532871972
2   IX_HistTradesOrig
33.3333333333333                    12                       2.25

Did I do something wrong ?

I don't see anything to explain why nothing changed. Except for the
change between uppercase and lowercase T in HistTradesOrg /
HistttradesOrg - if your database has a case sensitive collation, that
could refer to two different tables. Also, did you execute the ALTER
INDEX in the correct database, and did it finish without errors? (As
you see, I'm clutching at straws).

I'm also surprised to see two indexes - based on the table design you
posted earlier in this topic, I had expected to see one. Or did you
already implement some of the recommendations? Anyway, for the index
that is based on the order column, which will have out-of-order
inserts, I recommend specifying the FILLFACTOR option as well. This
reserves some free space on each page; this increases the size of the
index, but reduces the amount of page splits. The best value to use is
hard to say without knowing anything about frequency of data
modifications and index maintenance, but you might want to start with
a fill factor 75 or so.

Finally, why rebuild the nonclustered index with a fragmentation of
33%, but leave the much more heavily fragmented (69%) clustered index
untouched?
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -

- Show quoted text -

 

[Hugo Kornelis]

What did you mean by this ?

You posted this as the output of your query:

Index 1 (PK__HistTradesOrig__52593CB8) is reported as having a
fragmentation of almost 69 percent; index 2 (IX_HistTradesOrig) has a
fragmentation of a little over 33 percent. The ALTER INDEX statement
to rebuild the index specifies the latter index. Why not (also)
rebuild the more fragmented index?

 

[fniles]

Yes, sorry, I did the rebuild on the wrong database.
I did this on the correct database and this is the before and after
result:

index_id name avg_fragmentation_in_percent fragment_count
avg_fragment_size_in_pages
1 IX_HistTradesOrig 11.6666666666667 35 6.85714285714286
2 PK__HistTradesOrig__52593CB8 79.1666666666667 21 1.14285714285714

After rebuild index
index_id name avg_fragmentation_in_percent fragment_count
avg_fragment_size_in_pages
1 IX_HistTradesOrig 0 11 19.9090909090909
2 PK__HistTradesOrig__52593CB8 41.1764705882353 9 1.88888888888889

Do you know why after the rebuild the PK fragmentation didn't go down
lower than 41 % ?

 

[fniles]

Hi Hugo,
I have a question about the "retry" section.
The only other UNIQUE constraint is the ID column, but that's an
IDENTITY column, so it will be unique.
You mentioned that "even under the most extreme concurrency
conditions, you will eventually succeed in entering a row with the
retrieved scope_identity() before another connection does. "
But, in my SP the scope_identity() is outside the "retry" section.
The "retry" section I only use ident_current('HistTradesOrig').
So, do you still think that there is no way for the loop to go
forever ?
Thanks, Hugo

BEGIN TRANSACTION;
retry:
BEGIN TRY;
SELECT @expected_id = ident_current('HistTradesOrig') + 1
SELECT @order = @account + '-' + CAST(@expected_id AS varchar(50))
insert into HistTradesOrig
([Floor],[Order],...)
values
(@Floor,@Order,...)
END TRY
BEGIN CATCH;
SELECT @ErrNumber = ERROR_NUMBER()
IF @ErrNumber = 2627
BEGIN;
GOTO retry
END;
ELSE
BEGIN;
SET @ErrMsg = '1.' + ERROR_MESSAGE() + ' ' + @order
RAISERROR (@ErrMsg, 16, 1);
END;
END CATCH

select @ID = SCOPE_IDENTITY()
IF @id <> @expected_id
BEGIN
UPDATE HistTradesOrig
SET [Order] = @ACCOUNT + '-' + CAST(@ID AS varchar(50))
WHERE ID = @ID
END
SET @Success = 1
COMMIT TRANSACTION;