SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Locking Statistic by object - SQL SERVER 2005
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

weng
Starting Member

2 Posts

Posted - 12/04/2012 :  02:20:40  Show Profile  Reply with Quote
http://postimage.org/image/9uu1vh9oz/

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 ?

Edited by - weng on 12/04/2012 02:44:08

weng
Starting Member

2 Posts

Posted - 12/04/2012 :  02:43:21  Show Profile  Reply with Quote
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
)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000