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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.02 seconds. Powered By: Snitz Forums 2000