Transaction deadlock on inserting into a table

[Hugo Kornelis]

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;

Hi fniles,

Okay, let's use an example. Let's say that we want to add a row for
@account 'XYZ'. The last created identity value for HistTradesOrig is
123456. In the try, you first retrieve this value and add 1 to get the
expected next identity value (123457). You use this number to
precompute the order ('XYZ123457'), then insert the row.

If there was a concurrent insert from this same stored procedure that
executed the INSERT just a tad earlier, it will attempt to insert a
row with the same order (and a value of 123458 for the identity
column). The result is that this insert will run into a violation of
the unique constraint on the order column; control moves to the catch
block, that will cause the processing to start from scratch.

On the retry, the last used identity is 123458, so the expected id is
calculated as 123459. The order column is precomputed as 'XYZ123459',
and the row is inserted. If this time there was a concurrent insert
from another procedure, that uses different logic for the order
column, the insert will succeed - there is no collission on the order
column, and the IDENTITY column will simply get the next available
column. So we insert a row with IDENTITY value 123460, and order
'XYZ123459'.

After the TRY CATCH, the last generated IDENTITY for this connection
(which is the IDENTITY value of the row just inserted) is retrieved,
and compared to the expected value. If it matches, we're good and the
logic ends. In this case, because of the concurrent insert from the
other stored procedure, there is a difference, so now you update the
just inserted row to change the precomputed (but in this case
incorrect) order value to the correct value.

Had there been no concurrent insert, then SCOPE_IDENTITY would have
matched the expected ID, and the UPDATE would have been skipped. This
will probably be the most common execution pattern.

 

[Hugo Kornelis]

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 % ?

Hi fniles,

I'm sorry, I have no idea.

Erland???

 

[fniles]

Thanks, Hugo.

I think I am missing something, because I am still unclear.

In most cases (99% of time) the application calls this SP
Insert_Into_HisttradesOrig to insert data.
My concern is because the application is multi threaded, different
threads may call this same SP to insert the data at the same time.
The "Retry" section only includes the following
SELECT @expected_id = ident_current('HistTradesOrig') + 1
SELECT @order = @account + '-' + CAST(@expected_id AS varchar(50))
insert into HistTradesOrig ([Floor],[Order],...) values
(@Floor,@Order,...)

And I retry when I get the error 2627 for trying to insert a non
unique order number.
Do you think it is possible that the SP that's called from 1 thread
keeps on getting expected_id that's already created by another thread
(thus keeps getting error 2627 and creating an infinite loop) ?

Thank you.

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;

Hi fniles,

Okay, let's use an example. Let's say that we want to add a row for
@account 'XYZ'. The last created identity value for HistTradesOrig is
123456. In the try, you first retrieve this value and add 1 to get the
expected next identity value (123457). You use this number to
precompute the order ('XYZ123457'), then insert the row.

If there was a concurrent insert from this same stored procedure that
executed the INSERT just a tad earlier, it will attempt to insert a
row with the same order (and a value of 123458 for the identity
column). The result is that this insert will run into a violation of
the unique constraint on the order column; control moves to the catch
block, that will cause the processing to start from scratch.

On the retry, the last used identity is 123458, so the expected id is
calculated as 123459. The order column is precomputed as 'XYZ123459',
and the row is inserted. If this time there was a concurrent insert
from another procedure, that uses different logic for the order
column, the insert will succeed - there is no collission on the order
column, and the IDENTITY column will simply get the next available
column. So we insert a row with IDENTITY value 123460, and order
'XYZ123459'.

After the TRY CATCH, the last generated IDENTITY for this connection
(which is the IDENTITY value of the row just inserted) is retrieved,
and compared to the expected value. If it matches, we're good and the
logic ends. In this case, because of the concurrent insert from the
other stored procedure, there is a difference, so now you update the
just inserted row to change the precomputed (but in this case
incorrect) order value to the correct value.

Had there been no concurrent insert, then SCOPE_IDENTITY would have
matched the expected ID, and the UPDATE would have been skipped. This
will probably be the most common execution pattern.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -

- Show quoted text -

 

[Erland Sommarskog]

Hi fniles,

I'm sorry, I have no idea.

Erland???

Paul?

What is the page count for this index?

It is worth noting that there are several types of fragmentation. The
number avg_fragmentation_in_percent gives you is logical fragmentation.
This number matters if you have to scan the index, and the pages are
all on disk. If there is no logical fragmentation, the index is laid
out entirely in consecutive extents, which means that read-ahead reads
can be very effective. (But if the database file itself is fragmented,
it is less useful.) But if the index is entirely in cache, it matters
less.

A more interesting number, in my opinion, is avg_page_space_used_in_percent,
but this number with the default option LIMITED. This number should be as
close to 100% as possible. If this number is low, this has two ramification:
1) The index takes up more space, and reduces cache effciency.
2) The index take longer time to scan, both on disk and in memory.

It is also worth noting that scans are not very common events, at least
not in a well-tuned OLTP database. (A data warehouse is a different event),
but cache efficiency is always important - at least as long as the
database is bigger than the amount of RAM available.

--
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]

Thanks, Hugo.

I think I am missing something, because I am still unclear.

In most cases (99% of time) the application calls this SP
Insert_Into_HisttradesOrig to insert data.
My concern is because the application is multi threaded, different
threads may call this same SP to insert the data at the same time.
The "Retry" section only includes the following
SELECT @expected_id = ident_current('HistTradesOrig') + 1
SELECT @order = @account + '-' + CAST(@expected_id AS varchar(50))
insert into HistTradesOrig ([Floor],[Order],...) values
(@Floor,@Order,...)

And I retry when I get the error 2627 for trying to insert a non
unique order number.
Do you think it is possible that the SP that's called from 1 thread
keeps on getting expected_id that's already created by another thread
(thus keeps getting error 2627 and creating an infinite loop) ?

Thank you.

Hi fniles,

You can get a duplicate key violation (which will be caught by the
CATCH, and control moves back to retry) only if a concurrent call to
this same stored procedure executes its INSERT statement *after* you
retrieve the IDENT_CURRENT(), but *before* you execute the INSERT. The
window of opportunity for that collision is very small, so the chance
of it happening is very low.

Under heavy concurrency, the multiplication of that small chance with
the number of threads can become significant. That is the reason why
you need the retry logic - because it CAN actually happen. Compare it
to winning the jackpot in the lottery - a very small chance, but if
you buy thousands of lottery tickets each week, you do actually have a
chance of getting it.

The chance of it happening again on the retry (so two retries in a
row) is of course a lot smaller - just like the chance of hitting two
jackpots in a row is infinitely small, even with a thousand lottery
tickets.

And to expand on the comparison, the chance of getting stuck in an
endloess loop with this code is similar to the chance of winning the
jackpot every single week - even with a few thousand lottery tickets,
the chance is still nil.

 

[Hugo Kornelis]

A more interesting number, in my opinion, is avg_page_space_used_in_percent,
but this number with the default option LIMITED. This number should be as
close to 100% as possible. If this number is low, this has two ramification:
1) The index takes up more space, and reduces cache effciency.
2) The index take longer time to scan, both on disk and in memory.

That 100% you mention is not always the best goal. If, for instance,
you decide to leave some room for adding non-consecutive data without
getting page splits, you'd rebuid your index with a fill factor. Let's
say you choose a fill factor of 60 - in that case, I'd expect the
avg_page_space_used_in_percent to be around the 60 mark, and I would
consider not only much lower, but also much higher numbers as a good
reason to rebuild the index.

 

[fniles]

Hi Erland and Hugo,
these are more information on the index after the rebuild

index_id name avg_fragmentation_in_percent page_count
avg_page_space_used_in_percent
1 IX_HistTradesOrig 0 219 NULL
2 PK__HistTradesOrig 41.1764705882353 17 NULL


Thanks for both your help. Really appreciate it.

 

[fniles]

OK, Thank you, Hugo.

Thanks, Hugo.

I think I am missing something, because I am still unclear.

In most cases (99% of time) the application calls this SP
Insert_Into_HisttradesOrig to insert data.
My concern is because the application is multi threaded, different
threads may call this same SP to insert the data at the same time.
The "Retry" section only includes the following
SELECT @expected_id = ident_current('HistTradesOrig') + 1
SELECT @order = @account + '-' + CAST(@expected_id AS varchar(50))
insert into HistTradesOrig ([Floor],[Order],...) values
(@Floor,@Order,...)

And I retry when I get the error 2627 for trying to insert a non
unique order number.
Do you think it is possible that the SP that's called from 1 thread
keeps on getting expected_id that's already created by another thread
(thus keeps getting error 2627 and creating an infinite loop) ?

Thank you.

Hi fniles,

You can get a duplicate key violation (which will be caught by the
CATCH, and control moves back to retry) only if a concurrent call to
this same stored procedure executes its INSERT statement *after* you
retrieve the IDENT_CURRENT(), but *before* you execute the INSERT. The
window of opportunity for that collision is very small, so the chance
of it happening is very low.

Under heavy concurrency, the multiplication of that small chance with
the number of threads can become significant. That is the reason why
you need the retry logic - because it CAN actually happen. Compare it
to winning the jackpot in the lottery - a very small chance, but if
you buy thousands of lottery tickets each week, you do actually have a
chance of getting it.

The chance of it happening again on the retry (so two retries in a
row) is of course a lot smaller - just like the chance of hitting two
jackpots in a row is infinitely small, even with a thousand lottery
tickets.

And to expand on the comparison, the chance of getting stuck in an
endloess loop with this code is similar to the chance of winning the
jackpot every single week - even with a few thousand lottery tickets,
the chance is still nil.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -

- Show quoted text -

 

[Erland Sommarskog]

That 100% you mention is not always the best goal. If, for instance,
you decide to leave some room for adding non-consecutive data without
getting page splits, you'd rebuid your index with a fill factor. Let's
say you choose a fill factor of 60 - in that case, I'd expect the
avg_page_space_used_in_percent to be around the 60 mark, and I would
consider not only much lower, but also much higher numbers as a good
reason to rebuild the index.

Certainly correct. I was mainly thinking of which value that is good from
a read perspective.

--
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]

Hi Erland and Hugo,
these are more information on the index after the rebuild

index_id name avg_fragmentation_in_percent page_count
avg_page_space_used_in_percent
1 IX_HistTradesOrig 0 219 NULL
2 PK__HistTradesOrig 41.1764705882353 17 NULL

page_count = 17 means two full extents and one page on a mixed extent
(if that happens to non-clustered index.) I can't say why the fragmentation
is still that high, but 17 pages is not much.

avg_page_space_used_in_percent is NULL, becuse you ran with limited.
You need DETAILED to get that value populated.

--
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