lock info

hi,
from Activity Monitor, i found that there's a process (suspended) holding
over a thousand locks on a table named tbl_BusinessRecordDetailed.
i have 2 questions:
#1 - is there a way to find out which row it's locking?
#2 - what exactly is causing the locks? since the mode is S, which means
it's read only'
much appreciated.
from activity monitor:
resouce_type is all KEY
request_mode is all S
request_type is all LOCK
request_status is all GRANT
resouce_lock_partition is all 0
request_reference_count is all 0
request_exec_context_id is all 0
request_request_id is all 0
request_lifetime is all 1
request_owner_type is all TRANSACTION
but resource_description are pretty much all different, some examples:
1:210617
1:289260
(8800ee1b2515)
(b000d565e6d8)
(af008d6eefc0)
below is the table definition and the indexes:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tbl_BusinessRecordDetailed](
[recordID] [int] IDENTITY(1,1) NOT NULL,
[rowVersion] [timestamp] NOT NULL,
[name] [varchar](200) NULL,
[address] [varchar](200) NULL,
[crossstreet] [varchar](200) NULL,
[neighborhood] [varchar](200) NULL,
[borough] [varchar](200) NULL,
[city] [varchar](50) NULL,
[zip] [varchar](20) NULL,
[state] [varchar](2) NULL,
[telephone] [varchar](200) NULL,
[fax] [varchar](10) NULL,
[url] [varchar](500) NULL,
[bookingUrl] [varchar](500) NULL,
[bus] [varchar](200) NULL,
[subway] [varchar](300) NULL,
[description] [varchar](max) NULL,
[hours] [varchar](2000) NULL,
[admission] [varchar](2000) NULL,
[otherinfo] [varchar](1000) NULL,
[specialDirection] [varchar](max) NULL,
[sortOrder] [int] NOT NULL CONSTRAINT [DF__tbl_Busin__sortO__7ABC33CD]
DEFAULT ((0)),
[dateCreated] [datetime] NOT NULL CONSTRAINT
[DF__tbl_Busin__dateC__7BB05806] DEFAULT (getdate()),
[copyright] [varchar](100) NULL,
[mappedVarchar200_1] [varchar](200) NULL,
[mappedVarchar200_2] [varchar](200) NULL,
[mappedVarchar200_3] [varchar](200) NULL,
[mappedVarchar200_4] [varchar](200) NULL,
[mappedVarchar2000_1] [varchar](2000) NULL,
[mappedVarchar2000_2] [varchar](2000) NULL,
[mappedVarcharMax_1] [varchar](max) NULL,
[dateModified] [datetime] NULL,
[email] [varchar](50) NULL,
CONSTRAINT [PK__tbl_BusinessReco__79C80F94] PRIMARY KEY CLUSTERED
(
[recordID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name = N'idx_biz_datecreated')
CREATE NONCLUSTERED INDEX [idx_biz_datecreated] ON
[dbo].[tbl_BusinessRecordDetailed]
(
[dateCreated] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name = N'idx_biz_datemodifed')
CREATE NONCLUSTERED INDEX [idx_biz_datemodifed] ON
[dbo].[tbl_BusinessRecordDetailed]
(
[dateModified] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]Hi
Your lock is a shared lock so it will not be stopping other processes from
reading this information see
http://msdn2.microsoft.com/en-us/library/aa213039(sql.80).aspx
and http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx
Using DBCC INPUTBUFFER on the SID for the process should indicate the last
statement issued by the client.
John
"tin" wrote:
> hi,
> from Activity Monitor, i found that there's a process (suspended) holding
> over a thousand locks on a table named tbl_BusinessRecordDetailed.
> i have 2 questions:
> #1 - is there a way to find out which row it's locking?
> #2 - what exactly is causing the locks? since the mode is S, which means
> it's read only'
> much appreciated.
>
> from activity monitor:
> resouce_type is all KEY
> request_mode is all S
> request_type is all LOCK
> request_status is all GRANT
> resouce_lock_partition is all 0
> request_reference_count is all 0
> request_exec_context_id is all 0
> request_request_id is all 0
> request_lifetime is all 1
> request_owner_type is all TRANSACTION
> but resource_description are pretty much all different, some examples:
> 1:210617
>
> 1:289260
>
> (8800ee1b2515)
>
> (b000d565e6d8)
>
> (af008d6eefc0)
>
>
>
> below is the table definition and the indexes:
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND type in (N'U'))
> BEGIN
> CREATE TABLE [dbo].[tbl_BusinessRecordDetailed](
> [recordID] [int] IDENTITY(1,1) NOT NULL,
> [rowVersion] [timestamp] NOT NULL,
> [name] [varchar](200) NULL,
> [address] [varchar](200) NULL,
> [crossstreet] [varchar](200) NULL,
> [neighborhood] [varchar](200) NULL,
> [borough] [varchar](200) NULL,
> [city] [varchar](50) NULL,
> [zip] [varchar](20) NULL,
> [state] [varchar](2) NULL,
> [telephone] [varchar](200) NULL,
> [fax] [varchar](10) NULL,
> [url] [varchar](500) NULL,
> [bookingUrl] [varchar](500) NULL,
> [bus] [varchar](200) NULL,
> [subway] [varchar](300) NULL,
> [description] [varchar](max) NULL,
> [hours] [varchar](2000) NULL,
> [admission] [varchar](2000) NULL,
> [otherinfo] [varchar](1000) NULL,
> [specialDirection] [varchar](max) NULL,
> [sortOrder] [int] NOT NULL CONSTRAINT [DF__tbl_Busin__sortO__7ABC33CD]
> DEFAULT ((0)),
> [dateCreated] [datetime] NOT NULL CONSTRAINT
> [DF__tbl_Busin__dateC__7BB05806] DEFAULT (getdate()),
> [copyright] [varchar](100) NULL,
> [mappedVarchar200_1] [varchar](200) NULL,
> [mappedVarchar200_2] [varchar](200) NULL,
> [mappedVarchar200_3] [varchar](200) NULL,
> [mappedVarchar200_4] [varchar](200) NULL,
> [mappedVarchar2000_1] [varchar](2000) NULL,
> [mappedVarchar2000_2] [varchar](2000) NULL,
> [mappedVarcharMax_1] [varchar](max) NULL,
> [dateModified] [datetime] NULL,
> [email] [varchar](50) NULL,
> CONSTRAINT [PK__tbl_BusinessReco__79C80F94] PRIMARY KEY CLUSTERED
> (
> [recordID] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> END
> GO
> IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => N'idx_biz_datecreated')
> CREATE NONCLUSTERED INDEX [idx_biz_datecreated] ON
> [dbo].[tbl_BusinessRecordDetailed]
> (
> [dateCreated] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> GO
> IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => N'idx_biz_datemodifed')
> CREATE NONCLUSTERED INDEX [idx_biz_datemodifed] ON
> [dbo].[tbl_BusinessRecordDetailed]
> (
> [dateModified] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
>
>|||hmm, actually, there have been "somthing" that blocking some records in that
table that other process can't update them. as simple as an update like
"update tbl_businessrecorddetailed set name = 'whatever' where recordid =123" would just hang. just certain records are like that while most others
are fine. but after restarting the db service, it's fine. but the problem
would return after a while.
since the process is suspended, can i just kill it as a quick fix for now?
but i need to find out the cause going forward, any ideas'
thanks.
"John Bell" wrote:
> Hi
> Your lock is a shared lock so it will not be stopping other processes from
> reading this information see
> http://msdn2.microsoft.com/en-us/library/aa213039(sql.80).aspx
> and http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx
> Using DBCC INPUTBUFFER on the SID for the process should indicate the last
> statement issued by the client.
>
> John
> "tin" wrote:
> > hi,
> >
> > from Activity Monitor, i found that there's a process (suspended) holding
> > over a thousand locks on a table named tbl_BusinessRecordDetailed.
> >
> > i have 2 questions:
> >
> > #1 - is there a way to find out which row it's locking?
> >
> > #2 - what exactly is causing the locks? since the mode is S, which means
> > it's read only'
> >
> > much appreciated.
> >
> >
> > from activity monitor:
> >
> > resouce_type is all KEY
> > request_mode is all S
> > request_type is all LOCK
> > request_status is all GRANT
> > resouce_lock_partition is all 0
> > request_reference_count is all 0
> > request_exec_context_id is all 0
> > request_request_id is all 0
> > request_lifetime is all 1
> > request_owner_type is all TRANSACTION
> >
> > but resource_description are pretty much all different, some examples:
> > 1:210617
> >
> >
> >
> > 1:289260
> >
> >
> >
> > (8800ee1b2515)
> >
> >
> >
> > (b000d565e6d8)
> >
> >
> >
> > (af008d6eefc0)
> >
> >
> >
> >
> >
> >
> > below is the table definition and the indexes:
> >
> >
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND type in (N'U'))
> > BEGIN
> > CREATE TABLE [dbo].[tbl_BusinessRecordDetailed](
> > [recordID] [int] IDENTITY(1,1) NOT NULL,
> > [rowVersion] [timestamp] NOT NULL,
> > [name] [varchar](200) NULL,
> > [address] [varchar](200) NULL,
> > [crossstreet] [varchar](200) NULL,
> > [neighborhood] [varchar](200) NULL,
> > [borough] [varchar](200) NULL,
> > [city] [varchar](50) NULL,
> > [zip] [varchar](20) NULL,
> > [state] [varchar](2) NULL,
> > [telephone] [varchar](200) NULL,
> > [fax] [varchar](10) NULL,
> > [url] [varchar](500) NULL,
> > [bookingUrl] [varchar](500) NULL,
> > [bus] [varchar](200) NULL,
> > [subway] [varchar](300) NULL,
> > [description] [varchar](max) NULL,
> > [hours] [varchar](2000) NULL,
> > [admission] [varchar](2000) NULL,
> > [otherinfo] [varchar](1000) NULL,
> > [specialDirection] [varchar](max) NULL,
> > [sortOrder] [int] NOT NULL CONSTRAINT [DF__tbl_Busin__sortO__7ABC33CD]
> > DEFAULT ((0)),
> > [dateCreated] [datetime] NOT NULL CONSTRAINT
> > [DF__tbl_Busin__dateC__7BB05806] DEFAULT (getdate()),
> > [copyright] [varchar](100) NULL,
> > [mappedVarchar200_1] [varchar](200) NULL,
> > [mappedVarchar200_2] [varchar](200) NULL,
> > [mappedVarchar200_3] [varchar](200) NULL,
> > [mappedVarchar200_4] [varchar](200) NULL,
> > [mappedVarchar2000_1] [varchar](2000) NULL,
> > [mappedVarchar2000_2] [varchar](2000) NULL,
> > [mappedVarcharMax_1] [varchar](max) NULL,
> > [dateModified] [datetime] NULL,
> > [email] [varchar](50) NULL,
> > CONSTRAINT [PK__tbl_BusinessReco__79C80F94] PRIMARY KEY CLUSTERED
> > (
> > [recordID] ASC
> > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> > ) ON [PRIMARY]
> > END
> > GO
> >
> > IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => > N'idx_biz_datecreated')
> > CREATE NONCLUSTERED INDEX [idx_biz_datecreated] ON
> > [dbo].[tbl_BusinessRecordDetailed]
> > (
> > [dateCreated] ASC
> > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> > GO
> >
> > IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => > N'idx_biz_datemodifed')
> > CREATE NONCLUSTERED INDEX [idx_biz_datemodifed] ON
> > [dbo].[tbl_BusinessRecordDetailed]
> > (
> > [dateModified] ASC
> > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> >
> >
> >
> >|||Hi
sp_who2 has a blk column that may show what spid is blocking a process. You
may also want to look at http://support.microsoft.com/kb/271509 and run the
sp_blocker_pss80 procedure.
John
"tin" wrote:
> hmm, actually, there have been "somthing" that blocking some records in that
> table that other process can't update them. as simple as an update like
> "update tbl_businessrecorddetailed set name = 'whatever' where recordid => 123" would just hang. just certain records are like that while most others
> are fine. but after restarting the db service, it's fine. but the problem
> would return after a while.
> since the process is suspended, can i just kill it as a quick fix for now?
> but i need to find out the cause going forward, any ideas'
> thanks.
>
>
> "John Bell" wrote:
> > Hi
> >
> > Your lock is a shared lock so it will not be stopping other processes from
> > reading this information see
> > http://msdn2.microsoft.com/en-us/library/aa213039(sql.80).aspx
> > and http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx
> >
> > Using DBCC INPUTBUFFER on the SID for the process should indicate the last
> > statement issued by the client.
> >
> >
> > John
> >
> > "tin" wrote:
> >
> > > hi,
> > >
> > > from Activity Monitor, i found that there's a process (suspended) holding
> > > over a thousand locks on a table named tbl_BusinessRecordDetailed.
> > >
> > > i have 2 questions:
> > >
> > > #1 - is there a way to find out which row it's locking?
> > >
> > > #2 - what exactly is causing the locks? since the mode is S, which means
> > > it's read only'
> > >
> > > much appreciated.
> > >
> > >
> > > from activity monitor:
> > >
> > > resouce_type is all KEY
> > > request_mode is all S
> > > request_type is all LOCK
> > > request_status is all GRANT
> > > resouce_lock_partition is all 0
> > > request_reference_count is all 0
> > > request_exec_context_id is all 0
> > > request_request_id is all 0
> > > request_lifetime is all 1
> > > request_owner_type is all TRANSACTION
> > >
> > > but resource_description are pretty much all different, some examples:
> > > 1:210617
> > >
> > >
> > >
> > > 1:289260
> > >
> > >
> > >
> > > (8800ee1b2515)
> > >
> > >
> > >
> > > (b000d565e6d8)
> > >
> > >
> > >
> > > (af008d6eefc0)
> > >
> > >
> > >
> > >
> > >
> > >
> > > below is the table definition and the indexes:
> > >
> > >
> > > SET ANSI_NULLS ON
> > > GO
> > > SET QUOTED_IDENTIFIER ON
> > > GO
> > > IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => > > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND type in (N'U'))
> > > BEGIN
> > > CREATE TABLE [dbo].[tbl_BusinessRecordDetailed](
> > > [recordID] [int] IDENTITY(1,1) NOT NULL,
> > > [rowVersion] [timestamp] NOT NULL,
> > > [name] [varchar](200) NULL,
> > > [address] [varchar](200) NULL,
> > > [crossstreet] [varchar](200) NULL,
> > > [neighborhood] [varchar](200) NULL,
> > > [borough] [varchar](200) NULL,
> > > [city] [varchar](50) NULL,
> > > [zip] [varchar](20) NULL,
> > > [state] [varchar](2) NULL,
> > > [telephone] [varchar](200) NULL,
> > > [fax] [varchar](10) NULL,
> > > [url] [varchar](500) NULL,
> > > [bookingUrl] [varchar](500) NULL,
> > > [bus] [varchar](200) NULL,
> > > [subway] [varchar](300) NULL,
> > > [description] [varchar](max) NULL,
> > > [hours] [varchar](2000) NULL,
> > > [admission] [varchar](2000) NULL,
> > > [otherinfo] [varchar](1000) NULL,
> > > [specialDirection] [varchar](max) NULL,
> > > [sortOrder] [int] NOT NULL CONSTRAINT [DF__tbl_Busin__sortO__7ABC33CD]
> > > DEFAULT ((0)),
> > > [dateCreated] [datetime] NOT NULL CONSTRAINT
> > > [DF__tbl_Busin__dateC__7BB05806] DEFAULT (getdate()),
> > > [copyright] [varchar](100) NULL,
> > > [mappedVarchar200_1] [varchar](200) NULL,
> > > [mappedVarchar200_2] [varchar](200) NULL,
> > > [mappedVarchar200_3] [varchar](200) NULL,
> > > [mappedVarchar200_4] [varchar](200) NULL,
> > > [mappedVarchar2000_1] [varchar](2000) NULL,
> > > [mappedVarchar2000_2] [varchar](2000) NULL,
> > > [mappedVarcharMax_1] [varchar](max) NULL,
> > > [dateModified] [datetime] NULL,
> > > [email] [varchar](50) NULL,
> > > CONSTRAINT [PK__tbl_BusinessReco__79C80F94] PRIMARY KEY CLUSTERED
> > > (
> > > [recordID] ASC
> > > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> > > ) ON [PRIMARY]
> > > END
> > > GO
> > >
> > > IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => > > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => > > N'idx_biz_datecreated')
> > > CREATE NONCLUSTERED INDEX [idx_biz_datecreated] ON
> > > [dbo].[tbl_BusinessRecordDetailed]
> > > (
> > > [dateCreated] ASC
> > > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> > > GO
> > >
> > > IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => > > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => > > N'idx_biz_datemodifed')
> > > CREATE NONCLUSTERED INDEX [idx_biz_datemodifed] ON
> > > [dbo].[tbl_BusinessRecordDetailed]
> > > (
> > > [dateModified] ASC
> > > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> > >
> > >
> > >
> > >