October 5, 2016 at 2:59 pm
In a high-volume test environment, we are getting sporadic instances of this error: The current transaction failed to commit due to a serializable validation failure.
I understand WHAT causes this to occur, but I can't see HOW it's happening given the actual DDL involved. The procedure that is throwing this error inserts a single row to a memory-optimized table using the passed parameters as values. I don't see how a phantom row is being inserted in the range when there is no range being read.
I'm posting below the heavily redacted-and-reduced DDL for the table and procedure. I have removed most of the columns and all of the non-clustered indexes (I'm not sure if they could possibly be involved, but please correct me if non-clustered indexes can cause this issue). There are no foreign keys and no constraints besides the primary key.
I cannot reproduce this issue outside of a high-volume environment. There are other procedures that insert/update/select this table, is that relevant? This procedure, however, is the one that is failing. It's called by itself and is the only thing in its own transaction.
I would appreciate any clarifications or troubleshooting hints. I'm not sure what to look for.
CREATE TABLE [dbo].[test_table]
(
[first_key] [varchar](4) COLLATE Latin1_General_100_BIN2 NOT NULL,
[second_key] [varchar](30) COLLATE Latin1_General_100_BIN2 NOT NULL,
[numerous_other_fields] [varchar](15) COLLATE Latin1_General_100_BIN2 NULL,
CONSTRAINT [test_table_pk] PRIMARY KEY NONCLUSTERED
(
[first_key] ASC,
[second_key] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO
CREATE PROCEDURE [dbo].[the_problem_procedure]
(
@first_key CHAR(4)
, @second_key VARCHAR(30)
, @numerous_other_fields VARCHAR(15)
,@return_code INT OUTPUT
,@error_message NVARCHAR(4000) OUTPUT
)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english', DELAYED_DURABILITY = ON)
DECLARE @process_nameVARCHAR(35)
DECLARE @err_nbrINT
DECLARE @err_msgNVARCHAR(4000)
SET @return_code = 0
SET @error_message = 'SUCCESS'
BEGIN TRY
INSERT INTO [dbo].[test_table]
( [first_key]
,[second_key]
,[numerous_other_fields]
)
VALUES
(
@first_key
,@second_key
,@numerous_other_fields
);
END TRY
BEGIN CATCH
SELECT @err_nbr = ERROR_NUMBER(), @err_msg = ERROR_MESSAGE()
SET @error_message = 'FAILED Error Code: '+CONVERT(VARCHAR,@err_nbr) +', Error Message: '+ @err_msg
SET @return_code = -1;
THROW;
END CATCH
END
October 5, 2016 at 3:24 pm
The range-based nature of SERIALIAZABLE is likely getting you here, especially given that you have concurrent INSERT/DELETE/UPDATE activity. If those nonclustered indexes are RANGE based they could well be involved. Also you may not have sufficient indexing. If you INSERT into a range that has been scanned due to suboptimal indexing you could be hit (along with being inefficient in general).
Read Validation part of this (all of it actually):
https://msdn.microsoft.com/en-us/library/mt668435.aspx
You do have proper retry logic built into your application to handle such things, right?
Oh, an obvious question is could you be inserting the same (key) values twice at the same time?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 5, 2016 at 4:55 pm
Memory optimized tables are fairly new to me, and apparently to the third party who has written these procedures. While investigating this issue, I saw the retry logic recommended. No, there is no retry logic.
We should not be using serializable isolation level, but I can't guarantee that it isn't happening (third party).
I can guarantee that we aren't inserting duplicate keys. It's complicated, but the process before calling this procedure ensures that (and that process does not belong to the third party, so I can actually be certain of what it does).
Sub-optimal indexing is highly likely.
Thanks for giving me a direction to investigate.
October 5, 2016 at 5:58 pm
Stephanie Giovannini (10/5/2016)
Memory optimized tables are fairly new to me, and apparently to the third party who has written these procedures. While investigating this issue, I saw the retry logic recommended. No, there is no retry logic.We should not be using serializable isolation level, but I can't guarantee that it isn't happening (third party).
I can guarantee that we aren't inserting duplicate keys. It's complicated, but the process before calling this procedure ensures that (and that process does not belong to the third party, so I can actually be certain of what it does).
Sub-optimal indexing is highly likely.
Thanks for giving me a direction to investigate.
In-memory OLTP is fairly new to pretty much everyone. 😀 I would not be surprised at all if the 3rd party has done some questionable or suboptimal things. In my 20+ years of consulting experience they all do, even with regular SQL Server stuff. 😎
I'm pretty sure you can get details about the isolation level of the activity that is going on.
No dupes is good, as it eliminates a source of the problem. Now on to DELETE/UPDATE potentials and range scanning issues.
I wish I weren't so busy right now or I would offer to help out for free just to get a "production" project on Hekaton. 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 6, 2016 at 5:02 am
I'm with Kevin on still being a little new on the memory optimized stuff. Most of my testing and training suggests you might be better off with a hash key than just a nonclustered key. However, you need to be sure you get a proper setup on the hash buckets because hash collisions kill performance as bad as the scans you're currently getting, maybe worse.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply