none
Wrong Error Message when using Table Variables instead of local #Temp table RRS feed

  • Question

  • Hello folks...

    I found this today when I was updating some scheduled queries...

    I have an "select * from OPENQUERY" command that gets data from an Oracle database.  The select statement, by itself, works 100% of the time.  There are about 24000 records returned.

    The external database was recently changed.  One of the fields went from 3 characters to 5 characters, and I was not aware of the change.

    The OPENQUERY currently inserts into a table variable.  When I tried to insert into it, I get this message:

    "Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

    However, if I change the table to a #temp table, the query returns this error message (which is expected):

    "String or binary data would be truncated in table..."

    When I change the field size to 5 characters, both messages go away and BOTH inserts work.

    The "deadlock" message appears to be "incorrect".

    Hope this helps someone.
    Monday, July 13, 2020 3:55 PM

Answers

  • Hi Forch,

    I could recproduce this on Microsoft SQL Server 2019 (RTM-CU2).

    After adding trace flag 1222, I could have below error details about this deadlock.

    As mentioned in the error log, I had a deadlock involving a single process and single resource. The session already holds a SCH-M lock on a user defined table and this blocks an attempt to get a SCH-S lock on the same object by the same session later. 

    Please refer the answer in below link for more details:

    How to replicate a SCH_S, SCH_M deadlock

    Besides, I recommend you to submit your issue to the Microsoft feedback at this link https://feedback.azure.com/forums/908035-sql-server . This site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft feedback.

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Forch718 Tuesday, July 14, 2020 12:06 PM
    Tuesday, July 14, 2020 2:39 AM
  • I played more with this, and it is a regression error from SQL 2017. It appears to be related to the change in SQL 2019 where they defer compilation of statemetns with table variables until they are executed to get better estimates. If I run
    ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF

    I don't see the deadlock.

    Last night I said I did not get the deadlock on Azure SQL Database. This was because my database was in compatibility level 130, and thus deferred compilation on table variabels was turned off. When I changed the compat level to 150, I got the deadlock.

    Since this is a regression, Microsoft may be more keen to address it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Forch718 Tuesday, July 14, 2020 12:05 PM
    Tuesday, July 14, 2020 8:44 AM

All replies

  • What is your SQL Server version, can you show the complete repro (if possible), are you 100% sure it was not a deadlock in that situation (for whatever reason just that particular time)? 

    In other words, are you able to re-create this situation consistently every time?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, July 13, 2020 4:19 PM
    Moderator
  • So, when you use the table variable again, then you get the deadlock error again?

    Does the error occur, when you run the same change scenario with local tables?

    Does the error occur, when you run the same change scenario with a linked SQL Server?

    Does the table variable has primary or unique key on that change column and the temp table does not? 

    Does the query run in parallel?

    Cause table variables are in newer SQL Server versions written do tempdb, when they reach a certain size...

    Monday, July 13, 2020 4:27 PM
  • Hello.....

    Yes, I can reproduce it on the fly.  We are using SQL Server 2019, and I am using our development server.  I turned on the deadlock logging and nothing was being recorded.  I am sure it isn't deadlocked.

    In the past the OPENQUERY returned about 60000 rows, but now it is around 24000.

    To reproduce, all I have to do is change the table declaration from "[Fieldname] Varchar(5)" to "[FieldName] Varchar(3)"; and it shows up as deadlock.  I tried it with 2 different field name and the same result took place.

    I also tried it using a local table (instead of getting the data from an OPENQUERY).  The same results took place.

    Two interesting findings:

    First:
    If I remove "PRIMARY KEY" from the table variable declaration, I get the truncation error "String or binary data would be truncated in table".  
    If the primary key is in the table variable declaration, the error message is: "Transaction (Process ID 69) was deadlocked on lock".

    Second interesting finding:
    If I use the "TOP" parameter to limit the number of rows that are returned, the error message changes.  For example, if the number of rows is less than 910, then I get the "String or binary data would be truncated in table" message.  If there are 911 or more rows, I get "was deadlocked".

    Step to reproduce my findings in a brand new database... first create the database with a table with 2000 random records.

    Create Database ReproduceError;
    GO
    USE ReproduceError
    GO
    Create Table dbo.RandomData (ID varchar(6) Primary Key, Word Varchar(5));
    GO
    Declare @i INT = 1
    
    While @i <= 2000
    Begin
    	insert into dbo.RandomData (ID, Word) select Substring(CAST(NewID() as Varchar(255)), 1, 6), Substring(CAST(NewID() as Varchar(255)), 1, 5)
    	Set @i = @i + 1;
    End
    GO

    Now, this command will return the deadlock error:

    --DEADLOCK ERROR
    Declare @tableVariable Table (	ID varchar (6) NOT NULL PRIMARY KEY,
    								Word Varchar(3));
    
    insert into @tableVariable (ID, Word)
    	select ID, Word from dbo.RandomData;

    And these return the "truncation" error (I set TOP=5):

    --String or binary data would be truncated ERROR
    Declare @tableVariable Table (	ID varchar (6) NOT NULL PRIMARY KEY,
    								Word Varchar(3));
    
    insert into @tableVariable (ID, Word)
    	select top 5 ID, Word from dbo.RandomData;

    ...and this one also returns the truncation error, after removing PRIMARY KEY):

    --String or binary data would be truncated ERROR
    Declare @tableVariable Table (	ID varchar (6) NOT NULL,
    								Word Varchar(3));
    
    insert into @tableVariable (ID, Word)
    	select ID, Word from dbo.RandomData;

    ...and if I change the it from "Word Varchar(3)" to "Word Varchar(5)", it works as expected.

    --WORKS
    Declare @tableVariable Table (	ID varchar (6) NOT NULL PRIMARY KEY,
    								Word Varchar(5));
    
    insert into @tableVariable (ID, Word)
    	select ID, Word from dbo.RandomData;

    I would just expect to see the same (truncate) error message every time when "Varchar(3)" is specified.

    With the sample database I'm the only user running the only query, so I should assume "Deadlocks" aren't an issue, and there are only 2000 records.

    My tempDB has 4 MDF/NDF files; each at 75MB in size.



    • Edited by Forch718 Monday, July 13, 2020 6:24 PM
    Monday, July 13, 2020 6:07 PM
  • I got exactly the same result following these steps. It does look like a bug, so I suggest to post on Connect, share the link and I can vote.

    Also tested with int identity(1,1) PK column and got the same error too.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Monday, July 13, 2020 6:37 PM
    Moderator
  • Well, the error message as such is correct. There is a deadlock.

    But it is quite a weird deadlock, since it is only one process involved, so it somehow manage to block itself.

    As Naomi suggests, you can report this on
    https://feedback.azure.com/forums/908035-sql-server

    I was able to repro this on SQL 2019 CU5 (the most recent release), but to be honest, I don't expect this to by the triage bar, as long as it only happens when there is overlong string.

    Interesting enough, I don't get the deadlock when I test on Azure SQL Database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, July 13, 2020 8:56 PM
  • Hi Forch,

    I could recproduce this on Microsoft SQL Server 2019 (RTM-CU2).

    After adding trace flag 1222, I could have below error details about this deadlock.

    As mentioned in the error log, I had a deadlock involving a single process and single resource. The session already holds a SCH-M lock on a user defined table and this blocks an attempt to get a SCH-S lock on the same object by the same session later. 

    Please refer the answer in below link for more details:

    How to replicate a SCH_S, SCH_M deadlock

    Besides, I recommend you to submit your issue to the Microsoft feedback at this link https://feedback.azure.com/forums/908035-sql-server . This site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft feedback.

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Forch718 Tuesday, July 14, 2020 12:06 PM
    Tuesday, July 14, 2020 2:39 AM
  • I played more with this, and it is a regression error from SQL 2017. It appears to be related to the change in SQL 2019 where they defer compilation of statemetns with table variables until they are executed to get better estimates. If I run
    ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF

    I don't see the deadlock.

    Last night I said I did not get the deadlock on Azure SQL Database. This was because my database was in compatibility level 130, and thus deferred compilation on table variabels was turned off. When I changed the compat level to 150, I got the deadlock.

    Since this is a regression, Microsoft may be more keen to address it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Forch718 Tuesday, July 14, 2020 12:05 PM
    Tuesday, July 14, 2020 8:44 AM
  • Thank you everyone for taking the time to look into this.  I learned a few tricks too!

    I've also put my findings on the Azure site.  Maybe something they will want to fix later... Fortunately it isn't a show-stopper.


    Tuesday, July 14, 2020 2:44 PM