Read-Only filegroups and Locking

Read-Only filegroups and Locking

  • Comments 1

I recently saw a newsgroup post that sort of implied that accessing data residing on read-only filegroups can save you on locking. Well, it could, but it does not. If you really want to save on locks, the whole database needs to be set to read-only.

 

Back to read-only filegroups. They provide you the following three benefits:

 

  1. Can be compressed (using NTFS compression)
  2. During recovery you don’t need to apply logs to recover a read-only file group
  3. Protection of data from accidental modifications

 

But as far as locking is concerned, SQL Server still gets the locks when you access data from read-only filegroup. Here is one simple example:

 

use general

go

 

 

alter database general add filegroup foo

go

 

alter database general add file (

name = file1,

filename = 'c:\temp\file1')

to filegroup foo

 

-- create a table and associate it to a filegroup

create table t_fg (c1 int, c2 int) on foo

insert into t_fg values (1,1)

 

-- mark the filegroup read-only

alter database general modify filegroup foo read_only

 

-- run a transaction with repeatable read isolation

set transaction isolation level repeatable read

begin tran

select * from t_fg where c1 = 1

 

-- no check the locks

sp_lock @@spid

 

-- here is the output

spid   dbid   ObjId       IndId  Type Resource                         Mode     Status

------ ------ ----------- ------ ---- -------------------------------- -------- ------

53     10     1381579960  0      RID  3:8:0                         S        GRANT

53     10     0                 0      DB                                     S        GRANT

53     10     1381579960  0      PAG  3:8                           IS       GRANT

53     10     1381579960  0      TAB                                   IS       GRANT

53     1      1115151018   0      TAB                                   IS       GRANT

 

Leave a Comment
  • Please add 5 and 3 and type the answer here:
  • Post
  • Hi,

    when using read-only data no lock should be nedded, even with repeatable read transaction semantics.

    Why not placing the NOLOCK table hint?

    This should work, because the data is read-only, and therefore all subsequent eads will return the same value, even if there are no locks at all. Am I missing something?

Page 1 of 1 (1 items)