Transaction was deadlocked on lock resources with another process

Question

Post reply

Transaction was deadlocked on lock resources with another process

L_Goyal

SSC Enthusiast

Points: 115
More actions
January 19, 2021 at 8:21 am
Go to Answer
#3834904

I have table in SQL Server 2014. Please find the structure below:
CREATE TABLE [dbo].[ProductIMEISerialNoes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[IMEI1] [nvarchar](max) NULL,
[IMEI2] [nvarchar](max) NULL,
[SerialNo] [nvarchar](max) NULL,
[ProductModel_ProductID] [int] NULL,
[ProcessDate] [datetime] NOT NULL DEFAULT ('1900-01-01T00:00:00.000'),
[BoxName] [nvarchar](max) NULL,
[BoxNo] [nvarchar](max) NULL DEFAULT ('0'),
[Order_OrderID] [int] NULL,
[Color] [nvarchar](max) NULL,
[BoxSize] [int] NULL DEFAULT ((0)),
[IMEI3] [nvarchar](max) NULL,
[IMEI4] [nvarchar](max) NULL,
[Version] [int] NULL,
[CurrentStatus] [nvarchar](max) NULL,
[BoxStatus] [int] NULL,
[BoxId] [int] NULL,
[PrintStatus] [int] NULL,
[BoxWeight] [decimal](7, 3) NOT NULL DEFAULT ((0.0)),
[TempBoxNo] [nvarchar](max) NOT NULL CONSTRAINT [DF_ProductIMEISerialNoes_TempBoxNo] DEFAULT ('0')
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
We currently have around 8 systems that are running dotnet mvc applications that concurrently access the same database/same table for read/write. The issue is that, we very frequently get the above exception -- "Transaction was deadlocked on lock resources with another process and was chosen as the...". Could you please suggest what can be done to solve this issue or work around this issue? Maybe table restructuring, something at the sql server level? Any help will be greatly appreciated.
DesNorton

SSC-Insane

Points: 24386
More actions
January 19, 2021 at 8:40 am
Answer
#3834910
Deadlocks are not my strong point. That said, deadlocks occur when 2 or more processes are waiting on each other to access the same resource. The deadlocks can occur at the key/page/table level. In my limited experience with deadlocks, I have found that the best ways to prevent them is
- All processes to access the same resources in the same sequence, so that processes do not end up waiting for each other.
- Keep transactions as short as possible, to release locks as soon as possible
- Code to be as fast and efficient as possible, to release locks as soon as possible
That said, all of those nvarchar(max) fields are a massive red flag. SQL does not know up front how much data it will get back when reading each row, so it uses the size of the defined fields to attempt to allocate memory to work in.
Nvarchar(max) is approximately 1 billion characters (2GB of data). An IMEI number is what? 16 characters if I recall. Even if stored as nvarchar(20), MAX is 50million time bigger than it needs to be. Your code, and storage will NEVR be efficient with that kind of over engineering.
How to post data/code on a forum to get the best help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

L_Goyal SSC Enthusiast Points: 115 More actions · Answer 1

Thank You for your response. I am going to change the data types first and see.

L_Goyal SSC Enthusiast Points: 115 More actions · Answer 2

I have one more requirement. We usually receive IMEI set for a model in excel format (maybe 20K, 30K, 40K) etc. What will be the fastest way to check if these values exist in the database or not ? Any suggestions will be greatly appreciated.