I am using SQL Server 2000 sp4 and have a single table that is now getting a
deadlock problem after 3 years of use. It started when the number of rows
went past 16K.
The problem is that a query returning more than ~500 rows against the table
causes the optimizer to use a single "Shared Table" lock for the query
instead of its normal behavior of issuing two locks: 1 a Page Shared Lock (S)
and 2 a Table Intent Shared Lock (IS). The single Shared Table lock causes
any other clients wishing to modify the table to deadlock waiting for the
table lock to be freed up.
FYI. I have other tables in the same database over 16K rows that do not have
this problem.
I have manually created the table on a test database on another SQL
database, on another machine, copied the data and the same problem occurred.
Removing a few thousand rows fixes the problem, but is an unaccceptable
solution.
A temporary fix is to change the fill factor on the primary key index to a
lower number, but after a few days and more rows are added, the deadlock
occurs again, until the fill factor is lowered again. I am at 50% now.
I tried removing the cluster on the primary key index and moving the index
into another table space, but the same problem occurs once again if I shrink
the tables space the table is in (basically removing any fill space).
Removing an unneeded column also temporarily helped, but perhaps something
else was going on that made the locking issue go away temporarily.
So it seems that for this particular table the optimizer is taking into
consideration how much fill space is in the table to choose which type of
locking scheme to use. I can produce this problem simply using the
Enterprise Manager and opening a "SELECT *" query on the table and using the
"Current Activity" window to view the locks placed on the table.
I am in desperate need of a proper fix for this problem. Does anyone have
any ideas?
FYI: I also noticed what appears to be a bug with Enterprise Manager. When
looking at the â'Locks / Process IDâ' or the â'Locks / Objectâ' screens for locks
issued by several "SELECT *" queries that were still open, the â'Indexâ' column
showed the same primary key index being used for the â'Page lockâ' for all the
queries still open. The index name being displayed would be the primary key
index for the table of the last query I executed in Enterprise Manager.
Table DDL:
CREATE TABLE [dbo].[tCompany] (
[CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[NameNick] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[AddressLine1] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[AddressLine2] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Country] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Telephone] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[Telephone2] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[Fax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[AlertFax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[ContactPerson] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[ContactPerson2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[AccountNum] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[BillToAttention] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Airport] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[Email] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[Password] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[LastUsedTime] [datetime] NOT NULL ,
[OurCompanyID] [int] NOT NULL ,
[SalespersonID] [int] NOT NULL ,
[CustomerCompanyID] [int] NOT NULL ,
[ParentCompanyID] [int] NULL ,
[Shipper] [smallint] NOT NULL ,
[Receiver] [smallint] NOT NULL ,
[ThirdPartyBilling] [smallint] NOT NULL ,
[Cartage] [smallint] NOT NULL ,
[Customer] [smallint] NOT NULL ,
[Payee] [smallint] NOT NULL ,
[Company] [smallint] NOT NULL ,
[Hide] [smallint] NOT NULL ,
[AutoSendEmail] [smallint] NOT NULL ,
[CommericalAirApproved] [smallint] NOT NULL ,
[CarrierInsuranceAmount] [money] NULL ,
[CarrierInsuranceExpires] [datetime] NULL ,
[Notes] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[WebPageHome] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[WebPageTrackPRO] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
NULL ,
[WebPageTrackFlight] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
NULL ,
[SOPNotes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[WebPageTrackPROMethod] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CS_AS
NULL ,
[WebPassword] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[WebLoginID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[DepositACH] [smallint] NOT NULL ,
[Province] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[International] [smallint] NOT NULL ,
[AutoTrackStatus] [smallint] NOT NULL ,
[AutoTrackCharges] [smallint] NOT NULL ,
[InvoiceTerms] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[OpsPersonID] [int] NOT NULL ,
[RateSpreadsheet] [smallint] NOT NULL
) ON [Company]
GO
ALTER TABLE [dbo].[tCompany] WITH NOCHECK ADD
CONSTRAINT [PK_tCompany] PRIMARY KEY CLUSTERED
(
[CompanyID]
) WITH FILLFACTOR = 90 ON [Company]
GO
ALTER TABLE [dbo].[tCompany] ADD
CONSTRAINT [DF_tCompany_Name] DEFAULT ('N/A') FOR [Name],
CONSTRAINT [DF_tCompany_NameNick] DEFAULT ('N/A') FOR [NameNick],
CONSTRAINT [DF_tCompany_WebClient] DEFAULT ('none') FOR [Password],
CONSTRAINT [DF_tCompany_CreateDate] DEFAULT (getdate()) FOR [LastUsedTime],
CONSTRAINT [DF_tCompany_OurCompanyID] DEFAULT (3) FOR [OurCompanyID],
CONSTRAINT [DF_tCompany_SalespersonID] DEFAULT (0) FOR [SalespersonID],
CONSTRAINT [DF_tCompany_CustomerCompanyID] DEFAULT (0) FOR
[CustomerCompanyID],
CONSTRAINT [DF_tCompany_Shipper] DEFAULT (0) FOR [Shipper],
CONSTRAINT [DF_tCompany_Receiver] DEFAULT (0) FOR [Receiver],
CONSTRAINT [DF_tCompany_ThirdPartyBilling] DEFAULT (0) FOR
[ThirdPartyBilling],
CONSTRAINT [DF_tCompany_Cartage] DEFAULT (0) FOR [Cartage],
CONSTRAINT [DF_tCompany_Customer] DEFAULT (0) FOR [Customer],
CONSTRAINT [DF_tCompany_Payee] DEFAULT (0) FOR [Payee],
CONSTRAINT [DF_tCompany_Company] DEFAULT (0) FOR [Company],
CONSTRAINT [DF_tCompany_Hide] DEFAULT (0) FOR [Hide],
CONSTRAINT [DF_tCompany_AutoSendEmail] DEFAULT (0) FOR [AutoSendEmail],
CONSTRAINT [DF_tCompany_CommericalAirApproved] DEFAULT (0) FOR
[CommericalAirApproved],
CONSTRAINT [DF_tCompany_DepositACH_1] DEFAULT (0) FOR [DepositACH],
CONSTRAINT [DF_tCompany_International_1] DEFAULT (0) FOR [International],
CONSTRAINT [DF_tCompany_AutoTrackStatus] DEFAULT (0) FOR [AutoTrackStatus],
CONSTRAINT [DF_tCompany_AutoTrackCharges] DEFAULT (0) FOR [AutoTrackCharges],
CONSTRAINT [DF_tCompany_OpsPersonID] DEFAULT (0) FOR [OpsPersonID],
CONSTRAINT [DF_tCompany_RateSpreadsheet] DEFAULT (0) FOR [RateSpreadsheet]
GO
CREATE INDEX [IX_tCompany_NameNick] ON [dbo].[tCompany]([NameNick]) WITH
FILLFACTOR = 90 ON [CompanyX]
GOTroubleshooting Deadlocks
http://msdn2.microsoft.com/en-us/library/Aa937573(SQL.80).aspx
SQL Server technical bulletin - How to resolve a deadlock
http://support.microsoft.com/kb/832524/
AMB
"FloMiester" wrote:
> I am using SQL Server 2000 sp4 and have a single table that is now getting a
> deadlock problem after 3 years of use. It started when the number of rows
> went past 16K.
> The problem is that a query returning more than ~500 rows against the table
> causes the optimizer to use a single "Shared Table" lock for the query
> instead of its normal behavior of issuing two locks: 1 a Page Shared Lock (S)
> and 2 a Table Intent Shared Lock (IS). The single Shared Table lock causes
> any other clients wishing to modify the table to deadlock waiting for the
> table lock to be freed up.
> FYI. I have other tables in the same database over 16K rows that do not have
> this problem.
> I have manually created the table on a test database on another SQL
> database, on another machine, copied the data and the same problem occurred.
> Removing a few thousand rows fixes the problem, but is an unaccceptable
> solution.
> A temporary fix is to change the fill factor on the primary key index to a
> lower number, but after a few days and more rows are added, the deadlock
> occurs again, until the fill factor is lowered again. I am at 50% now.
> I tried removing the cluster on the primary key index and moving the index
> into another table space, but the same problem occurs once again if I shrink
> the tables space the table is in (basically removing any fill space).
> Removing an unneeded column also temporarily helped, but perhaps something
> else was going on that made the locking issue go away temporarily.
> So it seems that for this particular table the optimizer is taking into
> consideration how much fill space is in the table to choose which type of
> locking scheme to use. I can produce this problem simply using the
> Enterprise Manager and opening a "SELECT *" query on the table and using the
> "Current Activity" window to view the locks placed on the table.
> I am in desperate need of a proper fix for this problem. Does anyone have
> any ideas?
> FYI: I also noticed what appears to be a bug with Enterprise Manager. When
> looking at the â'Locks / Process IDâ' or the â'Locks / Objectâ' screens for locks
> issued by several "SELECT *" queries that were still open, the â'Indexâ' column
> showed the same primary key index being used for the â'Page lockâ' for all the
> queries still open. The index name being displayed would be the primary key
> index for the table of the last query I executed in Enterprise Manager.
>
> Table DDL:
> CREATE TABLE [dbo].[tCompany] (
> [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> [NameNick] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> [AddressLine1] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [AddressLine2] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [Country] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [Telephone] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> [Telephone2] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> [Fax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> [AlertFax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> [ContactPerson] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [ContactPerson2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [AccountNum] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> [BillToAttention] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [Airport] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> [Email] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> [Password] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
> [LastUsedTime] [datetime] NOT NULL ,
> [OurCompanyID] [int] NOT NULL ,
> [SalespersonID] [int] NOT NULL ,
> [CustomerCompanyID] [int] NOT NULL ,
> [ParentCompanyID] [int] NULL ,
> [Shipper] [smallint] NOT NULL ,
> [Receiver] [smallint] NOT NULL ,
> [ThirdPartyBilling] [smallint] NOT NULL ,
> [Cartage] [smallint] NOT NULL ,
> [Customer] [smallint] NOT NULL ,
> [Payee] [smallint] NOT NULL ,
> [Company] [smallint] NOT NULL ,
> [Hide] [smallint] NOT NULL ,
> [AutoSendEmail] [smallint] NOT NULL ,
> [CommericalAirApproved] [smallint] NOT NULL ,
> [CarrierInsuranceAmount] [money] NULL ,
> [CarrierInsuranceExpires] [datetime] NULL ,
> [Notes] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> [WebPageHome] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> [WebPageTrackPRO] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> NULL ,
> [WebPageTrackFlight] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> NULL ,
> [SOPNotes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> [WebPageTrackPROMethod] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CS_AS
> NULL ,
> [WebPassword] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> [WebLoginID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> [DepositACH] [smallint] NOT NULL ,
> [Province] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [International] [smallint] NOT NULL ,
> [AutoTrackStatus] [smallint] NOT NULL ,
> [AutoTrackCharges] [smallint] NOT NULL ,
> [InvoiceTerms] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> [OpsPersonID] [int] NOT NULL ,
> [RateSpreadsheet] [smallint] NOT NULL
> ) ON [Company]
> GO
> ALTER TABLE [dbo].[tCompany] WITH NOCHECK ADD
> CONSTRAINT [PK_tCompany] PRIMARY KEY CLUSTERED
> (
> [CompanyID]
> ) WITH FILLFACTOR = 90 ON [Company]
> GO
> ALTER TABLE [dbo].[tCompany] ADD
> CONSTRAINT [DF_tCompany_Name] DEFAULT ('N/A') FOR [Name],
> CONSTRAINT [DF_tCompany_NameNick] DEFAULT ('N/A') FOR [NameNick],
> CONSTRAINT [DF_tCompany_WebClient] DEFAULT ('none') FOR [Password],
> CONSTRAINT [DF_tCompany_CreateDate] DEFAULT (getdate()) FOR [LastUsedTime],
> CONSTRAINT [DF_tCompany_OurCompanyID] DEFAULT (3) FOR [OurCompanyID],
> CONSTRAINT [DF_tCompany_SalespersonID] DEFAULT (0) FOR [SalespersonID],
> CONSTRAINT [DF_tCompany_CustomerCompanyID] DEFAULT (0) FOR
> [CustomerCompanyID],
> CONSTRAINT [DF_tCompany_Shipper] DEFAULT (0) FOR [Shipper],
> CONSTRAINT [DF_tCompany_Receiver] DEFAULT (0) FOR [Receiver],
> CONSTRAINT [DF_tCompany_ThirdPartyBilling] DEFAULT (0) FOR
> [ThirdPartyBilling],
> CONSTRAINT [DF_tCompany_Cartage] DEFAULT (0) FOR [Cartage],
> CONSTRAINT [DF_tCompany_Customer] DEFAULT (0) FOR [Customer],
> CONSTRAINT [DF_tCompany_Payee] DEFAULT (0) FOR [Payee],
> CONSTRAINT [DF_tCompany_Company] DEFAULT (0) FOR [Company],
> CONSTRAINT [DF_tCompany_Hide] DEFAULT (0) FOR [Hide],
> CONSTRAINT [DF_tCompany_AutoSendEmail] DEFAULT (0) FOR [AutoSendEmail],
> CONSTRAINT [DF_tCompany_CommericalAirApproved] DEFAULT (0) FOR
> [CommericalAirApproved],
> CONSTRAINT [DF_tCompany_DepositACH_1] DEFAULT (0) FOR [DepositACH],
> CONSTRAINT [DF_tCompany_International_1] DEFAULT (0) FOR [International],
> CONSTRAINT [DF_tCompany_AutoTrackStatus] DEFAULT (0) FOR [AutoTrackStatus],
> CONSTRAINT [DF_tCompany_AutoTrackCharges] DEFAULT (0) FOR [AutoTrackCharges],
> CONSTRAINT [DF_tCompany_OpsPersonID] DEFAULT (0) FOR [OpsPersonID],
> CONSTRAINT [DF_tCompany_RateSpreadsheet] DEFAULT (0) FOR [RateSpreadsheet]
> GO
> CREATE INDEX [IX_tCompany_NameNick] ON [dbo].[tCompany]([NameNick]) WITH
> FILLFACTOR = 90 ON [CompanyX]
> GO
>|||Alehandro,
Thank you for the quick reply. I already had read the two articles you
refer to plus at least half a dozen more on deadlocks in the MS knowledge
base. Unfortunately they do not answer the question that is the root of the
problem. Why is the optimizer forcing the entire table to be locked when only
a simple SELECT query is issued?
At most, only a page lock should be issued. No other tables in my SQL
database exibit this problem. I have have worked with a dozen different
databases over the last 20 years and I have yet to run up against this type
of locking problem.
So perhaps I mistitled this posting, the correct title should be "How can a
simple select statement lock an entire table?"
If you can find a Knowledge Base artical that answers that question and
gives me a way to avoid it, my problem would be solved for good. You help in
this matter is much appreciated.
Many Thanks in Advance.
----
"Alejandro Mesa" wrote:
> Troubleshooting Deadlocks
> http://msdn2.microsoft.com/en-us/library/Aa937573(SQL.80).aspx
> SQL Server technical bulletin - How to resolve a deadlock
> http://support.microsoft.com/kb/832524/
>
> AMB
> "FloMiester" wrote:
> > I am using SQL Server 2000 sp4 and have a single table that is now getting a
> > deadlock problem after 3 years of use. It started when the number of rows
> > went past 16K.
> >
> > The problem is that a query returning more than ~500 rows against the table
> > causes the optimizer to use a single "Shared Table" lock for the query
> > instead of its normal behavior of issuing two locks: 1 a Page Shared Lock (S)
> > and 2 a Table Intent Shared Lock (IS). The single Shared Table lock causes
> > any other clients wishing to modify the table to deadlock waiting for the
> > table lock to be freed up.
> >
> > FYI. I have other tables in the same database over 16K rows that do not have
> > this problem.
> >
> > I have manually created the table on a test database on another SQL
> > database, on another machine, copied the data and the same problem occurred.
> >
> > Removing a few thousand rows fixes the problem, but is an unaccceptable
> > solution.
> >
> > A temporary fix is to change the fill factor on the primary key index to a
> > lower number, but after a few days and more rows are added, the deadlock
> > occurs again, until the fill factor is lowered again. I am at 50% now.
> >
> > I tried removing the cluster on the primary key index and moving the index
> > into another table space, but the same problem occurs once again if I shrink
> > the tables space the table is in (basically removing any fill space).
> >
> > Removing an unneeded column also temporarily helped, but perhaps something
> > else was going on that made the locking issue go away temporarily.
> >
> > So it seems that for this particular table the optimizer is taking into
> > consideration how much fill space is in the table to choose which type of
> > locking scheme to use. I can produce this problem simply using the
> > Enterprise Manager and opening a "SELECT *" query on the table and using the
> > "Current Activity" window to view the locks placed on the table.
> >
> > I am in desperate need of a proper fix for this problem. Does anyone have
> > any ideas?
> >
> > FYI: I also noticed what appears to be a bug with Enterprise Manager. When
> > looking at the â'Locks / Process IDâ' or the â'Locks / Objectâ' screens for locks
> > issued by several "SELECT *" queries that were still open, the â'Indexâ' column
> > showed the same primary key index being used for the â'Page lockâ' for all the
> > queries still open. The index name being displayed would be the primary key
> > index for the table of the last query I executed in Enterprise Manager.
> >
> >
> > Table DDL:
> >
> > CREATE TABLE [dbo].[tCompany] (
> > [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
> > [Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > [NameNick] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > [AddressLine1] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > [AddressLine2] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > [City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > [State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > [Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > [Country] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > [Telephone] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > [Telephone2] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > [Fax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > [AlertFax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > [ContactPerson] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > [ContactPerson2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > [AccountNum] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > [BillToAttention] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > [Airport] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > [Email] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > [Password] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
> > [LastUsedTime] [datetime] NOT NULL ,
> > [OurCompanyID] [int] NOT NULL ,
> > [SalespersonID] [int] NOT NULL ,
> > [CustomerCompanyID] [int] NOT NULL ,
> > [ParentCompanyID] [int] NULL ,
> > [Shipper] [smallint] NOT NULL ,
> > [Receiver] [smallint] NOT NULL ,
> > [ThirdPartyBilling] [smallint] NOT NULL ,
> > [Cartage] [smallint] NOT NULL ,
> > [Customer] [smallint] NOT NULL ,
> > [Payee] [smallint] NOT NULL ,
> > [Company] [smallint] NOT NULL ,
> > [Hide] [smallint] NOT NULL ,
> > [AutoSendEmail] [smallint] NOT NULL ,
> > [CommericalAirApproved] [smallint] NOT NULL ,
> > [CarrierInsuranceAmount] [money] NULL ,
> > [CarrierInsuranceExpires] [datetime] NULL ,
> > [Notes] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > [WebPageHome] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > [WebPageTrackPRO] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > NULL ,
> > [WebPageTrackFlight] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > NULL ,
> > [SOPNotes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > [WebPageTrackPROMethod] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CS_AS
> > NULL ,
> > [WebPassword] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > [WebLoginID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > [DepositACH] [smallint] NOT NULL ,
> > [Province] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > [International] [smallint] NOT NULL ,
> > [AutoTrackStatus] [smallint] NOT NULL ,
> > [AutoTrackCharges] [smallint] NOT NULL ,
> > [InvoiceTerms] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > [OpsPersonID] [int] NOT NULL ,
> > [RateSpreadsheet] [smallint] NOT NULL
> > ) ON [Company]
> > GO
> >
> > ALTER TABLE [dbo].[tCompany] WITH NOCHECK ADD
> > CONSTRAINT [PK_tCompany] PRIMARY KEY CLUSTERED
> > (
> > [CompanyID]
> > ) WITH FILLFACTOR = 90 ON [Company]
> > GO
> >
> > ALTER TABLE [dbo].[tCompany] ADD
> > CONSTRAINT [DF_tCompany_Name] DEFAULT ('N/A') FOR [Name],
> > CONSTRAINT [DF_tCompany_NameNick] DEFAULT ('N/A') FOR [NameNick],
> > CONSTRAINT [DF_tCompany_WebClient] DEFAULT ('none') FOR [Password],
> > CONSTRAINT [DF_tCompany_CreateDate] DEFAULT (getdate()) FOR [LastUsedTime],
> > CONSTRAINT [DF_tCompany_OurCompanyID] DEFAULT (3) FOR [OurCompanyID],
> > CONSTRAINT [DF_tCompany_SalespersonID] DEFAULT (0) FOR [SalespersonID],
> > CONSTRAINT [DF_tCompany_CustomerCompanyID] DEFAULT (0) FOR
> > [CustomerCompanyID],
> > CONSTRAINT [DF_tCompany_Shipper] DEFAULT (0) FOR [Shipper],
> > CONSTRAINT [DF_tCompany_Receiver] DEFAULT (0) FOR [Receiver],
> > CONSTRAINT [DF_tCompany_ThirdPartyBilling] DEFAULT (0) FOR
> > [ThirdPartyBilling],
> > CONSTRAINT [DF_tCompany_Cartage] DEFAULT (0) FOR [Cartage],
> > CONSTRAINT [DF_tCompany_Customer] DEFAULT (0) FOR [Customer],
> > CONSTRAINT [DF_tCompany_Payee] DEFAULT (0) FOR [Payee],
> > CONSTRAINT [DF_tCompany_Company] DEFAULT (0) FOR [Company],
> > CONSTRAINT [DF_tCompany_Hide] DEFAULT (0) FOR [Hide],
> > CONSTRAINT [DF_tCompany_AutoSendEmail] DEFAULT (0) FOR [AutoSendEmail],
> > CONSTRAINT [DF_tCompany_CommericalAirApproved] DEFAULT (0) FOR
> > [CommericalAirApproved],
> > CONSTRAINT [DF_tCompany_DepositACH_1] DEFAULT (0) FOR [DepositACH],
> > CONSTRAINT [DF_tCompany_International_1] DEFAULT (0) FOR [International],
> > CONSTRAINT [DF_tCompany_AutoTrackStatus] DEFAULT (0) FOR [AutoTrackStatus],
> > CONSTRAINT [DF_tCompany_AutoTrackCharges] DEFAULT (0) FOR [AutoTrackCharges],
> > CONSTRAINT [DF_tCompany_OpsPersonID] DEFAULT (0) FOR [OpsPersonID],
> > CONSTRAINT [DF_tCompany_RateSpreadsheet] DEFAULT (0) FOR [RateSpreadsheet]
> > GO
> >
> > CREATE INDEX [IX_tCompany_NameNick] ON [dbo].[tCompany]([NameNick]) WITH
> > FILLFACTOR = 90 ON [CompanyX]
> > GO
> >
> >|||FloMiester,
I will suggest to post table structe, including constraints and indexes, the
"select" statement and the execution plan (see "set showplan_text / xml on"
in BOL)
Depend on the indexes, the distribution of values in the index key, and the
filter expression being used, the query optimizer can decide to scan the
table (heap or not) and this could cause a lock escalation.
AMB
"FloMiester" wrote:
> Alehandro,
> Thank you for the quick reply. I already had read the two articles you
> refer to plus at least half a dozen more on deadlocks in the MS knowledge
> base. Unfortunately they do not answer the question that is the root of the
> problem. Why is the optimizer forcing the entire table to be locked when only
> a simple SELECT query is issued?
> At most, only a page lock should be issued. No other tables in my SQL
> database exibit this problem. I have have worked with a dozen different
> databases over the last 20 years and I have yet to run up against this type
> of locking problem.
> So perhaps I mistitled this posting, the correct title should be "How can a
> simple select statement lock an entire table?"
> If you can find a Knowledge Base artical that answers that question and
> gives me a way to avoid it, my problem would be solved for good. You help in
> this matter is much appreciated.
> Many Thanks in Advance.
> ----
> "Alejandro Mesa" wrote:
> > Troubleshooting Deadlocks
> > http://msdn2.microsoft.com/en-us/library/Aa937573(SQL.80).aspx
> >
> > SQL Server technical bulletin - How to resolve a deadlock
> > http://support.microsoft.com/kb/832524/
> >
> >
> > AMB
> >
> > "FloMiester" wrote:
> >
> > > I am using SQL Server 2000 sp4 and have a single table that is now getting a
> > > deadlock problem after 3 years of use. It started when the number of rows
> > > went past 16K.
> > >
> > > The problem is that a query returning more than ~500 rows against the table
> > > causes the optimizer to use a single "Shared Table" lock for the query
> > > instead of its normal behavior of issuing two locks: 1 a Page Shared Lock (S)
> > > and 2 a Table Intent Shared Lock (IS). The single Shared Table lock causes
> > > any other clients wishing to modify the table to deadlock waiting for the
> > > table lock to be freed up.
> > >
> > > FYI. I have other tables in the same database over 16K rows that do not have
> > > this problem.
> > >
> > > I have manually created the table on a test database on another SQL
> > > database, on another machine, copied the data and the same problem occurred.
> > >
> > > Removing a few thousand rows fixes the problem, but is an unaccceptable
> > > solution.
> > >
> > > A temporary fix is to change the fill factor on the primary key index to a
> > > lower number, but after a few days and more rows are added, the deadlock
> > > occurs again, until the fill factor is lowered again. I am at 50% now.
> > >
> > > I tried removing the cluster on the primary key index and moving the index
> > > into another table space, but the same problem occurs once again if I shrink
> > > the tables space the table is in (basically removing any fill space).
> > >
> > > Removing an unneeded column also temporarily helped, but perhaps something
> > > else was going on that made the locking issue go away temporarily.
> > >
> > > So it seems that for this particular table the optimizer is taking into
> > > consideration how much fill space is in the table to choose which type of
> > > locking scheme to use. I can produce this problem simply using the
> > > Enterprise Manager and opening a "SELECT *" query on the table and using the
> > > "Current Activity" window to view the locks placed on the table.
> > >
> > > I am in desperate need of a proper fix for this problem. Does anyone have
> > > any ideas?
> > >
> > > FYI: I also noticed what appears to be a bug with Enterprise Manager. When
> > > looking at the â'Locks / Process IDâ' or the â'Locks / Objectâ' screens for locks
> > > issued by several "SELECT *" queries that were still open, the â'Indexâ' column
> > > showed the same primary key index being used for the â'Page lockâ' for all the
> > > queries still open. The index name being displayed would be the primary key
> > > index for the table of the last query I executed in Enterprise Manager.
> > >
> > >
> > > Table DDL:
> > >
> > > CREATE TABLE [dbo].[tCompany] (
> > > [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
> > > [Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > > [NameNick] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > > [AddressLine1] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > [AddressLine2] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > [City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > [State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > [Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > [Country] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > [Telephone] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > [Telephone2] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > [Fax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > [AlertFax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > [ContactPerson] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > [ContactPerson2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > [AccountNum] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > [BillToAttention] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > [Airport] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > [Email] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > [Password] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
> > > [LastUsedTime] [datetime] NOT NULL ,
> > > [OurCompanyID] [int] NOT NULL ,
> > > [SalespersonID] [int] NOT NULL ,
> > > [CustomerCompanyID] [int] NOT NULL ,
> > > [ParentCompanyID] [int] NULL ,
> > > [Shipper] [smallint] NOT NULL ,
> > > [Receiver] [smallint] NOT NULL ,
> > > [ThirdPartyBilling] [smallint] NOT NULL ,
> > > [Cartage] [smallint] NOT NULL ,
> > > [Customer] [smallint] NOT NULL ,
> > > [Payee] [smallint] NOT NULL ,
> > > [Company] [smallint] NOT NULL ,
> > > [Hide] [smallint] NOT NULL ,
> > > [AutoSendEmail] [smallint] NOT NULL ,
> > > [CommericalAirApproved] [smallint] NOT NULL ,
> > > [CarrierInsuranceAmount] [money] NULL ,
> > > [CarrierInsuranceExpires] [datetime] NULL ,
> > > [Notes] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > [WebPageHome] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > [WebPageTrackPRO] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > NULL ,
> > > [WebPageTrackFlight] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > NULL ,
> > > [SOPNotes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > [WebPageTrackPROMethod] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > NULL ,
> > > [WebPassword] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > [WebLoginID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > [DepositACH] [smallint] NOT NULL ,
> > > [Province] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > [International] [smallint] NOT NULL ,
> > > [AutoTrackStatus] [smallint] NOT NULL ,
> > > [AutoTrackCharges] [smallint] NOT NULL ,
> > > [InvoiceTerms] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > [OpsPersonID] [int] NOT NULL ,
> > > [RateSpreadsheet] [smallint] NOT NULL
> > > ) ON [Company]
> > > GO
> > >
> > > ALTER TABLE [dbo].[tCompany] WITH NOCHECK ADD
> > > CONSTRAINT [PK_tCompany] PRIMARY KEY CLUSTERED
> > > (
> > > [CompanyID]
> > > ) WITH FILLFACTOR = 90 ON [Company]
> > > GO
> > >
> > > ALTER TABLE [dbo].[tCompany] ADD
> > > CONSTRAINT [DF_tCompany_Name] DEFAULT ('N/A') FOR [Name],
> > > CONSTRAINT [DF_tCompany_NameNick] DEFAULT ('N/A') FOR [NameNick],
> > > CONSTRAINT [DF_tCompany_WebClient] DEFAULT ('none') FOR [Password],
> > > CONSTRAINT [DF_tCompany_CreateDate] DEFAULT (getdate()) FOR [LastUsedTime],
> > > CONSTRAINT [DF_tCompany_OurCompanyID] DEFAULT (3) FOR [OurCompanyID],
> > > CONSTRAINT [DF_tCompany_SalespersonID] DEFAULT (0) FOR [SalespersonID],
> > > CONSTRAINT [DF_tCompany_CustomerCompanyID] DEFAULT (0) FOR
> > > [CustomerCompanyID],
> > > CONSTRAINT [DF_tCompany_Shipper] DEFAULT (0) FOR [Shipper],
> > > CONSTRAINT [DF_tCompany_Receiver] DEFAULT (0) FOR [Receiver],
> > > CONSTRAINT [DF_tCompany_ThirdPartyBilling] DEFAULT (0) FOR
> > > [ThirdPartyBilling],
> > > CONSTRAINT [DF_tCompany_Cartage] DEFAULT (0) FOR [Cartage],
> > > CONSTRAINT [DF_tCompany_Customer] DEFAULT (0) FOR [Customer],
> > > CONSTRAINT [DF_tCompany_Payee] DEFAULT (0) FOR [Payee],
> > > CONSTRAINT [DF_tCompany_Company] DEFAULT (0) FOR [Company],
> > > CONSTRAINT [DF_tCompany_Hide] DEFAULT (0) FOR [Hide],
> > > CONSTRAINT [DF_tCompany_AutoSendEmail] DEFAULT (0) FOR [AutoSendEmail],
> > > CONSTRAINT [DF_tCompany_CommericalAirApproved] DEFAULT (0) FOR
> > > [CommericalAirApproved],
> > > CONSTRAINT [DF_tCompany_DepositACH_1] DEFAULT (0) FOR [DepositACH],
> > > CONSTRAINT [DF_tCompany_International_1] DEFAULT (0) FOR [International],
> > > CONSTRAINT [DF_tCompany_AutoTrackStatus] DEFAULT (0) FOR [AutoTrackStatus],
> > > CONSTRAINT [DF_tCompany_AutoTrackCharges] DEFAULT (0) FOR [AutoTrackCharges],
> > > CONSTRAINT [DF_tCompany_OpsPersonID] DEFAULT (0) FOR [OpsPersonID],
> > > CONSTRAINT [DF_tCompany_RateSpreadsheet] DEFAULT (0) FOR [RateSpreadsheet]
> > > GO
> > >
> > > CREATE INDEX [IX_tCompany_NameNick] ON [dbo].[tCompany]([NameNick]) WITH
> > > FILLFACTOR = 90 ON [CompanyX]
> > > GO
> > >
> > >|||Alejandro,
Thanks again for the quick reply. I have already included the table
structure, etc. in my first post, perhaps you need to read it entirely to
help diagnose the problem.
I ran the query "SELECT * FROM dbo.tCompany;". The showplan output is as
follows:
|--Clustered Index
Scan(OBJECT:([FreightMgmt].[dbo].[tCompany].[PK_tCompany]))
This showplan result is identical in another similar table in my database
which does not have the table locking problem:
SELECT * from dbo.tStatusLog;
|--Clustered Index
Scan(OBJECT:([FreightMgmt].[dbo].[tStatusLog].[PK_tStatusLog]))
Please advise on the next steps I need to take.
Thanks!
"Alejandro Mesa" wrote:
> FloMiester,
> I will suggest to post table structe, including constraints and indexes, the
> "select" statement and the execution plan (see "set showplan_text / xml on"
> in BOL)
> Depend on the indexes, the distribution of values in the index key, and the
> filter expression being used, the query optimizer can decide to scan the
> table (heap or not) and this could cause a lock escalation.
> AMB
> "FloMiester" wrote:
> > Alehandro,
> >
> > Thank you for the quick reply. I already had read the two articles you
> > refer to plus at least half a dozen more on deadlocks in the MS knowledge
> > base. Unfortunately they do not answer the question that is the root of the
> > problem. Why is the optimizer forcing the entire table to be locked when only
> > a simple SELECT query is issued?
> >
> > At most, only a page lock should be issued. No other tables in my SQL
> > database exibit this problem. I have have worked with a dozen different
> > databases over the last 20 years and I have yet to run up against this type
> > of locking problem.
> >
> > So perhaps I mistitled this posting, the correct title should be "How can a
> > simple select statement lock an entire table?"
> >
> > If you can find a Knowledge Base artical that answers that question and
> > gives me a way to avoid it, my problem would be solved for good. You help in
> > this matter is much appreciated.
> >
> > Many Thanks in Advance.
> >
> > ----
> >
> > "Alejandro Mesa" wrote:
> >
> > > Troubleshooting Deadlocks
> > > http://msdn2.microsoft.com/en-us/library/Aa937573(SQL.80).aspx
> > >
> > > SQL Server technical bulletin - How to resolve a deadlock
> > > http://support.microsoft.com/kb/832524/
> > >
> > >
> > > AMB
> > >
> > > "FloMiester" wrote:
> > >
> > > > I am using SQL Server 2000 sp4 and have a single table that is now getting a
> > > > deadlock problem after 3 years of use. It started when the number of rows
> > > > went past 16K.
> > > >
> > > > The problem is that a query returning more than ~500 rows against the table
> > > > causes the optimizer to use a single "Shared Table" lock for the query
> > > > instead of its normal behavior of issuing two locks: 1 a Page Shared Lock (S)
> > > > and 2 a Table Intent Shared Lock (IS). The single Shared Table lock causes
> > > > any other clients wishing to modify the table to deadlock waiting for the
> > > > table lock to be freed up.
> > > >
> > > > FYI. I have other tables in the same database over 16K rows that do not have
> > > > this problem.
> > > >
> > > > I have manually created the table on a test database on another SQL
> > > > database, on another machine, copied the data and the same problem occurred.
> > > >
> > > > Removing a few thousand rows fixes the problem, but is an unaccceptable
> > > > solution.
> > > >
> > > > A temporary fix is to change the fill factor on the primary key index to a
> > > > lower number, but after a few days and more rows are added, the deadlock
> > > > occurs again, until the fill factor is lowered again. I am at 50% now.
> > > >
> > > > I tried removing the cluster on the primary key index and moving the index
> > > > into another table space, but the same problem occurs once again if I shrink
> > > > the tables space the table is in (basically removing any fill space).
> > > >
> > > > Removing an unneeded column also temporarily helped, but perhaps something
> > > > else was going on that made the locking issue go away temporarily.
> > > >
> > > > So it seems that for this particular table the optimizer is taking into
> > > > consideration how much fill space is in the table to choose which type of
> > > > locking scheme to use. I can produce this problem simply using the
> > > > Enterprise Manager and opening a "SELECT *" query on the table and using the
> > > > "Current Activity" window to view the locks placed on the table.
> > > >
> > > > I am in desperate need of a proper fix for this problem. Does anyone have
> > > > any ideas?
> > > >
> > > > FYI: I also noticed what appears to be a bug with Enterprise Manager. When
> > > > looking at the â'Locks / Process IDâ' or the â'Locks / Objectâ' screens for locks
> > > > issued by several "SELECT *" queries that were still open, the â'Indexâ' column
> > > > showed the same primary key index being used for the â'Page lockâ' for all the
> > > > queries still open. The index name being displayed would be the primary key
> > > > index for the table of the last query I executed in Enterprise Manager.
> > > >
> > > >
> > > > Table DDL:
> > > >
> > > > CREATE TABLE [dbo].[tCompany] (
> > > > [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
> > > > [Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > > > [NameNick] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > > > [AddressLine1] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > [AddressLine2] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > [City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > [State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > [Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > [Country] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > [Telephone] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > [Telephone2] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > [Fax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > [AlertFax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > [ContactPerson] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > [ContactPerson2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > [AccountNum] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > [BillToAttention] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > [Airport] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > [Email] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > [Password] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
> > > > [LastUsedTime] [datetime] NOT NULL ,
> > > > [OurCompanyID] [int] NOT NULL ,
> > > > [SalespersonID] [int] NOT NULL ,
> > > > [CustomerCompanyID] [int] NOT NULL ,
> > > > [ParentCompanyID] [int] NULL ,
> > > > [Shipper] [smallint] NOT NULL ,
> > > > [Receiver] [smallint] NOT NULL ,
> > > > [ThirdPartyBilling] [smallint] NOT NULL ,
> > > > [Cartage] [smallint] NOT NULL ,
> > > > [Customer] [smallint] NOT NULL ,
> > > > [Payee] [smallint] NOT NULL ,
> > > > [Company] [smallint] NOT NULL ,
> > > > [Hide] [smallint] NOT NULL ,
> > > > [AutoSendEmail] [smallint] NOT NULL ,
> > > > [CommericalAirApproved] [smallint] NOT NULL ,
> > > > [CarrierInsuranceAmount] [money] NULL ,
> > > > [CarrierInsuranceExpires] [datetime] NULL ,
> > > > [Notes] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > [WebPageHome] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > [WebPageTrackPRO] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > NULL ,
> > > > [WebPageTrackFlight] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > NULL ,
> > > > [SOPNotes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > [WebPageTrackPROMethod] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > NULL ,
> > > > [WebPassword] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > [WebLoginID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > [DepositACH] [smallint] NOT NULL ,
> > > > [Province] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > [International] [smallint] NOT NULL ,
> > > > [AutoTrackStatus] [smallint] NOT NULL ,
> > > > [AutoTrackCharges] [smallint] NOT NULL ,
> > > > [InvoiceTerms] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > [OpsPersonID] [int] NOT NULL ,
> > > > [RateSpreadsheet] [smallint] NOT NULL
> > > > ) ON [Company]
> > > > GO
> > > >
> > > > ALTER TABLE [dbo].[tCompany] WITH NOCHECK ADD
> > > > CONSTRAINT [PK_tCompany] PRIMARY KEY CLUSTERED
> > > > (
> > > > [CompanyID]
> > > > ) WITH FILLFACTOR = 90 ON [Company]
> > > > GO
> > > >
> > > > ALTER TABLE [dbo].[tCompany] ADD
> > > > CONSTRAINT [DF_tCompany_Name] DEFAULT ('N/A') FOR [Name],
> > > > CONSTRAINT [DF_tCompany_NameNick] DEFAULT ('N/A') FOR [NameNick],
> > > > CONSTRAINT [DF_tCompany_WebClient] DEFAULT ('none') FOR [Password],
> > > > CONSTRAINT [DF_tCompany_CreateDate] DEFAULT (getdate()) FOR [LastUsedTime],
> > > > CONSTRAINT [DF_tCompany_OurCompanyID] DEFAULT (3) FOR [OurCompanyID],
> > > > CONSTRAINT [DF_tCompany_SalespersonID] DEFAULT (0) FOR [SalespersonID],
> > > > CONSTRAINT [DF_tCompany_CustomerCompanyID] DEFAULT (0) FOR
> > > > [CustomerCompanyID],
> > > > CONSTRAINT [DF_tCompany_Shipper] DEFAULT (0) FOR [Shipper],
> > > > CONSTRAINT [DF_tCompany_Receiver] DEFAULT (0) FOR [Receiver],
> > > > CONSTRAINT [DF_tCompany_ThirdPartyBilling] DEFAULT (0) FOR
> > > > [ThirdPartyBilling],
> > > > CONSTRAINT [DF_tCompany_Cartage] DEFAULT (0) FOR [Cartage],
> > > > CONSTRAINT [DF_tCompany_Customer] DEFAULT (0) FOR [Customer],
> > > > CONSTRAINT [DF_tCompany_Payee] DEFAULT (0) FOR [Payee],
> > > > CONSTRAINT [DF_tCompany_Company] DEFAULT (0) FOR [Company],
> > > > CONSTRAINT [DF_tCompany_Hide] DEFAULT (0) FOR [Hide],
> > > > CONSTRAINT [DF_tCompany_AutoSendEmail] DEFAULT (0) FOR [AutoSendEmail],
> > > > CONSTRAINT [DF_tCompany_CommericalAirApproved] DEFAULT (0) FOR
> > > > [CommericalAirApproved],
> > > > CONSTRAINT [DF_tCompany_DepositACH_1] DEFAULT (0) FOR [DepositACH],
> > > > CONSTRAINT [DF_tCompany_International_1] DEFAULT (0) FOR [International],
> > > > CONSTRAINT [DF_tCompany_AutoTrackStatus] DEFAULT (0) FOR [AutoTrackStatus],
> > > > CONSTRAINT [DF_tCompany_AutoTrackCharges] DEFAULT (0) FOR [AutoTrackCharges],
> > > > CONSTRAINT [DF_tCompany_OpsPersonID] DEFAULT (0) FOR [OpsPersonID],
> > > > CONSTRAINT [DF_tCompany_RateSpreadsheet] DEFAULT (0) FOR [RateSpreadsheet]
> > > > GO
> > > >
> > > > CREATE INDEX [IX_tCompany_NameNick] ON [dbo].[tCompany]([NameNick]) WITH
> > > > FILLFACTOR = 90 ON [CompanyX]
> > > > GO
> > > >
> > > >|||FloMiester,
have you identified the other statement involved in the deadlock, can you
post it?
AMB
"FloMiester" wrote:
> Alejandro,
> Thanks again for the quick reply. I have already included the table
> structure, etc. in my first post, perhaps you need to read it entirely to
> help diagnose the problem.
> I ran the query "SELECT * FROM dbo.tCompany;". The showplan output is as
> follows:
> |--Clustered Index
> Scan(OBJECT:([FreightMgmt].[dbo].[tCompany].[PK_tCompany]))
> This showplan result is identical in another similar table in my database
> which does not have the table locking problem:
> SELECT * from dbo.tStatusLog;
> |--Clustered Index
> Scan(OBJECT:([FreightMgmt].[dbo].[tStatusLog].[PK_tStatusLog]))
> Please advise on the next steps I need to take.
> Thanks!
>
> "Alejandro Mesa" wrote:
> > FloMiester,
> >
> > I will suggest to post table structe, including constraints and indexes, the
> > "select" statement and the execution plan (see "set showplan_text / xml on"
> > in BOL)
> >
> > Depend on the indexes, the distribution of values in the index key, and the
> > filter expression being used, the query optimizer can decide to scan the
> > table (heap or not) and this could cause a lock escalation.
> >
> > AMB
> >
> > "FloMiester" wrote:
> >
> > > Alehandro,
> > >
> > > Thank you for the quick reply. I already had read the two articles you
> > > refer to plus at least half a dozen more on deadlocks in the MS knowledge
> > > base. Unfortunately they do not answer the question that is the root of the
> > > problem. Why is the optimizer forcing the entire table to be locked when only
> > > a simple SELECT query is issued?
> > >
> > > At most, only a page lock should be issued. No other tables in my SQL
> > > database exibit this problem. I have have worked with a dozen different
> > > databases over the last 20 years and I have yet to run up against this type
> > > of locking problem.
> > >
> > > So perhaps I mistitled this posting, the correct title should be "How can a
> > > simple select statement lock an entire table?"
> > >
> > > If you can find a Knowledge Base artical that answers that question and
> > > gives me a way to avoid it, my problem would be solved for good. You help in
> > > this matter is much appreciated.
> > >
> > > Many Thanks in Advance.
> > >
> > > ----
> > >
> > > "Alejandro Mesa" wrote:
> > >
> > > > Troubleshooting Deadlocks
> > > > http://msdn2.microsoft.com/en-us/library/Aa937573(SQL.80).aspx
> > > >
> > > > SQL Server technical bulletin - How to resolve a deadlock
> > > > http://support.microsoft.com/kb/832524/
> > > >
> > > >
> > > > AMB
> > > >
> > > > "FloMiester" wrote:
> > > >
> > > > > I am using SQL Server 2000 sp4 and have a single table that is now getting a
> > > > > deadlock problem after 3 years of use. It started when the number of rows
> > > > > went past 16K.
> > > > >
> > > > > The problem is that a query returning more than ~500 rows against the table
> > > > > causes the optimizer to use a single "Shared Table" lock for the query
> > > > > instead of its normal behavior of issuing two locks: 1 a Page Shared Lock (S)
> > > > > and 2 a Table Intent Shared Lock (IS). The single Shared Table lock causes
> > > > > any other clients wishing to modify the table to deadlock waiting for the
> > > > > table lock to be freed up.
> > > > >
> > > > > FYI. I have other tables in the same database over 16K rows that do not have
> > > > > this problem.
> > > > >
> > > > > I have manually created the table on a test database on another SQL
> > > > > database, on another machine, copied the data and the same problem occurred.
> > > > >
> > > > > Removing a few thousand rows fixes the problem, but is an unaccceptable
> > > > > solution.
> > > > >
> > > > > A temporary fix is to change the fill factor on the primary key index to a
> > > > > lower number, but after a few days and more rows are added, the deadlock
> > > > > occurs again, until the fill factor is lowered again. I am at 50% now.
> > > > >
> > > > > I tried removing the cluster on the primary key index and moving the index
> > > > > into another table space, but the same problem occurs once again if I shrink
> > > > > the tables space the table is in (basically removing any fill space).
> > > > >
> > > > > Removing an unneeded column also temporarily helped, but perhaps something
> > > > > else was going on that made the locking issue go away temporarily.
> > > > >
> > > > > So it seems that for this particular table the optimizer is taking into
> > > > > consideration how much fill space is in the table to choose which type of
> > > > > locking scheme to use. I can produce this problem simply using the
> > > > > Enterprise Manager and opening a "SELECT *" query on the table and using the
> > > > > "Current Activity" window to view the locks placed on the table.
> > > > >
> > > > > I am in desperate need of a proper fix for this problem. Does anyone have
> > > > > any ideas?
> > > > >
> > > > > FYI: I also noticed what appears to be a bug with Enterprise Manager. When
> > > > > looking at the â'Locks / Process IDâ' or the â'Locks / Objectâ' screens for locks
> > > > > issued by several "SELECT *" queries that were still open, the â'Indexâ' column
> > > > > showed the same primary key index being used for the â'Page lockâ' for all the
> > > > > queries still open. The index name being displayed would be the primary key
> > > > > index for the table of the last query I executed in Enterprise Manager.
> > > > >
> > > > >
> > > > > Table DDL:
> > > > >
> > > > > CREATE TABLE [dbo].[tCompany] (
> > > > > [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
> > > > > [Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > > > > [NameNick] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > > > > [AddressLine1] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > [AddressLine2] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > [City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > [State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > [Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > [Country] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > [Telephone] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > [Telephone2] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > [Fax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > [AlertFax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > [ContactPerson] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > [ContactPerson2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > [AccountNum] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > [BillToAttention] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > [Airport] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > [Email] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > [Password] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
> > > > > [LastUsedTime] [datetime] NOT NULL ,
> > > > > [OurCompanyID] [int] NOT NULL ,
> > > > > [SalespersonID] [int] NOT NULL ,
> > > > > [CustomerCompanyID] [int] NOT NULL ,
> > > > > [ParentCompanyID] [int] NULL ,
> > > > > [Shipper] [smallint] NOT NULL ,
> > > > > [Receiver] [smallint] NOT NULL ,
> > > > > [ThirdPartyBilling] [smallint] NOT NULL ,
> > > > > [Cartage] [smallint] NOT NULL ,
> > > > > [Customer] [smallint] NOT NULL ,
> > > > > [Payee] [smallint] NOT NULL ,
> > > > > [Company] [smallint] NOT NULL ,
> > > > > [Hide] [smallint] NOT NULL ,
> > > > > [AutoSendEmail] [smallint] NOT NULL ,
> > > > > [CommericalAirApproved] [smallint] NOT NULL ,
> > > > > [CarrierInsuranceAmount] [money] NULL ,
> > > > > [CarrierInsuranceExpires] [datetime] NULL ,
> > > > > [Notes] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > [WebPageHome] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > [WebPageTrackPRO] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > NULL ,
> > > > > [WebPageTrackFlight] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > NULL ,
> > > > > [SOPNotes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > [WebPageTrackPROMethod] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > NULL ,
> > > > > [WebPassword] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > [WebLoginID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > [DepositACH] [smallint] NOT NULL ,
> > > > > [Province] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > [International] [smallint] NOT NULL ,
> > > > > [AutoTrackStatus] [smallint] NOT NULL ,
> > > > > [AutoTrackCharges] [smallint] NOT NULL ,
> > > > > [InvoiceTerms] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > [OpsPersonID] [int] NOT NULL ,
> > > > > [RateSpreadsheet] [smallint] NOT NULL
> > > > > ) ON [Company]
> > > > > GO
> > > > >
> > > > > ALTER TABLE [dbo].[tCompany] WITH NOCHECK ADD
> > > > > CONSTRAINT [PK_tCompany] PRIMARY KEY CLUSTERED
> > > > > (
> > > > > [CompanyID]
> > > > > ) WITH FILLFACTOR = 90 ON [Company]
> > > > > GO
> > > > >
> > > > > ALTER TABLE [dbo].[tCompany] ADD
> > > > > CONSTRAINT [DF_tCompany_Name] DEFAULT ('N/A') FOR [Name],
> > > > > CONSTRAINT [DF_tCompany_NameNick] DEFAULT ('N/A') FOR [NameNick],
> > > > > CONSTRAINT [DF_tCompany_WebClient] DEFAULT ('none') FOR [Password],
> > > > > CONSTRAINT [DF_tCompany_CreateDate] DEFAULT (getdate()) FOR [LastUsedTime],
> > > > > CONSTRAINT [DF_tCompany_OurCompanyID] DEFAULT (3) FOR [OurCompanyID],
> > > > > CONSTRAINT [DF_tCompany_SalespersonID] DEFAULT (0) FOR [SalespersonID],
> > > > > CONSTRAINT [DF_tCompany_CustomerCompanyID] DEFAULT (0) FOR
> > > > > [CustomerCompanyID],
> > > > > CONSTRAINT [DF_tCompany_Shipper] DEFAULT (0) FOR [Shipper],
> > > > > CONSTRAINT [DF_tCompany_Receiver] DEFAULT (0) FOR [Receiver],
> > > > > CONSTRAINT [DF_tCompany_ThirdPartyBilling] DEFAULT (0) FOR
> > > > > [ThirdPartyBilling],
> > > > > CONSTRAINT [DF_tCompany_Cartage] DEFAULT (0) FOR [Cartage],
> > > > > CONSTRAINT [DF_tCompany_Customer] DEFAULT (0) FOR [Customer],
> > > > > CONSTRAINT [DF_tCompany_Payee] DEFAULT (0) FOR [Payee],
> > > > > CONSTRAINT [DF_tCompany_Company] DEFAULT (0) FOR [Company],
> > > > > CONSTRAINT [DF_tCompany_Hide] DEFAULT (0) FOR [Hide],
> > > > > CONSTRAINT [DF_tCompany_AutoSendEmail] DEFAULT (0) FOR [AutoSendEmail],
> > > > > CONSTRAINT [DF_tCompany_CommericalAirApproved] DEFAULT (0) FOR
> > > > > [CommericalAirApproved],
> > > > > CONSTRAINT [DF_tCompany_DepositACH_1] DEFAULT (0) FOR [DepositACH],
> > > > > CONSTRAINT [DF_tCompany_International_1] DEFAULT (0) FOR [International],
> > > > > CONSTRAINT [DF_tCompany_AutoTrackStatus] DEFAULT (0) FOR [AutoTrackStatus],
> > > > > CONSTRAINT [DF_tCompany_AutoTrackCharges] DEFAULT (0) FOR [AutoTrackCharges],
> > > > > CONSTRAINT [DF_tCompany_OpsPersonID] DEFAULT (0) FOR [OpsPersonID],
> > > > > CONSTRAINT [DF_tCompany_RateSpreadsheet] DEFAULT (0) FOR [RateSpreadsheet]
> > > > > GO
> > > > >
> > > > > CREATE INDEX [IX_tCompany_NameNick] ON [dbo].[tCompany]([NameNick]) WITH
> > > > > FILLFACTOR = 90 ON [CompanyX]
> > > > > GO
> > > > >
> > > > >|||Once again, the real problem is not the deadlock, it is the table lock which
causes the deadlock. No other query against any other table in my database
causes such a table lock.
To reproduce the deadlock, I use the Enterprise Manager.
I right click on the tCompany table. Select Open Table->Return all rows. I
leave that query window open. I repeat those steps a second time so I have
two query windows open to the tCompany table.
I right click on "Management" and select refresh. I use the
Managment->Current Activity->Locks / Object screen to see that there are two
processes that both have Shared table locks on the tCompany table.
Then if I try to modify any column in any row of either query the other
query will block it.
I can email you the data export from my tCompany table so that you can
reproduce the problem yourself.
Thanks again for your help. It is most important that I find a permanent
solution to this problem.
"Alejandro Mesa" wrote:
> FloMiester,
> have you identified the other statement involved in the deadlock, can you
> post it?
>
> AMB
> "FloMiester" wrote:
> > Alejandro,
> >
> > Thanks again for the quick reply. I have already included the table
> > structure, etc. in my first post, perhaps you need to read it entirely to
> > help diagnose the problem.
> >
> > I ran the query "SELECT * FROM dbo.tCompany;". The showplan output is as
> > follows:
> >
> > |--Clustered Index
> > Scan(OBJECT:([FreightMgmt].[dbo].[tCompany].[PK_tCompany]))
> >
> > This showplan result is identical in another similar table in my database
> > which does not have the table locking problem:
> >
> > SELECT * from dbo.tStatusLog;
> >
> > |--Clustered Index
> > Scan(OBJECT:([FreightMgmt].[dbo].[tStatusLog].[PK_tStatusLog]))
> >
> > Please advise on the next steps I need to take.
> >
> > Thanks!
> >
> >
> > "Alejandro Mesa" wrote:
> >
> > > FloMiester,
> > >
> > > I will suggest to post table structe, including constraints and indexes, the
> > > "select" statement and the execution plan (see "set showplan_text / xml on"
> > > in BOL)
> > >
> > > Depend on the indexes, the distribution of values in the index key, and the
> > > filter expression being used, the query optimizer can decide to scan the
> > > table (heap or not) and this could cause a lock escalation.
> > >
> > > AMB
> > >
> > > "FloMiester" wrote:
> > >
> > > > Alehandro,
> > > >
> > > > Thank you for the quick reply. I already had read the two articles you
> > > > refer to plus at least half a dozen more on deadlocks in the MS knowledge
> > > > base. Unfortunately they do not answer the question that is the root of the
> > > > problem. Why is the optimizer forcing the entire table to be locked when only
> > > > a simple SELECT query is issued?
> > > >
> > > > At most, only a page lock should be issued. No other tables in my SQL
> > > > database exibit this problem. I have have worked with a dozen different
> > > > databases over the last 20 years and I have yet to run up against this type
> > > > of locking problem.
> > > >
> > > > So perhaps I mistitled this posting, the correct title should be "How can a
> > > > simple select statement lock an entire table?"
> > > >
> > > > If you can find a Knowledge Base artical that answers that question and
> > > > gives me a way to avoid it, my problem would be solved for good. You help in
> > > > this matter is much appreciated.
> > > >
> > > > Many Thanks in Advance.
> > > >
> > > > ----
> > > >
> > > > "Alejandro Mesa" wrote:
> > > >
> > > > > Troubleshooting Deadlocks
> > > > > http://msdn2.microsoft.com/en-us/library/Aa937573(SQL.80).aspx
> > > > >
> > > > > SQL Server technical bulletin - How to resolve a deadlock
> > > > > http://support.microsoft.com/kb/832524/
> > > > >
> > > > >
> > > > > AMB
> > > > >
> > > > > "FloMiester" wrote:
> > > > >
> > > > > > I am using SQL Server 2000 sp4 and have a single table that is now getting a
> > > > > > deadlock problem after 3 years of use. It started when the number of rows
> > > > > > went past 16K.
> > > > > >
> > > > > > The problem is that a query returning more than ~500 rows against the table
> > > > > > causes the optimizer to use a single "Shared Table" lock for the query
> > > > > > instead of its normal behavior of issuing two locks: 1 a Page Shared Lock (S)
> > > > > > and 2 a Table Intent Shared Lock (IS). The single Shared Table lock causes
> > > > > > any other clients wishing to modify the table to deadlock waiting for the
> > > > > > table lock to be freed up.
> > > > > >
> > > > > > FYI. I have other tables in the same database over 16K rows that do not have
> > > > > > this problem.
> > > > > >
> > > > > > I have manually created the table on a test database on another SQL
> > > > > > database, on another machine, copied the data and the same problem occurred.
> > > > > >
> > > > > > Removing a few thousand rows fixes the problem, but is an unaccceptable
> > > > > > solution.
> > > > > >
> > > > > > A temporary fix is to change the fill factor on the primary key index to a
> > > > > > lower number, but after a few days and more rows are added, the deadlock
> > > > > > occurs again, until the fill factor is lowered again. I am at 50% now.
> > > > > >
> > > > > > I tried removing the cluster on the primary key index and moving the index
> > > > > > into another table space, but the same problem occurs once again if I shrink
> > > > > > the tables space the table is in (basically removing any fill space).
> > > > > >
> > > > > > Removing an unneeded column also temporarily helped, but perhaps something
> > > > > > else was going on that made the locking issue go away temporarily.
> > > > > >
> > > > > > So it seems that for this particular table the optimizer is taking into
> > > > > > consideration how much fill space is in the table to choose which type of
> > > > > > locking scheme to use. I can produce this problem simply using the
> > > > > > Enterprise Manager and opening a "SELECT *" query on the table and using the
> > > > > > "Current Activity" window to view the locks placed on the table.
> > > > > >
> > > > > > I am in desperate need of a proper fix for this problem. Does anyone have
> > > > > > any ideas?
> > > > > >
> > > > > > FYI: I also noticed what appears to be a bug with Enterprise Manager. When
> > > > > > looking at the â'Locks / Process IDâ' or the â'Locks / Objectâ' screens for locks
> > > > > > issued by several "SELECT *" queries that were still open, the â'Indexâ' column
> > > > > > showed the same primary key index being used for the â'Page lockâ' for all the
> > > > > > queries still open. The index name being displayed would be the primary key
> > > > > > index for the table of the last query I executed in Enterprise Manager.
> > > > > >
> > > > > >
> > > > > > Table DDL:
> > > > > >
> > > > > > CREATE TABLE [dbo].[tCompany] (
> > > > > > [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
> > > > > > [Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > > > > > [NameNick] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > > > > > [AddressLine1] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > [AddressLine2] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > [City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > [State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > [Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > [Country] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > [Telephone] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > [Telephone2] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > [Fax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > [AlertFax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > [ContactPerson] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > [ContactPerson2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > [AccountNum] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > [BillToAttention] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > [Airport] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > [Email] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > [Password] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
> > > > > > [LastUsedTime] [datetime] NOT NULL ,
> > > > > > [OurCompanyID] [int] NOT NULL ,
> > > > > > [SalespersonID] [int] NOT NULL ,
> > > > > > [CustomerCompanyID] [int] NOT NULL ,
> > > > > > [ParentCompanyID] [int] NULL ,
> > > > > > [Shipper] [smallint] NOT NULL ,
> > > > > > [Receiver] [smallint] NOT NULL ,
> > > > > > [ThirdPartyBilling] [smallint] NOT NULL ,
> > > > > > [Cartage] [smallint] NOT NULL ,
> > > > > > [Customer] [smallint] NOT NULL ,
> > > > > > [Payee] [smallint] NOT NULL ,
> > > > > > [Company] [smallint] NOT NULL ,
> > > > > > [Hide] [smallint] NOT NULL ,
> > > > > > [AutoSendEmail] [smallint] NOT NULL ,
> > > > > > [CommericalAirApproved] [smallint] NOT NULL ,
> > > > > > [CarrierInsuranceAmount] [money] NULL ,
> > > > > > [CarrierInsuranceExpires] [datetime] NULL ,
> > > > > > [Notes] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > [WebPageHome] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > [WebPageTrackPRO] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > > NULL ,
> > > > > > [WebPageTrackFlight] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > > NULL ,
> > > > > > [SOPNotes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > [WebPageTrackPROMethod] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > > NULL ,
> > > > > > [WebPassword] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > [WebLoginID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > [DepositACH] [smallint] NOT NULL ,
> > > > > > [Province] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > [International] [smallint] NOT NULL ,
> > > > > > [AutoTrackStatus] [smallint] NOT NULL ,
> > > > > > [AutoTrackCharges] [smallint] NOT NULL ,
> > > > > > [InvoiceTerms] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > [OpsPersonID] [int] NOT NULL ,
> > > > > > [RateSpreadsheet] [smallint] NOT NULL
> > > > > > ) ON [Company]
> > > > > > GO
> > > > > >
> > > > > > ALTER TABLE [dbo].[tCompany] WITH NOCHECK ADD
> > > > > > CONSTRAINT [PK_tCompany] PRIMARY KEY CLUSTERED
> > > > > > (
> > > > > > [CompanyID]
> > > > > > ) WITH FILLFACTOR = 90 ON [Company]
> > > > > > GO
> > > > > >
> > > > > > ALTER TABLE [dbo].[tCompany] ADD
> > > > > > CONSTRAINT [DF_tCompany_Name] DEFAULT ('N/A') FOR [Name],
> > > > > > CONSTRAINT [DF_tCompany_NameNick] DEFAULT ('N/A') FOR [NameNick],
> > > > > > CONSTRAINT [DF_tCompany_WebClient] DEFAULT ('none') FOR [Password],
> > > > > > CONSTRAINT [DF_tCompany_CreateDate] DEFAULT (getdate()) FOR [LastUsedTime],
> > > > > > CONSTRAINT [DF_tCompany_OurCompanyID] DEFAULT (3) FOR [OurCompanyID],
> > > > > > CONSTRAINT [DF_tCompany_SalespersonID] DEFAULT (0) FOR [SalespersonID],
> > > > > > CONSTRAINT [DF_tCompany_CustomerCompanyID] DEFAULT (0) FOR
> > > > > > [CustomerCompanyID],
> > > > > > CONSTRAINT [DF_tCompany_Shipper] DEFAULT (0) FOR [Shipper],
> > > > > > CONSTRAINT [DF_tCompany_Receiver] DEFAULT (0) FOR [Receiver],
> > > > > > CONSTRAINT [DF_tCompany_ThirdPartyBilling] DEFAULT (0) FOR
> > > > > > [ThirdPartyBilling],
> > > > > > CONSTRAINT [DF_tCompany_Cartage] DEFAULT (0) FOR [Cartage],
> > > > > > CONSTRAINT [DF_tCompany_Customer] DEFAULT (0) FOR [Customer],
> > > > > > CONSTRAINT [DF_tCompany_Payee] DEFAULT (0) FOR [Payee],
> > > > > > CONSTRAINT [DF_tCompany_Company] DEFAULT (0) FOR [Company],
> > > > > > CONSTRAINT [DF_tCompany_Hide] DEFAULT (0) FOR [Hide],
> > > > > > CONSTRAINT [DF_tCompany_AutoSendEmail] DEFAULT (0) FOR [AutoSendEmail],
> > > > > > CONSTRAINT [DF_tCompany_CommericalAirApproved] DEFAULT (0) FOR
> > > > > > [CommericalAirApproved],
> > > > > > CONSTRAINT [DF_tCompany_DepositACH_1] DEFAULT (0) FOR [DepositACH],
> > > > > > CONSTRAINT [DF_tCompany_International_1] DEFAULT (0) FOR [International],
> > > > > > CONSTRAINT [DF_tCompany_AutoTrackStatus] DEFAULT (0) FOR [AutoTrackStatus],
> > > > > > CONSTRAINT [DF_tCompany_AutoTrackCharges] DEFAULT (0) FOR [AutoTrackCharges],
> > > > > > CONSTRAINT [DF_tCompany_OpsPersonID] DEFAULT (0) FOR [OpsPersonID],
> > > > > > CONSTRAINT [DF_tCompany_RateSpreadsheet] DEFAULT (0) FOR [RateSpreadsheet]
> > > > > > GO
> > > > > >
> > > > > > CREATE INDEX [IX_tCompany_NameNick] ON [dbo].[tCompany]([NameNick]) WITH
> > > > > > FILLFACTOR = 90 ON [CompanyX]
> > > > > > GO
> > > > > >
> > > > > >|||Use profiler to catch the real statement that EM send to the engine. It could
be that it is using a table hint like (updlock or tablock).
AMB
"FloMiester" wrote:
> Once again, the real problem is not the deadlock, it is the table lock which
> causes the deadlock. No other query against any other table in my database
> causes such a table lock.
> To reproduce the deadlock, I use the Enterprise Manager.
> I right click on the tCompany table. Select Open Table->Return all rows. I
> leave that query window open. I repeat those steps a second time so I have
> two query windows open to the tCompany table.
> I right click on "Management" and select refresh. I use the
> Managment->Current Activity->Locks / Object screen to see that there are two
> processes that both have Shared table locks on the tCompany table.
> Then if I try to modify any column in any row of either query the other
> query will block it.
> I can email you the data export from my tCompany table so that you can
> reproduce the problem yourself.
> Thanks again for your help. It is most important that I find a permanent
> solution to this problem.
>
> "Alejandro Mesa" wrote:
> > FloMiester,
> >
> > have you identified the other statement involved in the deadlock, can you
> > post it?
> >
> >
> > AMB
> >
> > "FloMiester" wrote:
> >
> > > Alejandro,
> > >
> > > Thanks again for the quick reply. I have already included the table
> > > structure, etc. in my first post, perhaps you need to read it entirely to
> > > help diagnose the problem.
> > >
> > > I ran the query "SELECT * FROM dbo.tCompany;". The showplan output is as
> > > follows:
> > >
> > > |--Clustered Index
> > > Scan(OBJECT:([FreightMgmt].[dbo].[tCompany].[PK_tCompany]))
> > >
> > > This showplan result is identical in another similar table in my database
> > > which does not have the table locking problem:
> > >
> > > SELECT * from dbo.tStatusLog;
> > >
> > > |--Clustered Index
> > > Scan(OBJECT:([FreightMgmt].[dbo].[tStatusLog].[PK_tStatusLog]))
> > >
> > > Please advise on the next steps I need to take.
> > >
> > > Thanks!
> > >
> > >
> > > "Alejandro Mesa" wrote:
> > >
> > > > FloMiester,
> > > >
> > > > I will suggest to post table structe, including constraints and indexes, the
> > > > "select" statement and the execution plan (see "set showplan_text / xml on"
> > > > in BOL)
> > > >
> > > > Depend on the indexes, the distribution of values in the index key, and the
> > > > filter expression being used, the query optimizer can decide to scan the
> > > > table (heap or not) and this could cause a lock escalation.
> > > >
> > > > AMB
> > > >
> > > > "FloMiester" wrote:
> > > >
> > > > > Alehandro,
> > > > >
> > > > > Thank you for the quick reply. I already had read the two articles you
> > > > > refer to plus at least half a dozen more on deadlocks in the MS knowledge
> > > > > base. Unfortunately they do not answer the question that is the root of the
> > > > > problem. Why is the optimizer forcing the entire table to be locked when only
> > > > > a simple SELECT query is issued?
> > > > >
> > > > > At most, only a page lock should be issued. No other tables in my SQL
> > > > > database exibit this problem. I have have worked with a dozen different
> > > > > databases over the last 20 years and I have yet to run up against this type
> > > > > of locking problem.
> > > > >
> > > > > So perhaps I mistitled this posting, the correct title should be "How can a
> > > > > simple select statement lock an entire table?"
> > > > >
> > > > > If you can find a Knowledge Base artical that answers that question and
> > > > > gives me a way to avoid it, my problem would be solved for good. You help in
> > > > > this matter is much appreciated.
> > > > >
> > > > > Many Thanks in Advance.
> > > > >
> > > > > ----
> > > > >
> > > > > "Alejandro Mesa" wrote:
> > > > >
> > > > > > Troubleshooting Deadlocks
> > > > > > http://msdn2.microsoft.com/en-us/library/Aa937573(SQL.80).aspx
> > > > > >
> > > > > > SQL Server technical bulletin - How to resolve a deadlock
> > > > > > http://support.microsoft.com/kb/832524/
> > > > > >
> > > > > >
> > > > > > AMB
> > > > > >
> > > > > > "FloMiester" wrote:
> > > > > >
> > > > > > > I am using SQL Server 2000 sp4 and have a single table that is now getting a
> > > > > > > deadlock problem after 3 years of use. It started when the number of rows
> > > > > > > went past 16K.
> > > > > > >
> > > > > > > The problem is that a query returning more than ~500 rows against the table
> > > > > > > causes the optimizer to use a single "Shared Table" lock for the query
> > > > > > > instead of its normal behavior of issuing two locks: 1 a Page Shared Lock (S)
> > > > > > > and 2 a Table Intent Shared Lock (IS). The single Shared Table lock causes
> > > > > > > any other clients wishing to modify the table to deadlock waiting for the
> > > > > > > table lock to be freed up.
> > > > > > >
> > > > > > > FYI. I have other tables in the same database over 16K rows that do not have
> > > > > > > this problem.
> > > > > > >
> > > > > > > I have manually created the table on a test database on another SQL
> > > > > > > database, on another machine, copied the data and the same problem occurred.
> > > > > > >
> > > > > > > Removing a few thousand rows fixes the problem, but is an unaccceptable
> > > > > > > solution.
> > > > > > >
> > > > > > > A temporary fix is to change the fill factor on the primary key index to a
> > > > > > > lower number, but after a few days and more rows are added, the deadlock
> > > > > > > occurs again, until the fill factor is lowered again. I am at 50% now.
> > > > > > >
> > > > > > > I tried removing the cluster on the primary key index and moving the index
> > > > > > > into another table space, but the same problem occurs once again if I shrink
> > > > > > > the tables space the table is in (basically removing any fill space).
> > > > > > >
> > > > > > > Removing an unneeded column also temporarily helped, but perhaps something
> > > > > > > else was going on that made the locking issue go away temporarily.
> > > > > > >
> > > > > > > So it seems that for this particular table the optimizer is taking into
> > > > > > > consideration how much fill space is in the table to choose which type of
> > > > > > > locking scheme to use. I can produce this problem simply using the
> > > > > > > Enterprise Manager and opening a "SELECT *" query on the table and using the
> > > > > > > "Current Activity" window to view the locks placed on the table.
> > > > > > >
> > > > > > > I am in desperate need of a proper fix for this problem. Does anyone have
> > > > > > > any ideas?
> > > > > > >
> > > > > > > FYI: I also noticed what appears to be a bug with Enterprise Manager. When
> > > > > > > looking at the â'Locks / Process IDâ' or the â'Locks / Objectâ' screens for locks
> > > > > > > issued by several "SELECT *" queries that were still open, the â'Indexâ' column
> > > > > > > showed the same primary key index being used for the â'Page lockâ' for all the
> > > > > > > queries still open. The index name being displayed would be the primary key
> > > > > > > index for the table of the last query I executed in Enterprise Manager.
> > > > > > >
> > > > > > >
> > > > > > > Table DDL:
> > > > > > >
> > > > > > > CREATE TABLE [dbo].[tCompany] (
> > > > > > > [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
> > > > > > > [Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > > > > > > [NameNick] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > > > > > > [AddressLine1] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > [AddressLine2] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > [City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > [State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > [Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > [Country] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > [Telephone] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > [Telephone2] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > [Fax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > [AlertFax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > [ContactPerson] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > [ContactPerson2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > [AccountNum] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > [BillToAttention] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > [Airport] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > [Email] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > [Password] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
> > > > > > > [LastUsedTime] [datetime] NOT NULL ,
> > > > > > > [OurCompanyID] [int] NOT NULL ,
> > > > > > > [SalespersonID] [int] NOT NULL ,
> > > > > > > [CustomerCompanyID] [int] NOT NULL ,
> > > > > > > [ParentCompanyID] [int] NULL ,
> > > > > > > [Shipper] [smallint] NOT NULL ,
> > > > > > > [Receiver] [smallint] NOT NULL ,
> > > > > > > [ThirdPartyBilling] [smallint] NOT NULL ,
> > > > > > > [Cartage] [smallint] NOT NULL ,
> > > > > > > [Customer] [smallint] NOT NULL ,
> > > > > > > [Payee] [smallint] NOT NULL ,
> > > > > > > [Company] [smallint] NOT NULL ,
> > > > > > > [Hide] [smallint] NOT NULL ,
> > > > > > > [AutoSendEmail] [smallint] NOT NULL ,
> > > > > > > [CommericalAirApproved] [smallint] NOT NULL ,
> > > > > > > [CarrierInsuranceAmount] [money] NULL ,
> > > > > > > [CarrierInsuranceExpires] [datetime] NULL ,
> > > > > > > [Notes] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > [WebPageHome] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > [WebPageTrackPRO] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > > > NULL ,
> > > > > > > [WebPageTrackFlight] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > > > NULL ,
> > > > > > > [SOPNotes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > [WebPageTrackPROMethod] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > > > NULL ,
> > > > > > > [WebPassword] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > [WebLoginID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > [DepositACH] [smallint] NOT NULL ,
> > > > > > > [Province] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > [International] [smallint] NOT NULL ,
> > > > > > > [AutoTrackStatus] [smallint] NOT NULL ,
> > > > > > > [AutoTrackCharges] [smallint] NOT NULL ,
> > > > > > > [InvoiceTerms] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > [OpsPersonID] [int] NOT NULL ,
> > > > > > > [RateSpreadsheet] [smallint] NOT NULL
> > > > > > > ) ON [Company]
> > > > > > > GO
> > > > > > >
> > > > > > > ALTER TABLE [dbo].[tCompany] WITH NOCHECK ADD
> > > > > > > CONSTRAINT [PK_tCompany] PRIMARY KEY CLUSTERED
> > > > > > > (
> > > > > > > [CompanyID]
> > > > > > > ) WITH FILLFACTOR = 90 ON [Company]
> > > > > > > GO
> > > > > > >
> > > > > > > ALTER TABLE [dbo].[tCompany] ADD
> > > > > > > CONSTRAINT [DF_tCompany_Name] DEFAULT ('N/A') FOR [Name],
> > > > > > > CONSTRAINT [DF_tCompany_NameNick] DEFAULT ('N/A') FOR [NameNick],
> > > > > > > CONSTRAINT [DF_tCompany_WebClient] DEFAULT ('none') FOR [Password],
> > > > > > > CONSTRAINT [DF_tCompany_CreateDate] DEFAULT (getdate()) FOR [LastUsedTime],
> > > > > > > CONSTRAINT [DF_tCompany_OurCompanyID] DEFAULT (3) FOR [OurCompanyID],
> > > > > > > CONSTRAINT [DF_tCompany_SalespersonID] DEFAULT (0) FOR [SalespersonID],
> > > > > > > CONSTRAINT [DF_tCompany_CustomerCompanyID] DEFAULT (0) FOR
> > > > > > > [CustomerCompanyID],
> > > > > > > CONSTRAINT [DF_tCompany_Shipper] DEFAULT (0) FOR [Shipper],
> > > > > > > CONSTRAINT [DF_tCompany_Receiver] DEFAULT (0) FOR [Receiver],
> > > > > > > CONSTRAINT [DF_tCompany_ThirdPartyBilling] DEFAULT (0) FOR
> > > > > > > [ThirdPartyBilling],
> > > > > > > CONSTRAINT [DF_tCompany_Cartage] DEFAULT (0) FOR [Cartage],
> > > > > > > CONSTRAINT [DF_tCompany_Customer] DEFAULT (0) FOR [Customer],
> > > > > > > CONSTRAINT [DF_tCompany_Payee] DEFAULT (0) FOR [Payee],
> > > > > > > CONSTRAINT [DF_tCompany_Company] DEFAULT (0) FOR [Company],
> > > > > > > CONSTRAINT [DF_tCompany_Hide] DEFAULT (0) FOR [Hide],
> > > > > > > CONSTRAINT [DF_tCompany_AutoSendEmail] DEFAULT (0) FOR [AutoSendEmail],
> > > > > > > CONSTRAINT [DF_tCompany_CommericalAirApproved] DEFAULT (0) FOR
> > > > > > > [CommericalAirApproved],
> > > > > > > CONSTRAINT [DF_tCompany_DepositACH_1] DEFAULT (0) FOR [DepositACH],
> > > > > > > CONSTRAINT [DF_tCompany_International_1] DEFAULT (0) FOR [International],
> > > > > > > CONSTRAINT [DF_tCompany_AutoTrackStatus] DEFAULT (0) FOR [AutoTrackStatus],
> > > > > > > CONSTRAINT [DF_tCompany_AutoTrackCharges] DEFAULT (0) FOR [AutoTrackCharges],
> > > > > > > CONSTRAINT [DF_tCompany_OpsPersonID] DEFAULT (0) FOR [OpsPersonID],
> > > > > > > CONSTRAINT [DF_tCompany_RateSpreadsheet] DEFAULT (0) FOR [RateSpreadsheet]
> > > > > > > GO
> > > > > > >
> > > > > > > CREATE INDEX [IX_tCompany_NameNick] ON [dbo].[tCompany]([NameNick]) WITH
> > > > > > > FILLFACTOR = 90 ON [CompanyX]
> > > > > > > GO
> > > > > > >
> > > > > > >|||I played with Profiler and came up with two scenarios for comparison. The
first scenario provides proper locking behavior allowing other users to
update the table while the second does not.
1) The primary key clustered index fill factor set to 70
2) Changed the fill factor to 90 percent.
Both times I ran the simple query "SELECT * FROM dbo.tCompany" using MS
Access. I started the trace, opened the query and then quickly closed it and
stopped the trace. The table has ~17K rows, but MS Access only reads the
first 100 rows and leaves the query open unless you scroll down the entire
list of rows. This helps to have a more controlled test but I can get the
same results using any query tool.
If you send me an email to admin@.titanservices.net, I can reply back with
the two trace files, but I can give you the basics here:
Both runs were identical except that when the clustered fill factor was 70,
the database used several Intent Shared Page level locks for the first 100
rows it returned and then when the fill factor was changed to 90, the
database used a single Intent Shared Table level lock.
The execution plan was identical:
Execution Tree
--
Clustered Index Scan(OBJECT:([FreightMgmt].[dbo].[tCompany].[PK_tCompany]))
Then funny thing is that when I look at the what Enterprise Manager reports
for locks on the tCompany table under the fill factor 90 scenario, it shows a
single Shared Table lock, not an Intent Shared table lock like the trace
does. Which one is correct? My opinion is that the trace is reporting the
correct lock mode because I don't think a simple shared table lock would
prevent other users from modifying the table while an intent shared table
lock should block modifications.
If you look at my first post in this thread, I also describe an inconsistent
display of information by the Enterprise Manager which makes me not what to
trust what it reports regarding locks.
Please advise on the next step I need to take to help resolve this issue.
Many Thanks!!!!
"Alejandro Mesa" wrote:
> Use profiler to catch the real statement that EM send to the engine. It could
> be that it is using a table hint like (updlock or tablock).
>
> AMB
> "FloMiester" wrote:
> > Once again, the real problem is not the deadlock, it is the table lock which
> > causes the deadlock. No other query against any other table in my database
> > causes such a table lock.
> >
> > To reproduce the deadlock, I use the Enterprise Manager.
> >
> > I right click on the tCompany table. Select Open Table->Return all rows. I
> > leave that query window open. I repeat those steps a second time so I have
> > two query windows open to the tCompany table.
> >
> > I right click on "Management" and select refresh. I use the
> > Managment->Current Activity->Locks / Object screen to see that there are two
> > processes that both have Shared table locks on the tCompany table.
> >
> > Then if I try to modify any column in any row of either query the other
> > query will block it.
> >
> > I can email you the data export from my tCompany table so that you can
> > reproduce the problem yourself.
> >
> > Thanks again for your help. It is most important that I find a permanent
> > solution to this problem.
> >
> >
> > "Alejandro Mesa" wrote:
> >
> > > FloMiester,
> > >
> > > have you identified the other statement involved in the deadlock, can you
> > > post it?
> > >
> > >
> > > AMB
> > >
> > > "FloMiester" wrote:
> > >
> > > > Alejandro,
> > > >
> > > > Thanks again for the quick reply. I have already included the table
> > > > structure, etc. in my first post, perhaps you need to read it entirely to
> > > > help diagnose the problem.
> > > >
> > > > I ran the query "SELECT * FROM dbo.tCompany;". The showplan output is as
> > > > follows:
> > > >
> > > > |--Clustered Index
> > > > Scan(OBJECT:([FreightMgmt].[dbo].[tCompany].[PK_tCompany]))
> > > >
> > > > This showplan result is identical in another similar table in my database
> > > > which does not have the table locking problem:
> > > >
> > > > SELECT * from dbo.tStatusLog;
> > > >
> > > > |--Clustered Index
> > > > Scan(OBJECT:([FreightMgmt].[dbo].[tStatusLog].[PK_tStatusLog]))
> > > >
> > > > Please advise on the next steps I need to take.
> > > >
> > > > Thanks!
> > > >
> > > >
> > > > "Alejandro Mesa" wrote:
> > > >
> > > > > FloMiester,
> > > > >
> > > > > I will suggest to post table structe, including constraints and indexes, the
> > > > > "select" statement and the execution plan (see "set showplan_text / xml on"
> > > > > in BOL)
> > > > >
> > > > > Depend on the indexes, the distribution of values in the index key, and the
> > > > > filter expression being used, the query optimizer can decide to scan the
> > > > > table (heap or not) and this could cause a lock escalation.
> > > > >
> > > > > AMB
> > > > >
> > > > > "FloMiester" wrote:
> > > > >
> > > > > > Alehandro,
> > > > > >
> > > > > > Thank you for the quick reply. I already had read the two articles you
> > > > > > refer to plus at least half a dozen more on deadlocks in the MS knowledge
> > > > > > base. Unfortunately they do not answer the question that is the root of the
> > > > > > problem. Why is the optimizer forcing the entire table to be locked when only
> > > > > > a simple SELECT query is issued?
> > > > > >
> > > > > > At most, only a page lock should be issued. No other tables in my SQL
> > > > > > database exibit this problem. I have have worked with a dozen different
> > > > > > databases over the last 20 years and I have yet to run up against this type
> > > > > > of locking problem.
> > > > > >
> > > > > > So perhaps I mistitled this posting, the correct title should be "How can a
> > > > > > simple select statement lock an entire table?"
> > > > > >
> > > > > > If you can find a Knowledge Base artical that answers that question and
> > > > > > gives me a way to avoid it, my problem would be solved for good. You help in
> > > > > > this matter is much appreciated.
> > > > > >
> > > > > > Many Thanks in Advance.
> > > > > >
> > > > > > ----
> > > > > >
> > > > > > "Alejandro Mesa" wrote:
> > > > > >
> > > > > > > Troubleshooting Deadlocks
> > > > > > > http://msdn2.microsoft.com/en-us/library/Aa937573(SQL.80).aspx
> > > > > > >
> > > > > > > SQL Server technical bulletin - How to resolve a deadlock
> > > > > > > http://support.microsoft.com/kb/832524/
> > > > > > >
> > > > > > >
> > > > > > > AMB
> > > > > > >
> > > > > > > "FloMiester" wrote:
> > > > > > >
> > > > > > > > I am using SQL Server 2000 sp4 and have a single table that is now getting a
> > > > > > > > deadlock problem after 3 years of use. It started when the number of rows
> > > > > > > > went past 16K.
> > > > > > > >
> > > > > > > > The problem is that a query returning more than ~500 rows against the table
> > > > > > > > causes the optimizer to use a single "Shared Table" lock for the query
> > > > > > > > instead of its normal behavior of issuing two locks: 1 a Page Shared Lock (S)
> > > > > > > > and 2 a Table Intent Shared Lock (IS). The single Shared Table lock causes
> > > > > > > > any other clients wishing to modify the table to deadlock waiting for the
> > > > > > > > table lock to be freed up.
> > > > > > > >
> > > > > > > > FYI. I have other tables in the same database over 16K rows that do not have
> > > > > > > > this problem.
> > > > > > > >
> > > > > > > > I have manually created the table on a test database on another SQL
> > > > > > > > database, on another machine, copied the data and the same problem occurred.
> > > > > > > >
> > > > > > > > Removing a few thousand rows fixes the problem, but is an unaccceptable
> > > > > > > > solution.
> > > > > > > >
> > > > > > > > A temporary fix is to change the fill factor on the primary key index to a
> > > > > > > > lower number, but after a few days and more rows are added, the deadlock
> > > > > > > > occurs again, until the fill factor is lowered again. I am at 50% now.
> > > > > > > >
> > > > > > > > I tried removing the cluster on the primary key index and moving the index
> > > > > > > > into another table space, but the same problem occurs once again if I shrink
> > > > > > > > the tables space the table is in (basically removing any fill space).
> > > > > > > >
> > > > > > > > Removing an unneeded column also temporarily helped, but perhaps something
> > > > > > > > else was going on that made the locking issue go away temporarily.
> > > > > > > >
> > > > > > > > So it seems that for this particular table the optimizer is taking into
> > > > > > > > consideration how much fill space is in the table to choose which type of
> > > > > > > > locking scheme to use. I can produce this problem simply using the
> > > > > > > > Enterprise Manager and opening a "SELECT *" query on the table and using the
> > > > > > > > "Current Activity" window to view the locks placed on the table.
> > > > > > > >
> > > > > > > > I am in desperate need of a proper fix for this problem. Does anyone have
> > > > > > > > any ideas?
> > > > > > > >
> > > > > > > > FYI: I also noticed what appears to be a bug with Enterprise Manager. When
> > > > > > > > looking at the â'Locks / Process IDâ' or the â'Locks / Objectâ' screens for locks
> > > > > > > > issued by several "SELECT *" queries that were still open, the â'Indexâ' column
> > > > > > > > showed the same primary key index being used for the â'Page lockâ' for all the
> > > > > > > > queries still open. The index name being displayed would be the primary key
> > > > > > > > index for the table of the last query I executed in Enterprise Manager.
> > > > > > > >
> > > > > > > >
> > > > > > > > Table DDL:
> > > > > > > >
> > > > > > > > CREATE TABLE [dbo].[tCompany] (
> > > > > > > > [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
> > > > > > > > [Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > > > > > > > [NameNick] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > > > > > > > [AddressLine1] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [AddressLine2] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [Country] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [Telephone] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [Telephone2] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [Fax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [AlertFax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [ContactPerson] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [ContactPerson2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [AccountNum] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [BillToAttention] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [Airport] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [Email] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [Password] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
> > > > > > > > [LastUsedTime] [datetime] NOT NULL ,
> > > > > > > > [OurCompanyID] [int] NOT NULL ,
> > > > > > > > [SalespersonID] [int] NOT NULL ,
> > > > > > > > [CustomerCompanyID] [int] NOT NULL ,
> > > > > > > > [ParentCompanyID] [int] NULL ,
> > > > > > > > [Shipper] [smallint] NOT NULL ,
> > > > > > > > [Receiver] [smallint] NOT NULL ,
> > > > > > > > [ThirdPartyBilling] [smallint] NOT NULL ,
> > > > > > > > [Cartage] [smallint] NOT NULL ,
> > > > > > > > [Customer] [smallint] NOT NULL ,
> > > > > > > > [Payee] [smallint] NOT NULL ,
> > > > > > > > [Company] [smallint] NOT NULL ,
> > > > > > > > [Hide] [smallint] NOT NULL ,
> > > > > > > > [AutoSendEmail] [smallint] NOT NULL ,
> > > > > > > > [CommericalAirApproved] [smallint] NOT NULL ,
> > > > > > > > [CarrierInsuranceAmount] [money] NULL ,
> > > > > > > > [CarrierInsuranceExpires] [datetime] NULL ,
> > > > > > > > [Notes] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [WebPageHome] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [WebPageTrackPRO] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > > > > NULL ,
> > > > > > > > [WebPageTrackFlight] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > > > > NULL ,
> > > > > > > > [SOPNotes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [WebPageTrackPROMethod] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > > > > NULL ,
> > > > > > > > [WebPassword] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [WebLoginID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [DepositACH] [smallint] NOT NULL ,
> > > > > > > > [Province] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [International] [smallint] NOT NULL ,
> > > > > > > > [AutoTrackStatus] [smallint] NOT NULL ,
> > > > > > > > [AutoTrackCharges] [smallint] NOT NULL ,
> > > > > > > > [InvoiceTerms] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [OpsPersonID] [int] NOT NULL ,
> > > > > > > > [RateSpreadsheet] [smallint] NOT NULL
> > > > > > > > ) ON [Company]
> > > > > > > > GO
> > > > > > > >
> > > > > > > > ALTER TABLE [dbo].[tCompany] WITH NOCHECK ADD
> > > > > > > > CONSTRAINT [PK_tCompany] PRIMARY KEY CLUSTERED
> > > > > > > > (
> > > > > > > > [CompanyID]
> > > > > > > > ) WITH FILLFACTOR = 90 ON [Company]
> > > > > > > > GO
> > > > > > > >
> > > > > > > > ALTER TABLE [dbo].[tCompany] ADD
> > > > > > > > CONSTRAINT [DF_tCompany_Name] DEFAULT ('N/A') FOR [Name],
> > > > > > > > CONSTRAINT [DF_tCompany_NameNick] DEFAULT ('N/A') FOR [NameNick],
> > > > > > > > CONSTRAINT [DF_tCompany_WebClient] DEFAULT ('none') FOR [Password],
> > > > > > > > CONSTRAINT [DF_tCompany_CreateDate] DEFAULT (getdate()) FOR [LastUsedTime],
> > > > > > > > CONSTRAINT [DF_tCompany_OurCompanyID] DEFAULT (3) FOR [OurCompanyID],
> > > > > > > > CONSTRAINT [DF_tCompany_SalespersonID] DEFAULT (0) FOR [SalespersonID],
> > > > > > > > CONSTRAINT [DF_tCompany_CustomerCompanyID] DEFAULT (0) FOR
> > > > > > > > [CustomerCompanyID],
> > > > > > > > CONSTRAINT [DF_tCompany_Shipper] DEFAULT (0) FOR [Shipper],
> > > > > > > > CONSTRAINT [DF_tCompany_Receiver] DEFAULT (0) FOR [Receiver],
> > > > > > > > CONSTRAINT [DF_tCompany_ThirdPartyBilling] DEFAULT (0) FOR
> > > > > > > > [ThirdPartyBilling],
> > > > > > > > CONSTRAINT [DF_tCompany_Cartage] DEFAULT (0) FOR [Cartage],
> > > > > > > > CONSTRAINT [DF_tCompany_Customer] DEFAULT (0) FOR [Customer],
> > > > > > > > CONSTRAINT [DF_tCompany_Payee] DEFAULT (0) FOR [Payee],
> > > > > > > > CONSTRAINT [DF_tCompany_Company] DEFAULT (0) FOR [Company],
> > > > > > > > CONSTRAINT [DF_tCompany_Hide] DEFAULT (0) FOR [Hide],
> > > > > > > > CONSTRAINT [DF_tCompany_AutoSendEmail] DEFAULT (0) FOR [AutoSendEmail],
> > > > > > > > CONSTRAINT [DF_tCompany_CommericalAirApproved] DEFAULT (0) FOR
> > > > > > > > [CommericalAirApproved],
> > > > > > > > CONSTRAINT [DF_tCompany_DepositACH_1] DEFAULT (0) FOR [DepositACH],
> > > > > > > > CONSTRAINT [DF_tCompany_International_1] DEFAULT (0) FOR [International],
> > > > > > > > CONSTRAINT [DF_tCompany_AutoTrackStatus] DEFAULT (0) FOR [AutoTrackStatus],
> > > > > > > > CONSTRAINT [DF_tCompany_AutoTrackCharges] DEFAULT (0) FOR [AutoTrackCharges],
> > > > > > > > CONSTRAINT [DF_tCompany_OpsPersonID] DEFAULT (0) FOR [OpsPersonID],
> > > > > > > > CONSTRAINT [DF_tCompany_RateSpreadsheet] DEFAULT (0) FOR [RateSpreadsheet]
> > > > > > > > GO
> > > > > > > >
> > > > > > > > CREATE INDEX [IX_tCompany_NameNick] ON [dbo].[tCompany]([NameNick]) WITH
> > > > > > > > FILLFACTOR = 90 ON [CompanyX]
> > > > > > > > GO
> > > > > > > >
> > > > > > > >|||I forgot to mention that I defined the "SELECT * FROM tCompany" query in MS
Access to have a recordset type of "Snap Shot". If I set the recordset type
to Dynaset, the table locking problem went away for the fill factor 90
scenario when I had another index present on the table. The trace showed that
the database was using that additional index for some reason (it was not a
unique index). When I removed that index and the only remaing index was the
primary key, the table locking problem occurred again even with the recordset
type of dynaset.
I hope this info makes things clearer to someone as to why the database is
using a table level level locking rather than page level locking because I
still don't have a clue. :-)
"Alejandro Mesa" wrote:
> Use profiler to catch the real statement that EM send to the engine. It could
> be that it is using a table hint like (updlock or tablock).
>
> AMB
> "FloMiester" wrote:
> > Once again, the real problem is not the deadlock, it is the table lock which
> > causes the deadlock. No other query against any other table in my database
> > causes such a table lock.
> >
> > To reproduce the deadlock, I use the Enterprise Manager.
> >
> > I right click on the tCompany table. Select Open Table->Return all rows. I
> > leave that query window open. I repeat those steps a second time so I have
> > two query windows open to the tCompany table.
> >
> > I right click on "Management" and select refresh. I use the
> > Managment->Current Activity->Locks / Object screen to see that there are two
> > processes that both have Shared table locks on the tCompany table.
> >
> > Then if I try to modify any column in any row of either query the other
> > query will block it.
> >
> > I can email you the data export from my tCompany table so that you can
> > reproduce the problem yourself.
> >
> > Thanks again for your help. It is most important that I find a permanent
> > solution to this problem.
> >
> >
> > "Alejandro Mesa" wrote:
> >
> > > FloMiester,
> > >
> > > have you identified the other statement involved in the deadlock, can you
> > > post it?
> > >
> > >
> > > AMB
> > >
> > > "FloMiester" wrote:
> > >
> > > > Alejandro,
> > > >
> > > > Thanks again for the quick reply. I have already included the table
> > > > structure, etc. in my first post, perhaps you need to read it entirely to
> > > > help diagnose the problem.
> > > >
> > > > I ran the query "SELECT * FROM dbo.tCompany;". The showplan output is as
> > > > follows:
> > > >
> > > > |--Clustered Index
> > > > Scan(OBJECT:([FreightMgmt].[dbo].[tCompany].[PK_tCompany]))
> > > >
> > > > This showplan result is identical in another similar table in my database
> > > > which does not have the table locking problem:
> > > >
> > > > SELECT * from dbo.tStatusLog;
> > > >
> > > > |--Clustered Index
> > > > Scan(OBJECT:([FreightMgmt].[dbo].[tStatusLog].[PK_tStatusLog]))
> > > >
> > > > Please advise on the next steps I need to take.
> > > >
> > > > Thanks!
> > > >
> > > >
> > > > "Alejandro Mesa" wrote:
> > > >
> > > > > FloMiester,
> > > > >
> > > > > I will suggest to post table structe, including constraints and indexes, the
> > > > > "select" statement and the execution plan (see "set showplan_text / xml on"
> > > > > in BOL)
> > > > >
> > > > > Depend on the indexes, the distribution of values in the index key, and the
> > > > > filter expression being used, the query optimizer can decide to scan the
> > > > > table (heap or not) and this could cause a lock escalation.
> > > > >
> > > > > AMB
> > > > >
> > > > > "FloMiester" wrote:
> > > > >
> > > > > > Alehandro,
> > > > > >
> > > > > > Thank you for the quick reply. I already had read the two articles you
> > > > > > refer to plus at least half a dozen more on deadlocks in the MS knowledge
> > > > > > base. Unfortunately they do not answer the question that is the root of the
> > > > > > problem. Why is the optimizer forcing the entire table to be locked when only
> > > > > > a simple SELECT query is issued?
> > > > > >
> > > > > > At most, only a page lock should be issued. No other tables in my SQL
> > > > > > database exibit this problem. I have have worked with a dozen different
> > > > > > databases over the last 20 years and I have yet to run up against this type
> > > > > > of locking problem.
> > > > > >
> > > > > > So perhaps I mistitled this posting, the correct title should be "How can a
> > > > > > simple select statement lock an entire table?"
> > > > > >
> > > > > > If you can find a Knowledge Base artical that answers that question and
> > > > > > gives me a way to avoid it, my problem would be solved for good. You help in
> > > > > > this matter is much appreciated.
> > > > > >
> > > > > > Many Thanks in Advance.
> > > > > >
> > > > > > ----
> > > > > >
> > > > > > "Alejandro Mesa" wrote:
> > > > > >
> > > > > > > Troubleshooting Deadlocks
> > > > > > > http://msdn2.microsoft.com/en-us/library/Aa937573(SQL.80).aspx
> > > > > > >
> > > > > > > SQL Server technical bulletin - How to resolve a deadlock
> > > > > > > http://support.microsoft.com/kb/832524/
> > > > > > >
> > > > > > >
> > > > > > > AMB
> > > > > > >
> > > > > > > "FloMiester" wrote:
> > > > > > >
> > > > > > > > I am using SQL Server 2000 sp4 and have a single table that is now getting a
> > > > > > > > deadlock problem after 3 years of use. It started when the number of rows
> > > > > > > > went past 16K.
> > > > > > > >
> > > > > > > > The problem is that a query returning more than ~500 rows against the table
> > > > > > > > causes the optimizer to use a single "Shared Table" lock for the query
> > > > > > > > instead of its normal behavior of issuing two locks: 1 a Page Shared Lock (S)
> > > > > > > > and 2 a Table Intent Shared Lock (IS). The single Shared Table lock causes
> > > > > > > > any other clients wishing to modify the table to deadlock waiting for the
> > > > > > > > table lock to be freed up.
> > > > > > > >
> > > > > > > > FYI. I have other tables in the same database over 16K rows that do not have
> > > > > > > > this problem.
> > > > > > > >
> > > > > > > > I have manually created the table on a test database on another SQL
> > > > > > > > database, on another machine, copied the data and the same problem occurred.
> > > > > > > >
> > > > > > > > Removing a few thousand rows fixes the problem, but is an unaccceptable
> > > > > > > > solution.
> > > > > > > >
> > > > > > > > A temporary fix is to change the fill factor on the primary key index to a
> > > > > > > > lower number, but after a few days and more rows are added, the deadlock
> > > > > > > > occurs again, until the fill factor is lowered again. I am at 50% now.
> > > > > > > >
> > > > > > > > I tried removing the cluster on the primary key index and moving the index
> > > > > > > > into another table space, but the same problem occurs once again if I shrink
> > > > > > > > the tables space the table is in (basically removing any fill space).
> > > > > > > >
> > > > > > > > Removing an unneeded column also temporarily helped, but perhaps something
> > > > > > > > else was going on that made the locking issue go away temporarily.
> > > > > > > >
> > > > > > > > So it seems that for this particular table the optimizer is taking into
> > > > > > > > consideration how much fill space is in the table to choose which type of
> > > > > > > > locking scheme to use. I can produce this problem simply using the
> > > > > > > > Enterprise Manager and opening a "SELECT *" query on the table and using the
> > > > > > > > "Current Activity" window to view the locks placed on the table.
> > > > > > > >
> > > > > > > > I am in desperate need of a proper fix for this problem. Does anyone have
> > > > > > > > any ideas?
> > > > > > > >
> > > > > > > > FYI: I also noticed what appears to be a bug with Enterprise Manager. When
> > > > > > > > looking at the â'Locks / Process IDâ' or the â'Locks / Objectâ' screens for locks
> > > > > > > > issued by several "SELECT *" queries that were still open, the â'Indexâ' column
> > > > > > > > showed the same primary key index being used for the â'Page lockâ' for all the
> > > > > > > > queries still open. The index name being displayed would be the primary key
> > > > > > > > index for the table of the last query I executed in Enterprise Manager.
> > > > > > > >
> > > > > > > >
> > > > > > > > Table DDL:
> > > > > > > >
> > > > > > > > CREATE TABLE [dbo].[tCompany] (
> > > > > > > > [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
> > > > > > > > [Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > > > > > > > [NameNick] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > > > > > > > [AddressLine1] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [AddressLine2] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [Country] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [Telephone] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [Telephone2] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [Fax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [AlertFax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [ContactPerson] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [ContactPerson2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [AccountNum] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [BillToAttention] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [Airport] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [Email] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [Password] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
> > > > > > > > [LastUsedTime] [datetime] NOT NULL ,
> > > > > > > > [OurCompanyID] [int] NOT NULL ,
> > > > > > > > [SalespersonID] [int] NOT NULL ,
> > > > > > > > [CustomerCompanyID] [int] NOT NULL ,
> > > > > > > > [ParentCompanyID] [int] NULL ,
> > > > > > > > [Shipper] [smallint] NOT NULL ,
> > > > > > > > [Receiver] [smallint] NOT NULL ,
> > > > > > > > [ThirdPartyBilling] [smallint] NOT NULL ,
> > > > > > > > [Cartage] [smallint] NOT NULL ,
> > > > > > > > [Customer] [smallint] NOT NULL ,
> > > > > > > > [Payee] [smallint] NOT NULL ,
> > > > > > > > [Company] [smallint] NOT NULL ,
> > > > > > > > [Hide] [smallint] NOT NULL ,
> > > > > > > > [AutoSendEmail] [smallint] NOT NULL ,
> > > > > > > > [CommericalAirApproved] [smallint] NOT NULL ,
> > > > > > > > [CarrierInsuranceAmount] [money] NULL ,
> > > > > > > > [CarrierInsuranceExpires] [datetime] NULL ,
> > > > > > > > [Notes] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [WebPageHome] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [WebPageTrackPRO] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > > > > NULL ,
> > > > > > > > [WebPageTrackFlight] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > > > > NULL ,
> > > > > > > > [SOPNotes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [WebPageTrackPROMethod] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > > > > NULL ,
> > > > > > > > [WebPassword] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [WebLoginID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [DepositACH] [smallint] NOT NULL ,
> > > > > > > > [Province] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [International] [smallint] NOT NULL ,
> > > > > > > > [AutoTrackStatus] [smallint] NOT NULL ,
> > > > > > > > [AutoTrackCharges] [smallint] NOT NULL ,
> > > > > > > > [InvoiceTerms] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [OpsPersonID] [int] NOT NULL ,
> > > > > > > > [RateSpreadsheet] [smallint] NOT NULL
> > > > > > > > ) ON [Company]
> > > > > > > > GO
> > > > > > > >
> > > > > > > > ALTER TABLE [dbo].[tCompany] WITH NOCHECK ADD
> > > > > > > > CONSTRAINT [PK_tCompany] PRIMARY KEY CLUSTERED
> > > > > > > > (
> > > > > > > > [CompanyID]
> > > > > > > > ) WITH FILLFACTOR = 90 ON [Company]
> > > > > > > > GO
> > > > > > > >
> > > > > > > > ALTER TABLE [dbo].[tCompany] ADD
> > > > > > > > CONSTRAINT [DF_tCompany_Name] DEFAULT ('N/A') FOR [Name],
> > > > > > > > CONSTRAINT [DF_tCompany_NameNick] DEFAULT ('N/A') FOR [NameNick],
> > > > > > > > CONSTRAINT [DF_tCompany_WebClient] DEFAULT ('none') FOR [Password],
> > > > > > > > CONSTRAINT [DF_tCompany_CreateDate] DEFAULT (getdate()) FOR [LastUsedTime],
> > > > > > > > CONSTRAINT [DF_tCompany_OurCompanyID] DEFAULT (3) FOR [OurCompanyID],
> > > > > > > > CONSTRAINT [DF_tCompany_SalespersonID] DEFAULT (0) FOR [SalespersonID],
> > > > > > > > CONSTRAINT [DF_tCompany_CustomerCompanyID] DEFAULT (0) FOR
> > > > > > > > [CustomerCompanyID],
> > > > > > > > CONSTRAINT [DF_tCompany_Shipper] DEFAULT (0) FOR [Shipper],
> > > > > > > > CONSTRAINT [DF_tCompany_Receiver] DEFAULT (0) FOR [Receiver],
> > > > > > > > CONSTRAINT [DF_tCompany_ThirdPartyBilling] DEFAULT (0) FOR
> > > > > > > > [ThirdPartyBilling],
> > > > > > > > CONSTRAINT [DF_tCompany_Cartage] DEFAULT (0) FOR [Cartage],
> > > > > > > > CONSTRAINT [DF_tCompany_Customer] DEFAULT (0) FOR [Customer],
> > > > > > > > CONSTRAINT [DF_tCompany_Payee] DEFAULT (0) FOR [Payee],
> > > > > > > > CONSTRAINT [DF_tCompany_Company] DEFAULT (0) FOR [Company],
> > > > > > > > CONSTRAINT [DF_tCompany_Hide] DEFAULT (0) FOR [Hide],
> > > > > > > > CONSTRAINT [DF_tCompany_AutoSendEmail] DEFAULT (0) FOR [AutoSendEmail],
> > > > > > > > CONSTRAINT [DF_tCompany_CommericalAirApproved] DEFAULT (0) FOR
> > > > > > > > [CommericalAirApproved],
> > > > > > > > CONSTRAINT [DF_tCompany_DepositACH_1] DEFAULT (0) FOR [DepositACH],
> > > > > > > > CONSTRAINT [DF_tCompany_International_1] DEFAULT (0) FOR [International],
> > > > > > > > CONSTRAINT [DF_tCompany_AutoTrackStatus] DEFAULT (0) FOR [AutoTrackStatus],
> > > > > > > > CONSTRAINT [DF_tCompany_AutoTrackCharges] DEFAULT (0) FOR [AutoTrackCharges],
> > > > > > > > CONSTRAINT [DF_tCompany_OpsPersonID] DEFAULT (0) FOR [OpsPersonID],
> > > > > > > > CONSTRAINT [DF_tCompany_RateSpreadsheet] DEFAULT (0) FOR [RateSpreadsheet]
> > > > > > > > GO
> > > > > > > >
> > > > > > > > CREATE INDEX [IX_tCompany_NameNick] ON [dbo].[tCompany]([NameNick]) WITH
> > > > > > > > FILLFACTOR = 90 ON [CompanyX]
> > > > > > > > GO
> > > > > > > >
> > > > > > > >|||I have worn myself out testing this issue and have come to the conclusion
that the database optimizer has a mind of its own and just insists on using a
table lock on my tCompany table when it does not do the same on any other
table under the same circumstances. It must be due to a combination of the
table structure and the data stored in the table. It does seems strange to
me that the fill factor has an influence on the optimizer's decision, but
then again I'm not the one who designed the optimizer.
I can override the optimizer by using the WITH (PAGLOCK) clause on the
SELECT statement. This would normally be the easiest solution to the problem
except that my business application is an MS Access front end that uses
linked tables and queries in MS Access do not allow the WITH (PAGLOCK)
syntax unless you change the query type to "Pass-Through". This change
causes the resultset to become read only which is a problem if editing is
required.
I have seen that the optimizer will choose to use page level locking if the
query limits the number of rows returned to a small percentage of the total
number of rows.
Unless someone has another way to convince the optimizer to quit locking the
entire table, I will need to recode my MS Access front end to work around the
issue. I'll have to use a combination of Pass-Through queries and smaller
result sets. Not a simple task because that tCompany table is the most used
one in the system.
"Alejandro Mesa" wrote:
> Use profiler to catch the real statement that EM send to the engine. It could
> be that it is using a table hint like (updlock or tablock).
>
> AMB
> "FloMiester" wrote:
> > Once again, the real problem is not the deadlock, it is the table lock which
> > causes the deadlock. No other query against any other table in my database
> > causes such a table lock.
> >
> > To reproduce the deadlock, I use the Enterprise Manager.
> >
> > I right click on the tCompany table. Select Open Table->Return all rows. I
> > leave that query window open. I repeat those steps a second time so I have
> > two query windows open to the tCompany table.
> >
> > I right click on "Management" and select refresh. I use the
> > Managment->Current Activity->Locks / Object screen to see that there are two
> > processes that both have Shared table locks on the tCompany table.
> >
> > Then if I try to modify any column in any row of either query the other
> > query will block it.
> >
> > I can email you the data export from my tCompany table so that you can
> > reproduce the problem yourself.
> >
> > Thanks again for your help. It is most important that I find a permanent
> > solution to this problem.
> >
> >
> > "Alejandro Mesa" wrote:
> >
> > > FloMiester,
> > >
> > > have you identified the other statement involved in the deadlock, can you
> > > post it?
> > >
> > >
> > > AMB
> > >
> > > "FloMiester" wrote:
> > >
> > > > Alejandro,
> > > >
> > > > Thanks again for the quick reply. I have already included the table
> > > > structure, etc. in my first post, perhaps you need to read it entirely to
> > > > help diagnose the problem.
> > > >
> > > > I ran the query "SELECT * FROM dbo.tCompany;". The showplan output is as
> > > > follows:
> > > >
> > > > |--Clustered Index
> > > > Scan(OBJECT:([FreightMgmt].[dbo].[tCompany].[PK_tCompany]))
> > > >
> > > > This showplan result is identical in another similar table in my database
> > > > which does not have the table locking problem:
> > > >
> > > > SELECT * from dbo.tStatusLog;
> > > >
> > > > |--Clustered Index
> > > > Scan(OBJECT:([FreightMgmt].[dbo].[tStatusLog].[PK_tStatusLog]))
> > > >
> > > > Please advise on the next steps I need to take.
> > > >
> > > > Thanks!
> > > >
> > > >
> > > > "Alejandro Mesa" wrote:
> > > >
> > > > > FloMiester,
> > > > >
> > > > > I will suggest to post table structe, including constraints and indexes, the
> > > > > "select" statement and the execution plan (see "set showplan_text / xml on"
> > > > > in BOL)
> > > > >
> > > > > Depend on the indexes, the distribution of values in the index key, and the
> > > > > filter expression being used, the query optimizer can decide to scan the
> > > > > table (heap or not) and this could cause a lock escalation.
> > > > >
> > > > > AMB
> > > > >
> > > > > "FloMiester" wrote:
> > > > >
> > > > > > Alehandro,
> > > > > >
> > > > > > Thank you for the quick reply. I already had read the two articles you
> > > > > > refer to plus at least half a dozen more on deadlocks in the MS knowledge
> > > > > > base. Unfortunately they do not answer the question that is the root of the
> > > > > > problem. Why is the optimizer forcing the entire table to be locked when only
> > > > > > a simple SELECT query is issued?
> > > > > >
> > > > > > At most, only a page lock should be issued. No other tables in my SQL
> > > > > > database exibit this problem. I have have worked with a dozen different
> > > > > > databases over the last 20 years and I have yet to run up against this type
> > > > > > of locking problem.
> > > > > >
> > > > > > So perhaps I mistitled this posting, the correct title should be "How can a
> > > > > > simple select statement lock an entire table?"
> > > > > >
> > > > > > If you can find a Knowledge Base artical that answers that question and
> > > > > > gives me a way to avoid it, my problem would be solved for good. You help in
> > > > > > this matter is much appreciated.
> > > > > >
> > > > > > Many Thanks in Advance.
> > > > > >
> > > > > > ----
> > > > > >
> > > > > > "Alejandro Mesa" wrote:
> > > > > >
> > > > > > > Troubleshooting Deadlocks
> > > > > > > http://msdn2.microsoft.com/en-us/library/Aa937573(SQL.80).aspx
> > > > > > >
> > > > > > > SQL Server technical bulletin - How to resolve a deadlock
> > > > > > > http://support.microsoft.com/kb/832524/
> > > > > > >
> > > > > > >
> > > > > > > AMB
> > > > > > >
> > > > > > > "FloMiester" wrote:
> > > > > > >
> > > > > > > > I am using SQL Server 2000 sp4 and have a single table that is now getting a
> > > > > > > > deadlock problem after 3 years of use. It started when the number of rows
> > > > > > > > went past 16K.
> > > > > > > >
> > > > > > > > The problem is that a query returning more than ~500 rows against the table
> > > > > > > > causes the optimizer to use a single "Shared Table" lock for the query
> > > > > > > > instead of its normal behavior of issuing two locks: 1 a Page Shared Lock (S)
> > > > > > > > and 2 a Table Intent Shared Lock (IS). The single Shared Table lock causes
> > > > > > > > any other clients wishing to modify the table to deadlock waiting for the
> > > > > > > > table lock to be freed up.
> > > > > > > >
> > > > > > > > FYI. I have other tables in the same database over 16K rows that do not have
> > > > > > > > this problem.
> > > > > > > >
> > > > > > > > I have manually created the table on a test database on another SQL
> > > > > > > > database, on another machine, copied the data and the same problem occurred.
> > > > > > > >
> > > > > > > > Removing a few thousand rows fixes the problem, but is an unaccceptable
> > > > > > > > solution.
> > > > > > > >
> > > > > > > > A temporary fix is to change the fill factor on the primary key index to a
> > > > > > > > lower number, but after a few days and more rows are added, the deadlock
> > > > > > > > occurs again, until the fill factor is lowered again. I am at 50% now.
> > > > > > > >
> > > > > > > > I tried removing the cluster on the primary key index and moving the index
> > > > > > > > into another table space, but the same problem occurs once again if I shrink
> > > > > > > > the tables space the table is in (basically removing any fill space).
> > > > > > > >
> > > > > > > > Removing an unneeded column also temporarily helped, but perhaps something
> > > > > > > > else was going on that made the locking issue go away temporarily.
> > > > > > > >
> > > > > > > > So it seems that for this particular table the optimizer is taking into
> > > > > > > > consideration how much fill space is in the table to choose which type of
> > > > > > > > locking scheme to use. I can produce this problem simply using the
> > > > > > > > Enterprise Manager and opening a "SELECT *" query on the table and using the
> > > > > > > > "Current Activity" window to view the locks placed on the table.
> > > > > > > >
> > > > > > > > I am in desperate need of a proper fix for this problem. Does anyone have
> > > > > > > > any ideas?
> > > > > > > >
> > > > > > > > FYI: I also noticed what appears to be a bug with Enterprise Manager. When
> > > > > > > > looking at the â'Locks / Process IDâ' or the â'Locks / Objectâ' screens for locks
> > > > > > > > issued by several "SELECT *" queries that were still open, the â'Indexâ' column
> > > > > > > > showed the same primary key index being used for the â'Page lockâ' for all the
> > > > > > > > queries still open. The index name being displayed would be the primary key
> > > > > > > > index for the table of the last query I executed in Enterprise Manager.
> > > > > > > >
> > > > > > > >
> > > > > > > > Table DDL:
> > > > > > > >
> > > > > > > > CREATE TABLE [dbo].[tCompany] (
> > > > > > > > [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
> > > > > > > > [Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > > > > > > > [NameNick] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
> > > > > > > > [AddressLine1] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [AddressLine2] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [Country] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [Telephone] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [Telephone2] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [Fax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [AlertFax] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [ContactPerson] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [ContactPerson2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [AccountNum] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [BillToAttention] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [Airport] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [Email] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [Password] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
> > > > > > > > [LastUsedTime] [datetime] NOT NULL ,
> > > > > > > > [OurCompanyID] [int] NOT NULL ,
> > > > > > > > [SalespersonID] [int] NOT NULL ,
> > > > > > > > [CustomerCompanyID] [int] NOT NULL ,
> > > > > > > > [ParentCompanyID] [int] NULL ,
> > > > > > > > [Shipper] [smallint] NOT NULL ,
> > > > > > > > [Receiver] [smallint] NOT NULL ,
> > > > > > > > [ThirdPartyBilling] [smallint] NOT NULL ,
> > > > > > > > [Cartage] [smallint] NOT NULL ,
> > > > > > > > [Customer] [smallint] NOT NULL ,
> > > > > > > > [Payee] [smallint] NOT NULL ,
> > > > > > > > [Company] [smallint] NOT NULL ,
> > > > > > > > [Hide] [smallint] NOT NULL ,
> > > > > > > > [AutoSendEmail] [smallint] NOT NULL ,
> > > > > > > > [CommericalAirApproved] [smallint] NOT NULL ,
> > > > > > > > [CarrierInsuranceAmount] [money] NULL ,
> > > > > > > > [CarrierInsuranceExpires] [datetime] NULL ,
> > > > > > > > [Notes] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [WebPageHome] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [WebPageTrackPRO] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > > > > NULL ,
> > > > > > > > [WebPageTrackFlight] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > > > > NULL ,
> > > > > > > > [SOPNotes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [WebPageTrackPROMethod] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CS_AS
> > > > > > > > NULL ,
> > > > > > > > [WebPassword] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [WebLoginID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [DepositACH] [smallint] NOT NULL ,
> > > > > > > > [Province] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > > > > > > > [International] [smallint] NOT NULL ,
> > > > > > > > [AutoTrackStatus] [smallint] NOT NULL ,
> > > > > > > > [AutoTrackCharges] [smallint] NOT NULL ,
> > > > > > > > [InvoiceTerms] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> > > > > > > > [OpsPersonID] [int] NOT NULL ,
> > > > > > > > [RateSpreadsheet] [smallint] NOT NULL
> > > > > > > > ) ON [Company]
> > > > > > > > GO
> > > > > > > >
> > > > > > > > ALTER TABLE [dbo].[tCompany] WITH NOCHECK ADD
> > > > > > > > CONSTRAINT [PK_tCompany] PRIMARY KEY CLUSTERED
> > > > > > > > (
> > > > > > > > [CompanyID]
> > > > > > > > ) WITH FILLFACTOR = 90 ON [Company]
> > > > > > > > GO
> > > > > > > >
> > > > > > > > ALTER TABLE [dbo].[tCompany] ADD
> > > > > > > > CONSTRAINT [DF_tCompany_Name] DEFAULT ('N/A') FOR [Name],
> > > > > > > > CONSTRAINT [DF_tCompany_NameNick] DEFAULT ('N/A') FOR [NameNick],
> > > > > > > > CONSTRAINT [DF_tCompany_WebClient] DEFAULT ('none') FOR [Password],
> > > > > > > > CONSTRAINT [DF_tCompany_CreateDate] DEFAULT (getdate()) FOR [LastUsedTime],
> > > > > > > > CONSTRAINT [DF_tCompany_OurCompanyID] DEFAULT (3) FOR [OurCompanyID],
> > > > > > > > CONSTRAINT [DF_tCompany_SalespersonID] DEFAULT (0) FOR [SalespersonID],
> > > > > > > > CONSTRAINT [DF_tCompany_CustomerCompanyID] DEFAULT (0) FOR
> > > > > > > > [CustomerCompanyID],
> > > > > > > > CONSTRAINT [DF_tCompany_Shipper] DEFAULT (0) FOR [Shipper],
> > > > > > > > CONSTRAINT [DF_tCompany_Receiver] DEFAULT (0) FOR [Receiver],
> > > > > > > > CONSTRAINT [DF_tCompany_ThirdPartyBilling] DEFAULT (0) FOR
> > > > > > > > [ThirdPartyBilling],
> > > > > > > > CONSTRAINT [DF_tCompany_Cartage] DEFAULT (0) FOR [Cartage],
> > > > > > > > CONSTRAINT [DF_tCompany_Customer] DEFAULT (0) FOR [Customer],
> > > > > > > > CONSTRAINT [DF_tCompany_Payee] DEFAULT (0) FOR [Payee],
> > > > > > > > CONSTRAINT [DF_tCompany_Company] DEFAULT (0) FOR [Company],
> > > > > > > > CONSTRAINT [DF_tCompany_Hide] DEFAULT (0) FOR [Hide],
> > > > > > > > CONSTRAINT [DF_tCompany_AutoSendEmail] DEFAULT (0) FOR [AutoSendEmail],
> > > > > > > > CONSTRAINT [DF_tCompany_CommericalAirApproved] DEFAULT (0) FOR
> > > > > > > > [CommericalAirApproved],
> > > > > > > > CONSTRAINT [DF_tCompany_DepositACH_1] DEFAULT (0) FOR [DepositACH],
> > > > > > > > CONSTRAINT [DF_tCompany_International_1] DEFAULT (0) FOR [International],
> > > > > > > > CONSTRAINT [DF_tCompany_AutoTrackStatus] DEFAULT (0) FOR [AutoTrackStatus],
> > > > > > > > CONSTRAINT [DF_tCompany_AutoTrackCharges] DEFAULT (0) FOR [AutoTrackCharges],
> > > > > > > > CONSTRAINT [DF_tCompany_OpsPersonID] DEFAULT (0) FOR [OpsPersonID],
> > > > > > > > CONSTRAINT [DF_tCompany_RateSpreadsheet] DEFAULT (0) FOR [RateSpreadsheet]
> > > > > > > > GO
> > > > > > > >
> > > > > > > > CREATE INDEX [IX_tCompany_NameNick] ON [dbo].[tCompany]([NameNick]) WITH
> > > > > > > > FILLFACTOR = 90 ON [CompanyX]
> > > > > > > > GO
> > > > > > > >
> > > > > > > >|||FloMiester wrote:
> I have worn myself out testing this issue and have come to the conclusion
> that the database optimizer has a mind of its own and just insists on using a
> table lock on my tCompany table when it does not do the same on any other
> table under the same circumstances. It must be due to a combination of the
> table structure and the data stored in the table. It does seems strange to
> me that the fill factor has an influence on the optimizer's decision, but
> then again I'm not the one who designed the optimizer.
> I can override the optimizer by using the WITH (PAGLOCK) clause on the
> SELECT statement. This would normally be the easiest solution to the problem
> except that my business application is an MS Access front end that uses
> linked tables and queries in MS Access do not allow the WITH (PAGLOCK)
> syntax unless you change the query type to "Pass-Through". This change
> causes the resultset to become read only which is a problem if editing is
> required.
> I have seen that the optimizer will choose to use page level locking if the
> query limits the number of rows returned to a small percentage of the total
> number of rows.
> Unless someone has another way to convince the optimizer to quit locking the
> entire table, I will need to recode my MS Access front end to work around the
> issue. I'll have to use a combination of Pass-Through queries and smaller
> result sets. Not a simple task because that tCompany table is the most used
> one in the system.
You could try the following: You could create a view in which you use
the PAGLOCK hint, and then in MS Access select from the view instead of
the table.
--
Gert-Jan|||What a great solution. I can kick myself for not thinking of it first !
I created a view "SELECT * from tCompany WITH(PAGLOCK);" and then linked MS
Access to the view instead of the table (made sure to mark the proper primary
key column when linking so the resultset would be editable) and I no longer
have the deadlock problem.
Funny thing is that Enterprise Manager shows that an Intent Shared table
lock is on the table instead the page lock the view specifies. Oh well I'm
just happy it works.
Thanks for your help!!!
"Gert-Jan Strik" wrote:
> FloMiester wrote:
> >
> > I have worn myself out testing this issue and have come to the conclusion
> > that the database optimizer has a mind of its own and just insists on using a
> > table lock on my tCompany table when it does not do the same on any other
> > table under the same circumstances. It must be due to a combination of the
> > table structure and the data stored in the table. It does seems strange to
> > me that the fill factor has an influence on the optimizer's decision, but
> > then again I'm not the one who designed the optimizer.
> >
> > I can override the optimizer by using the WITH (PAGLOCK) clause on the
> > SELECT statement. This would normally be the easiest solution to the problem
> > except that my business application is an MS Access front end that uses
> > linked tables and queries in MS Access do not allow the WITH (PAGLOCK)
> > syntax unless you change the query type to "Pass-Through". This change
> > causes the resultset to become read only which is a problem if editing is
> > required.
> >
> > I have seen that the optimizer will choose to use page level locking if the
> > query limits the number of rows returned to a small percentage of the total
> > number of rows.
> >
> > Unless someone has another way to convince the optimizer to quit locking the
> > entire table, I will need to recode my MS Access front end to work around the
> > issue. I'll have to use a combination of Pass-Through queries and smaller
> > result sets. Not a simple task because that tCompany table is the most used
> > one in the system.
> You could try the following: You could create a view in which you use
> the PAGLOCK hint, and then in MS Access select from the view instead of
> the table.
> --
> Gert-Jan
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment