October 6, 2014 at 11:02 am
Wondering if someone can help out.
I have been encountering deadlocks every half an hour. How can I resolve this issue.
BEGIN TRANSACTION UPDATE COLUMN_NEXT_ID SET Next_ID = Next_ID + 1 WHERE Company = N'BTMG' AND Form_Name = N'COL:CORE:Recent_Items'
Environment details:
SQL Server 2008R2
Table has 173 rows.(Pretty small table)
No Indexes.
Let me know if I can provide you guys with some more details.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
October 6, 2014 at 2:01 pm
A deadlock involves two actions that both need an lock (mostly on a row or page) but the resource is already locked by the other action. Most of the times a deadlock occurs with two different queries, but it could sometimes be in a single query when the same page is accessed.
The deadlock with two different queries can be prevented by rewriting the queries so they access the different tables in the same sequence.
The deadlock from a single query can be prevented by first storing values in variables and use these later in the query, instead of reading and updating in the same statement.
October 6, 2014 at 3:17 pm
Add a clustered index to the "COLUMN_NEXT_ID" table on "Company" and "Form_Name".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 6, 2014 at 3:48 pm
ScottPletcher (10/6/2014)
Add a clustered index to the "COLUMN_NEXT_ID" table on "Company" and "Form_Name".
And how creating an index will help?:-D
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
October 6, 2014 at 4:06 pm
New Born DBA (10/6/2014)
ScottPletcher (10/6/2014)
Add a clustered index to the "COLUMN_NEXT_ID" table on "Company" and "Form_Name".And how creating an index will help?:-D
It will avoid a full table scan, locking as it goes. Instead, a single, specific row will be locked. Unless two people need to update the exact same row, you're much less likely to run into a deadlock. If you still have issues, worst case, you can (artificially) increase the row size to have each page limited to only 1 or 2 rows.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 6, 2014 at 4:22 pm
ScottPletcher (10/6/2014)
New Born DBA (10/6/2014)
ScottPletcher (10/6/2014)
Add a clustered index to the "COLUMN_NEXT_ID" table on "Company" and "Form_Name".And how creating an index will help?:-D
It will avoid a full table scan, locking as it goes. Instead, a single, specific row will be locked. Unless two people need to update the exact same row, you're much less likely to run into a deadlock. If you still have issues, worst case, you can (artificially) increase the row size to have each page limited to only 1 or 2 rows.
The same row is getting updated all the time, only FormName column is different.
For instance:
BEGIN TRANSACTION UPDATE COLUMN_NEXT_ID SET Next_ID = Next_ID + 1 WHERE Company = N'BTMG' AND Form_Name = N'COL:CORE:Recent_Items'
BEGIN TRANSACTION UPDATE COLUMN_NEXT_ID SET Next_ID = Next_ID + 1 WHERE Company = N'BTMG' AND Form_Name = N'COL:CORE:Audit_Status'
BEGIN TRANSACTION UPDATE COLUMN_NEXT_ID SET Next_ID = Next_ID + 1 WHERE Company = N'BTMG' AND Form_Name = N'COL:CORE:Journal'
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
October 6, 2014 at 4:29 pm
New Born DBA (10/6/2014)
ScottPletcher (10/6/2014)
New Born DBA (10/6/2014)
ScottPletcher (10/6/2014)
Add a clustered index to the "COLUMN_NEXT_ID" table on "Company" and "Form_Name".And how creating an index will help?:-D
It will avoid a full table scan, locking as it goes. Instead, a single, specific row will be locked. Unless two people need to update the exact same row, you're much less likely to run into a deadlock. If you still have issues, worst case, you can (artificially) increase the row size to have each page limited to only 1 or 2 rows.
The same row is getting updated all the time, only FormName column is different.
For instance:
BEGIN TRANSACTION UPDATE COLUMN_NEXT_ID SET Next_ID = Next_ID + 1 WHERE Company = N'BTMG' AND Form_Name = N'COL:CORE:Recent_Items'
BEGIN TRANSACTION UPDATE COLUMN_NEXT_ID SET Next_ID = Next_ID + 1 WHERE Company = N'BTMG' AND Form_Name = N'COL:CORE:Audit_Status'
BEGIN TRANSACTION UPDATE COLUMN_NEXT_ID SET Next_ID = Next_ID + 1 WHERE Company = N'BTMG' AND Form_Name = N'COL:CORE:Journal'
The index I suggested was on Company and Form_Name.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 7, 2014 at 10:36 am
ScottPletcher (10/6/2014)
The index I suggested was on Company and Form_Name.
Do you think I should test it out in Dev and QA first before I add a clustered index on Company name and a non clustered index on Form_Name or just add those indexes in Prod?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
October 7, 2014 at 10:37 am
New Born DBA (10/7/2014)
ScottPletcher (10/6/2014)
The index I suggested was on Company and Form_Name.
Do you think I should test it out in Dev and QA first before I add a clustered index on Company name and a non clustered index on Form_Name or just add those indexes in Prod?
Since the table has no index at all at present, just add it. It can't hurt anything.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 7, 2014 at 10:50 am
ScottPletcher (10/7/2014)
New Born DBA (10/7/2014)
ScottPletcher (10/6/2014)
The index I suggested was on Company and Form_Name.
Do you think I should test it out in Dev and QA first before I add a clustered index on Company name and a non clustered index on Form_Name or just add those indexes in Prod?
Since the table has no index at all at present, just add it. It can't hurt anything.
Just wanted to make sure I have the right syntax and again the table has only 173 records.
USE [DBName]
GO
ALTER TABLE [dbo].[COLUMN_NEXT_ID] ADD PRIMARY KEY CLUSTERED
(
[Company] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE [DBName]
GO
CREATE UNIQUE NONCLUSTERED INDEX [FormName] ON [dbo].[COLUMN_NEXT_ID]
(
[Form_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
October 7, 2014 at 10:57 am
USE [DBName]
GO
CREATE CLUSTERED INDEX [Company_FormName] ON [dbo].[COLUMN_NEXT_ID]
(
[Company], [Form_Name]
)WITH ( FILLFACTOR = 100 ) ON [PRIMARY]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 7, 2014 at 11:00 am
ScottPletcher (10/7/2014)
USE [DBName]
GO
CREATE CLUSTERED INDEX [Company_FormName] ON [dbo].[COLUMN_NEXT_ID]
(
[Company], [Form_Name]
)WITH ( FILLFACTOR = 100 ) ON [PRIMARY]
Now this is the warning I am getting:
Warning! The maximum key length is 900 bytes. The index 'Company_FormName' has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
October 7, 2014 at 11:05 am
New Born DBA (10/7/2014)
ScottPletcher (10/7/2014)
USE [DBName]
GO
CREATE CLUSTERED INDEX [Company_FormName] ON [dbo].[COLUMN_NEXT_ID]
(
[Company], [Form_Name]
)WITH ( FILLFACTOR = 100 ) ON [PRIMARY]
Now this is the warning I am getting:
Warning! The maximum key length is 900 bytes. The index 'Company_FormName' has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail.
Unless you actually ever plan to use really, really long names, you can ignore that.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 7, 2014 at 11:08 am
ScottPletcher (10/7/2014)
New Born DBA (10/7/2014)
ScottPletcher (10/7/2014)
USE [DBName]
GO
CREATE CLUSTERED INDEX [Company_FormName] ON [dbo].[COLUMN_NEXT_ID]
(
[Company], [Form_Name]
)WITH ( FILLFACTOR = 100 ) ON [PRIMARY]
Now this is the warning I am getting:
Warning! The maximum key length is 900 bytes. The index 'Company_FormName' has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail.
Unless you actually ever plan to use really, really long names, you can ignore that.
Got it. So if I ever update the table with very long name which is let's say 1030bytes, it will fail but other than, I should be OK.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
October 7, 2014 at 11:14 am
New Born DBA (10/7/2014)
ScottPletcher (10/7/2014)
New Born DBA (10/7/2014)
ScottPletcher (10/7/2014)
USE [DBName]
GO
CREATE CLUSTERED INDEX [Company_FormName] ON [dbo].[COLUMN_NEXT_ID]
(
[Company], [Form_Name]
)WITH ( FILLFACTOR = 100 ) ON [PRIMARY]
Now this is the warning I am getting:
Warning! The maximum key length is 900 bytes. The index 'Company_FormName' has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail.
Unless you actually ever plan to use really, really long names, you can ignore that.
Got it. So if I ever update the table with very long name which is let's say 1030bytes, it will fail but other than, I should be OK.
Yes, exactly.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply