Key range lock on every row in the table when selecting a single row
-
From my research, when operating within a transaction with isolation level of serializable, a select should lock only those rows that are within the range being selected. I'm finding however that for a specific table all rows are being locked even though I am only selecting 1 row by primary key. The code to reproduce this is as follows:
===================================================================================
select p.loginame, o.name, li.req_mode, count(*)
from sysprocesses p, sysdatabases d, syslockinfo li, iomorderstatus.dbo.sysobjects o
where li.rsc_dbid = d.dbid
and li.req_spid = p.spid
and o.id = li.rsc_objid
group by p.loginame, o.name, li.req_mode
order by 3 desc
begin transaction
set transaction isolation level serializable
select * From iomorderstatus.dbo.iom_stat_task
where source = 'ICCI'
and order_id = 33096101
and version = 1
and site_id = 6
and task_name = 'CARE Trigger';
select p.loginame, o.name, li.req_mode, count(*)
from sysprocesses p, sysdatabases d, syslockinfo li, iomorderstatus.dbo.sysobjects o
where li.rsc_dbid = d.dbid
and li.req_spid = p.spid
and o.id = li.rsc_objid
group by p.loginame, o.name, li.req_mode
order by 3 desc
select count(*) total_locks From iomorderstatus.dbo.iom_stat_task;
rollback;
===================================================================
Results:
===================================================================
loginame name req_mode count
-----no rows------
source order_id version site_id task_name bb_id task_status task_start_dt task_complete_dt
ICCI 33096101 1 6 CARE Trigger 300.00 Complete 2005-02-08 15:41:52.390 2005-02-08 15:42:00.000
loginame name req_mode count
CSCOE\thinerc IOM_STAT_TASK 13 175607
CSCOE\thinerc IOM_STAT_TASK 6 3296
total_rows
175608
==================================================================
This is ONLY happening in our production environment. I have moved the data in this table to a test environment, as well as backing up the database and restoring to a test environment and am still unable to reproduce anywhere other than production.
Can anyone offer any insight into this behavior? What else should I be looking at?
Table definition is:
==================================================================
CREATE TABLE dbo.IOM_STAT_TASK
(
SOURCE char(10) NOT NULL,
ORDER_ID char(10) NOT NULL,
VERSION int NOT NULL,
SITE_ID varchar(50) NOT NULL,
TASK_NAME varchar(30) NOT NULL,
BB_ID decimal(18, 2) NULL,
TASK_STATUS varchar(30) NOT NULL,
TASK_START_DT datetime NULL,
TASK_COMPLETE_DT datetime NULL,
TASK_INTERVAL float(53) NULL,
STATUS_EFFECTIVE_DT datetime NOT NULL,
WORK_GROUP_NAME varchar(100) NOT NULL,
ERROR_NUMBER varchar(20) NULL,
ERROR_DESC varchar(1000) NULL,
SYSTEM char(10) NULL,
COMMENT varchar(1000) NULL,
USER_NAME char(10) NULL,
CURRENT_TASK char(10) NULL,
MILESTON_ID int NOT NULL
 
ON [PRIMARY]GO
ALTER TABLE dbo.IOM_STAT_TASK ADD CONSTRAINT
IOM_STAT_TASK_PK PRIMARY KEY CLUSTERED
(
SOURCE,
ORDER_ID,
VERSION,
TASK_NAME,
SITE_ID
 
ON [PRIMARY]GO
** 3 foreign keys reference this table.
=========================================================
-
This was removed by the editor as SPAM
-
What happens if you change select * to specific column names?
-
Look at the execution plan to make sure that the query is using the index.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Good call, implicit conversion of the order_id to char supresses the index, and thus the key range locks on all rows. Changing the stored procs fixed the issue.
From:
and order_id = 33096101
to:
and order_id = '33096101'
-
Good, glad I could help; but that doesn't explain why it was behaving differently on other servers.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply