Why Row Locks Exist on An Completely Empty Partition in SQL Server?
I made a partitioned table with range left func. I then insert some rows into just one partition. Result:
select partition_id, partition_number, rows from sys.partitions where object_id = object_id('ptest')
result:
partition_id partition_number row开发者_StackOverflow中文版s
72057594464436224 1 8000
72057594464501760 2 0
72057594464567296 3 0
72057594464632832 4 0
then I update some rows of this table and query sys.dm_tran_locks, then I find some RID Locks on partition4 like this:
select * from sys.dm_tran_locks where resource_type = 'RID'
Result like this:
Resource_type Resource_description
RID 3:13:192
RID 1:153496:257
When I use dbcc page to verify what page 3:13 contains, I get nothing:
dbcc traceon(3604)
dbcc page(db, 3, 13, 3)
Result like this:
PAGE: (3:13)
BUFFER:
BUF @0x03E98BCC
bpage = 0x5A622000 bhash = 0x00000000 bpageno = (3:13)
bdbid = 11 breferences = 0 bUse1 = 38298
bstat = 0x1c0010b blog = 0x79797979 bnext = 0x00000000
PAGE HEADER:
Page @0x5A622000
m_pageId = (3:13) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0xa008
m_objId (AllocUnitId.idObj) = 6973 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594494910464
Metadata: PartitionId = 72057594464501760 Metadata: IndexId = 0
Metadata: ObjectId = 1967398128 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 12 m_slotCnt = 1 m_freeCnt = 8094
m_freeData = 5867 m_reservedCnt = 0 m_lsn = (25294:376:199)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = -252731581
Allocation Status
GAM (3:2) = ALLOCATED SGAM (3:3) = ALLOCATED
PFS (3:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (3:6) = CHANGED
ML (3:7) = NOT MIN_LOGGED
So, dbcc page confirms that there is nothing in page 3:13. There comes my question, why are RID locks exists on File 3:13 while nothing exists there?
what's your " I update some rows of this table" T-SQL? Maybe your update T-SQL made it be locked . There isn't "nothing" here , there are still pages in the empty partition but no data
精彩评论