OPTIMIZER LOCK HINTS

I want to populate ULF with all the records from ULF_Backup in my License
database.
I can't get around this error, "OPTIMIZER LOCK HINTS"
Here is the SQL code I'm running from Query Analyzer.
sp_dbcmptlevel 'license', 70
GO
SET IDENTITY_INSERT ULF ON
INSERT INTO ULF (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY, HARVEST_FLAG,
AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME, TRANSACTION_DATE,
NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE, LICENSE_STATUS,
STATUS_DATE)
SELECT * FROM ULF_Backup (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM,
CONFIRMATION, ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME,
TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE,
LICENSE_STATUS, STATUS_DATE)
WITH (NO LOCK)
SET IDENTITY_INSERT ULF OFFPull out the select statement by itself and try to execute it. Before you
do that, do you see anything wrong with the sytax you are using? Hint -
there is something very wrong.|||The SELECT statement column was misplaced. Also, there was an extraneous
space in the NOLOCK hint. Try:
SET IDENTITY_INSERT ULF ON
GO
INSERT INTO ULF (
SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE)
SELECT
SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE
FROM ULF_Backup WITH (NOLOCK)
GO
SET IDENTITY_INSERT ULF OFF
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike L" <Cadel@.nospam.nospam> wrote in message
news:9AFF21C9-C89A-4051-9C2F-9E5C4D91B3B4@.microsoft.com...
>I want to populate ULF with all the records from ULF_Backup in my License
> database.
> I can't get around this error, "OPTIMIZER LOCK HINTS"
> Here is the SQL code I'm running from Query Analyzer.
> sp_dbcmptlevel 'license', 70
> GO
> SET IDENTITY_INSERT ULF ON
> INSERT INTO ULF (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY, HARVEST_FLAG,
> AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME, TRANSACTION_DATE,
> NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE, LICENSE_STATUS,
> STATUS_DATE)
> SELECT * FROM ULF_Backup (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM,
> CONFIRMATION, ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME,
> TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE,
> LICENSE_STATUS, STATUS_DATE)
> WITH (NO LOCK)
> SET IDENTITY_INSERT ULF OFF|||no sure what that error is, as there are multiple errors for locking hints
but there are more than that here:
1. mixed INSERT..SELECT and INSERT..VALUES syntax.
2. NOLOCK hint is one word
3. are you truncating the ULF table first? if not, you'll need to check
for existing rows in the ULF table, based on the key. [as no DDL posted,
assuming SPORTSMAN_KEY, LICENSE_NUM is the key)
e.g.
INSERT INTO ULF (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY, HARVEST_FLAG,
AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME, TRANSACTION_DATE,
NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE, LICENSE_STATUS,
STATUS_DATE)
SELECT SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM,
CONFIRMATION, ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME,
TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE,
LICENSE_STATUS, STATUS_DATE
FROM ULF_Backup WITH (NOLOCK)
WHERE NOT EXISTS (
SELECT * FROM ULF
WHERE SPORTSMAN_KEY = ULF_Backup.SPORTSMAN_KEY
AND LICENSE_NUM = ULF_Backup.LICENSE_NUM
)
Mike L wrote:
> I want to populate ULF with all the records from ULF_Backup in my License
> database.
> I can't get around this error, "OPTIMIZER LOCK HINTS"
> Here is the SQL code I'm running from Query Analyzer.
> sp_dbcmptlevel 'license', 70
> GO
> SET IDENTITY_INSERT ULF ON
> INSERT INTO ULF (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY, HARVEST_FLAG,
> AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME, TRANSACTION_DATE,
> NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE, LICENSE_STATUS,
> STATUS_DATE)
> SELECT * FROM ULF_Backup (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM,
> CONFIRMATION, ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME,
> TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE,
> LICENSE_STATUS, STATUS_DATE)
> WITH (NO LOCK)
> SET IDENTITY_INSERT ULF OFF|||Mike,
My guess is that you are passing a column name as a parameter to
a user-defined function (or something the parser thinks is one). What
is FROM ULF_Backup(column names here?) supposed to mean?
Generally the only time there is a parameter list in this part of a FROM
clause is when the source is a table-valued user-defined function, and
in that case the parameters cannot be column names.
Steve Kass
Drew University
Mike L wrote:

>I want to populate ULF with all the records from ULF_Backup in my License
>database.
>I can't get around this error, "OPTIMIZER LOCK HINTS"
>Here is the SQL code I'm running from Query Analyzer.
>sp_dbcmptlevel 'license', 70
>GO
>SET IDENTITY_INSERT ULF ON
>INSERT INTO ULF (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
>ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY, HARVEST_FLAG,
>AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME, TRANSACTION_DATE,
>NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE, LICENSE_STATUS,
>STATUS_DATE)
>SELECT * FROM ULF_Backup (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM,
>CONFIRMATION, ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
>HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME,
>TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE,
>LICENSE_STATUS, STATUS_DATE)
>WITH (NO LOCK)
>SET IDENTITY_INSERT ULF OFF
>|||I get the following error message when I run your code. Why and how do I
correct this error?
Server: Msg 545, Level 16, State 1, Line 1
Explicit value must be specified for identity column in table 'ULF' when
IDENTITY_INSERT is set to ON.
"Dan Guzman" wrote:

> The SELECT statement column was misplaced. Also, there was an extraneous
> space in the NOLOCK hint. Try:
> SET IDENTITY_INSERT ULF ON
> GO
> INSERT INTO ULF (
> SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
> HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
> DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE)
> SELECT
> SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
> HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
> DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE
> FROM ULF_Backup WITH (NOLOCK)
> GO
> SET IDENTITY_INSERT ULF OFF
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mike L" <Cadel@.nospam.nospam> wrote in message
> news:9AFF21C9-C89A-4051-9C2F-9E5C4D91B3B4@.microsoft.com...
>
>|||I don't get an error. Can you post your CREATE TABLE statements?
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike L" <Cadel@.nospam.nospam> wrote in message
news:0B0CF504-D831-4B47-A44A-49387B11514B@.microsoft.com...
>I get the following error message when I run your code. Why and how do I
> correct this error?
> Server: Msg 545, Level 16, State 1, Line 1
> Explicit value must be specified for identity column in table 'ULF' when
> IDENTITY_INSERT is set to ON.
> "Dan Guzman" wrote:
>|||The code is long because I created a script of the table. Here is the scrip
t.
CREATE TABLE [dbo].[ULF] (
[ULF_KEY] [Surrogate_Key] IDENTITY (1, 1) NOT NULL ,
[SPORTSMAN_KEY] [int] NULL ,
[LICENSE_YEAR] [smallint] NULL ,
[LICENSE_NUM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CONFIRMATION] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ULF_FORM_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BOOK_FORM_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BOOK_NUM] [int] NULL ,
[DEALER_BATCH_KEY] [int] NULL ,
[HARVEST_FLAG] [tinyint] NULL ,
[AMOUNT] [money] NULL ,
[COUNTY_OF_DEALER] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HARVEST_DATE] [datetime] NULL ,
[HARVEST_TIME] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TRANSACTION_DATE] [datetime] NULL ,
[NO_PENALTY_FLAG] [tinyint] NULL ,
[BP_CONFIRM_NUM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DUPLICATION_DATE] [datetime] NULL ,
[LICENSE_STATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[STATUS_DATE] [datetime] NOT NULL ,
[Lchg_Userid] [Last_Chg_Userid] NOT NULL ,
[Lchg_Date] [Last_Chg_Date] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [XIF418ULF] ON [dbo].[ULF]([BOOK_FORM_TYPE],
[BOOK_NUM]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[ULF] ADD
CONSTRAINT [DF__ULF__Lchg_Userid__789EE131] DEFAULT (user_name()) FOR
[Lchg_Userid],
CONSTRAINT [DF__ULF__Lchg_Date__7993056A] DEFAULT (getdate()) FOR
[Lchg_Date],
CONSTRAINT [PK__ULF__7A8729A3] PRIMARY KEY NONCLUSTERED
(
[ULF_KEY]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE UNIQUE INDEX [Num_Form_Type_Idx] ON [dbo].[ULF]([LICENSE_NUM],
[ULF_FORM_TYPE], [LICENSE_STATUS]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF332ULF] ON [dbo].[ULF]([SPORTSMAN_KEY]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [XIF349ULF] ON [dbo].[ULF]([LICENSE_STATUS], [AMOUNT]) WITH
FILLFACTOR = 90, PAD_INDEX ON [PRIMARY]
GO
CREATE INDEX [XIF455ULF] ON [dbo].[ULF]([DEALER_BATCH_KEY]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF421ULF] ON [dbo].[ULF]([COUNTY_OF_DEALER]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF432ULF] ON [dbo].[ULF]([LICENSE_YEAR]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
CREATE INDEX [XIF440ULF] ON [dbo].[ULF]([ULF_FORM_TYPE]) WITH FILLFACTOR
= 90, PAD_INDEX ON [PRIMARY]
GO
CREATE INDEX [ULF_Num_Idx] ON [dbo].[ULF]([LICENSE_NUM]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [IX_ULF] ON [dbo].[ULF]([TRANSACTION_DATE]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [Index_BookNum] ON [dbo].[ULF]([BOOK_NUM]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
setuser
GO
EXEC sp_bindrule N'[dbo].[ZERO_ONE]', N'[ULF].[HARVEST_FLAG]'
GO
EXEC sp_bindrule N'[dbo].[ZERO_ONE]', N'[ULF].[NO_PENALTY_FLAG]'
GO
setuser
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create trigger tD_ULF on dbo.ULF for DELETE as
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* DELETE trigger on ULF */
begin
declare @.errno int,
@.errmsg varchar(255)
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/461 DEALER_TRAN ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,DEALER_TRAN
where
/* %JoinFKPK(DEALER_TRAN,deleted," = "," and") */
DEALER_TRAN.LOST_ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE ULF because DEALER_TRAN exists.'
goto error
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/451 REVENUE_DETAIL ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,REVENUE_DETAIL
where
/* %JoinFKPK(REVENUE_DETAIL,deleted," = "," and") */
REVENUE_DETAIL.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE ULF because REVENUE_DETAIL exists.'
goto error
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/333 ULF_QUESTIONS ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,ULF_QUESTIONS
where
/* %JoinFKPK(ULF_QUESTIONS,deleted," = "," and") */
ULF_QUESTIONS.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE ULF because ULF_QUESTIONS exists.'
goto error
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/4 ULF_PRIVILEGES ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,ULF_PRIVILEGES
where
/* %JoinFKPK(ULF_PRIVILEGES,deleted," = "," and") */
ULF_PRIVILEGES.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE ULF because ULF_PRIVILEGES exists.'
goto error
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/335 HARVEST_TOTALS ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,HARVEST_TOTALS
where
/* %JoinFKPK(HARVEST_TOTALS,deleted," = "," and") */
HARVEST_TOTALS.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE ULF because HARVEST_TOTALS exists.'
goto error
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create trigger tI_ULF on dbo.ULF for INSERT as
/* BTG Incorporated - Builtin Trigger Tue Nov 23 18:59:15 1999 */
/* INSERT trigger on ULF */
begin
declare @.numrows int,
@.nullcnt int,
@.validcnt int,
@.errno int,
@.errmsg varchar(255)
/* BTG Incorporated - Builtin Trigger Tue Nov 23 18:59:15 1999 */
/* Update Last Change UserID and Last Change Date */
UPDATE ULF
SET lchg_date = GETDATE(),
lchg_userid = CURRENT_USER
FROM ULF, inserted
WHERE ULF.ULF_KEY = inserted.ULF_KEY
select @.numrows = @.@.rowcount
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF_FORM_TYPE R/440 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(ULF_FORM_TYPE)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,ULF_FORM_TYPE
where
/* %JoinFKPK(inserted,ULF_FORM_TYPE) */
inserted.ULF_FORM_TYPE = ULF_FORM_TYPE.ULF_FORM_TYPE
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because ULF_FORM_TYPE does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_YEAR R/432 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(LICENSE_YEAR)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_YEAR
where
/* %JoinFKPK(inserted,LICENSE_YEAR) */
inserted.LICENSE_YEAR = LICENSE_YEAR.LICENSE_YEAR
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.LICENSE_YEAR is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because LICENSE_YEAR does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* COUNTY R/421 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(COUNTY_OF_DEALER)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,COUNTY
where
/* %JoinFKPK(inserted,COUNTY) */
inserted.COUNTY_OF_DEALER = COUNTY.COUNTY_CODE
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.COUNTY_OF_DEALER is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because COUNTY does not exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* DEALER_BATCH R/455 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(DEALER_BATCH_KEY)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,DEALER_BATCH
where
/* %JoinFKPK(inserted,DEALER_BATCH) */
inserted.DEALER_BATCH_KEY = DEALER_BATCH.DEALER_BATCH_KEY
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.DEALER_BATCH_KEY is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because DEALER_BATCH does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_BOOK R/418 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(BOOK_FORM_TYPE) or
update(BOOK_NUM)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_BOOK
where
/* %JoinFKPK(inserted,LICENSE_BOOK) */
inserted.BOOK_FORM_TYPE = LICENSE_BOOK.BOOK_FORM_TYPE and
inserted.BOOK_NUM = LICENSE_BOOK.BOOK_NUM
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.BOOK_FORM_TYPE is null and
inserted.BOOK_NUM is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because LICENSE_BOOK does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_STATUS R/349 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(LICENSE_STATUS)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_STATUS
where
/* %JoinFKPK(inserted,LICENSE_STATUS) */
inserted.LICENSE_STATUS = LICENSE_STATUS.LICENSE_STATUS
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because LICENSE_STATUS does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* SPORTSMAN R/332 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(SPORTSMAN_KEY)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,SPORTSMAN
where
/* %JoinFKPK(inserted,SPORTSMAN) */
inserted.SPORTSMAN_KEY = SPORTSMAN.SPORTSMAN_KEY
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.SPORTSMAN_KEY is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because SPORTSMAN does not exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create trigger tU_ULF on dbo.ULF for UPDATE as
/* BTG Incorporated - Builtin Trigger Tue Nov 23 18:59:15 1999 */
/* UPDATE trigger on ULF */
begin
declare @.numrows int,
@.nullcnt int,
@.validcnt int,
@.insULF_KEY Surrogate_Key,
@.errno int,
@.errmsg varchar(255)
/* BTG Incorporated - Builtin Trigger Tue Nov 23 18:59:15 1999 */
/* Update Last Change UserID and Last Change Date */
UPDATE ULF
SET lchg_date = GETDATE(),
lchg_userid = CURRENT_USER
FROM ULF, inserted
WHERE ULF.ULF_KEY = inserted.ULF_KEY
select @.numrows = @.@.rowcount
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/461 DEALER_TRAN ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ULF_KEY)
begin
if exists (
select * from deleted,DEALER_TRAN
where
/* %JoinFKPK(DEALER_TRAN,deleted," = "," and") */
DEALER_TRAN.LOST_ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE ULF because DEALER_TRAN exists.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/451 REVENUE_DETAIL ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ULF_KEY)
begin
if exists (
select * from deleted,REVENUE_DETAIL
where
/* %JoinFKPK(REVENUE_DETAIL,deleted," = "," and") */
REVENUE_DETAIL.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE ULF because REVENUE_DETAIL exists.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/333 ULF_QUESTIONS ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ULF_KEY)
begin
if exists (
select * from deleted,ULF_QUESTIONS
where
/* %JoinFKPK(ULF_QUESTIONS,deleted," = "," and") */
ULF_QUESTIONS.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE ULF because ULF_QUESTIONS exists.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/4 ULF_PRIVILEGES ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ULF_KEY)
begin
if exists (
select * from deleted,ULF_PRIVILEGES
where
/* %JoinFKPK(ULF_PRIVILEGES,deleted," = "," and") */
ULF_PRIVILEGES.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE ULF because ULF_PRIVILEGES exists.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/335 HARVEST_TOTALS ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ULF_KEY)
begin
if exists (
select * from deleted,HARVEST_TOTALS
where
/* %JoinFKPK(HARVEST_TOTALS,deleted," = "," and") */
HARVEST_TOTALS.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE ULF because HARVEST_TOTALS exists.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF_FORM_TYPE R/440 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(ULF_FORM_TYPE)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,ULF_FORM_TYPE
where
/* %JoinFKPK(inserted,ULF_FORM_TYPE) */
inserted.ULF_FORM_TYPE = ULF_FORM_TYPE.ULF_FORM_TYPE
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because ULF_FORM_TYPE does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_YEAR R/432 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(LICENSE_YEAR)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_YEAR
where
/* %JoinFKPK(inserted,LICENSE_YEAR) */
inserted.LICENSE_YEAR = LICENSE_YEAR.LICENSE_YEAR
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.LICENSE_YEAR is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because LICENSE_YEAR does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* COUNTY R/421 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(COUNTY_OF_DEALER)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,COUNTY
where
/* %JoinFKPK(inserted,COUNTY) */
inserted.COUNTY_OF_DEALER = COUNTY.COUNTY_CODE
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.COUNTY_OF_DEALER is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because COUNTY does not exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* DEALER_BATCH R/455 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(DEALER_BATCH_KEY)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,DEALER_BATCH
where
/* %JoinFKPK(inserted,DEALER_BATCH) */
inserted.DEALER_BATCH_KEY = DEALER_BATCH.DEALER_BATCH_KEY
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.DEALER_BATCH_KEY is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because DEALER_BATCH does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_BOOK R/418 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(BOOK_FORM_TYPE) or
update(BOOK_NUM)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_BOOK
where
/* %JoinFKPK(inserted,LICENSE_BOOK) */
inserted.BOOK_FORM_TYPE = LICENSE_BOOK.BOOK_FORM_TYPE and
inserted.BOOK_NUM = LICENSE_BOOK.BOOK_NUM
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.BOOK_FORM_TYPE is null and
inserted.BOOK_NUM is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because LICENSE_BOOK does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_STATUS R/349 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(LICENSE_STATUS)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_STATUS
where
/* %JoinFKPK(inserted,LICENSE_STATUS) */
inserted.LICENSE_STATUS = LICENSE_STATUS.LICENSE_STATUS
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because LICENSE_STATUS does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* SPORTSMAN R/332 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(SPORTSMAN_KEY)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,SPORTSMAN
where
/* %JoinFKPK(inserted,SPORTSMAN) */
inserted.SPORTSMAN_KEY = SPORTSMAN.SPORTSMAN_KEY
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.SPORTSMAN_KEY is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because SPORTSMAN does not exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Dan Guzman" wrote:

> I don't get an error. Can you post your CREATE TABLE statements?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mike L" <Cadel@.nospam.nospam> wrote in message
> news:0B0CF504-D831-4B47-A44A-49387B11514B@.microsoft.com...
>
>|||Assuming the ULF_Backup backup table has the same schema as table ULF, all
you need to do is add the ULF_KEY column so that you can specify
IDENTITY_INSERT:
SET IDENTITY_INSERT ULF ON
GO
INSERT INTO ULF (
ULF_KEY,
SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE)
SELECT
ULF_KEY,
SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE
FROM ULF_Backup WITH (NOLOCK)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike L" <Cadel@.nospam.nospam> wrote in message
news:F0077D93-CEA3-445E-A86A-688817B67352@.microsoft.com...
> The code is long because I created a script of the table. Here is the
> script.|||That fixed the problem.
Thank you.
"Dan Guzman" wrote:

> Assuming the ULF_Backup backup table has the same schema as table ULF, all
> you need to do is add the ULF_KEY column so that you can specify
> IDENTITY_INSERT:
> SET IDENTITY_INSERT ULF ON
> GO
> INSERT INTO ULF (
> ULF_KEY,
> SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
> HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
> DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE)
> SELECT
> ULF_KEY,
> SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
> HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
> DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE
> FROM ULF_Backup WITH (NOLOCK)
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mike L" <Cadel@.nospam.nospam> wrote in message
> news:F0077D93-CEA3-445E-A86A-688817B67352@.microsoft.com...
>
>

Optimizer chooses Shared Table Lock causing deadlock

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
>