1

I am updating a table Hr.employees for a single record in a transaction in one query window

Begin Tran A

update hr.Employees
set lastname = 'davis'
where empid=1

In other query window i am running two select statements without a transaction

select count(*) from hr.Employees with(nolock)
select count(*) from hr.Employees with(readpast)

Both of them returning 9, whereas readpast should return 8 as it removes dirty read when returning data.

CREATE TABLE [HR].[Employees](
    [empid] [int] IDENTITY(1,1) NOT NULL,
    [lastname] [nvarchar](20) NOT NULL,
    [firstname] [nvarchar](10) NOT NULL,
    [title] [nvarchar](30) NOT NULL,
    [titleofcourtesy] [nvarchar](25) NOT NULL,
    [birthdate] [datetime] NOT NULL,
    [hiredate] [datetime] NOT NULL,
    [address] [nvarchar](60) NOT NULL,
    [city] [nvarchar](15) NOT NULL,
    [region] [nvarchar](15) NULL,
    [postalcode] [nvarchar](10) NULL,
    [country] [nvarchar](15) NOT NULL,
    [phone] [nvarchar](24) NOT NULL,
    [mgrid] [int] NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
    [empid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [HR].[Employees]  WITH CHECK ADD  CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([mgrid])
REFERENCES [HR].[Employees] ([empid])
GO

ALTER TABLE [HR].[Employees] CHECK CONSTRAINT [FK_Employees_Employees]
GO

ALTER TABLE [HR].[Employees]  WITH CHECK ADD  CONSTRAINT [CHK_birthdate] CHECK  (([birthdate]<=getdate()))
GO

ALTER TABLE [HR].[Employees] CHECK CONSTRAINT [CHK_birthdate]
GO

CREATE NONCLUSTERED INDEX [idx_nc_lastname] ON [HR].[Employees]
(
    [lastname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_nc_postalcode] ON [HR].[Employees]
(
    [postalcode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
omkar
  • 79
  • 1
  • 9
  • 1
    When I try this on a two row table with no indexes, and execute `A` under the `SERIALIZABLE` level, `SELECT COUNT(*) ... WITH (READPAST)` blocks entirely, as it can't get past the full table lock this causes. If I create an index on the column instead, the `COUNT(*)` returns only the number of records that aren't locked, as the index scan skips the others. The explanation for what you're seeing is logically that the `COUNT(*)` isn't running into any lock held by the `UPDATE` statement. As to why that is so, you'll have to look deeper -- look at the execution plan. – Jeroen Mostert Aug 31 '17 at 10:52
  • not able to repro this issue .below is the sample data used..`create table t1 ( id int primary key ) insert into t1 values (1), (2), (3) select * from t1` – TheGameiswar Aug 31 '17 at 10:53
  • in another session..`select count(*) from t1 with(readpast) select * from t1 with(readpast)` both returns 2 – TheGameiswar Aug 31 '17 at 10:54
  • one more update session `begin tran update t1 set id=4 where id=3` – TheGameiswar Aug 31 '17 at 10:55
  • as @JeroenMostert suggsted,try pasting the table data you have along with schema of table,so that some one can test – TheGameiswar Aug 31 '17 at 10:55
  • Readpast can only be used with REad committed and repeatable read. – omkar Aug 31 '17 at 11:06
  • There you go. The `COUNT(*)` can be satisfied (and *will* be satisfied) from an index which is smaller than the clustered index, like the one on `postalcode`, which is conveniently not holding any row locks while the table is updated. If you updated both `lastname` and `postalcode`, you should see the count differ, as now locks are required on all indexes. – Jeroen Mostert Aug 31 '17 at 11:06
  • @JeroenMostert I added postalcode in my update statement, still when i doing a count both are returning same numbers. – omkar Aug 31 '17 at 11:12
  • Unable to reproduce. Filling the table with 9 records and executing `begin transaction; update hr.Employees set lastname = 'davis', postalcode='' where empid=1` one will result in `SELECT COUNT(*) ... WITH READPAST` returning 8. Leaving out `postalcode` gives 9. – Jeroen Mostert Aug 31 '17 at 11:19
  • The database doesn't have `READ_COMMITTED_SNAPSHOT` turned on by any chance, does it? That's about the only explanation I can think of. – Jeroen Mostert Aug 31 '17 at 11:25
  • No Is Read Committed Snapshot On is set to False – omkar Aug 31 '17 at 12:00

0 Answers0