Locking Statistic by object - SQL SERVER 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Locking Statistic by object - SQL SERVER 2005

Author

Topic

weng
Starting Member

2 Posts

Posted - 2012-12-04 : 02:20:40

[url]http://postimage.org/image/9uu1vh9oz/[/url]

Based on the blocking statistic image attached as above. What is the possible reason for these blocking :

spid -51 -

begin tran
update Master_table_data set sys_value=sys_value +1
where GETDATE()> start_dt and sys_key='LAST_USED_ID';
commit
select sys_value
from Master_table_data with(nolock)
WHERE GETDATE()>eff_start_dt and sys_key='LAST_USED_LOG_ID'
END

spid -53 -

begin
set @ErrorMsg='Msg_'+@ErrorMsg;
set @SYS_VALUE = NULL

select
@SYS_VALUE = sys_value from Master_table_data where sys_key=@ErrorMsg

if @SYS_VALUE is not null
begin
insert into TXN_AUDIT_LOG
select
@LogID,
GetDate(),
@IP,
@Flag,
@SYS_VALUE
end

Question :

1, It seem like the select is issuing a Exclusive lock . Is these possible ?

weng
Starting Member

2 Posts

Posted - 2012-12-04 : 02:43:21

the table only have 17 rows .

the same statement will run in every minutes when there are new data enter from the front end system . The blocking occurs intermittent and update is always block by the exclusive lock. I don't understand how the exclusive lock trigger by a select statement.

Additional information on the table:

CREATE TABLE [dbo].[Master_table_data](
[SYS_KEY] [nvarchar](30) NOT NULL,
[SYS_VALUE] [nvarchar](500) NOT NULL,
[EFF_START_DT] [datetime] NOT NULL,
[EFF_END_DT] [datetime] NULL,
[CREATED_DT] [datetime] NULL,
[CREATED_BY] [nvarchar](30) NULL,
[MODIFIED_DT] [datetime] NULL,
[MODIFIED_BY] [nvarchar](30) NULL,
CONSTRAINT [PK_Master_table_data] PRIMARY KEY CLUSTERED
(
[SYS_KEY] ASC,
[SYS_VALUE] ASC,
[EFF_START_DT] ASC
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources