| Author |
 Topic  |
|
|
weng
Starting Member
2 Posts |
 Posted - 12/04/2012 : 02:20:40
|
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
|
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
)
|
 |
|
| |
Topic |
|
|
|
Locking Statistic by object - SQL SERVER 2005
