StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy Row Locks Exist on An Completely Empty Partition in SQL Server?
primarykey
Id
6756125
data
AcceptedAnswerId
0
AnswerCount
1
ClosedDate
CommentCount
5
CommunityOwnedDate
CreationDate
2011-07-20T01:47:13.347
FavoriteCount
0
LastActivityDate
2011-07-20T02:33:38.137
LastEditDate
2011-07-20T02:33:38.137
LastEditorUserId
847493
OwnerUserId
819123
ParentId
0
PostTypeId
1
Score
0
ViewCount
150
LastEditorDisplayName
text
Body
I made a partitioned table with range left func. I then insert some rows into just one partition. Result: <pre><code>select partition_id, partition_number, rows from sys.partitions where object_id = object_id('ptest') </code></pre> result: <pre><code>partition_id partition_number rows 72057594464436224 1 8000 72057594464501760 2 0 72057594464567296 3 0 72057594464632832 4 0 </code></pre> then I update some rows of this table and query sys.dm_tran_locks, then I find some RID Locks on partition4 like this: <pre><code>select * from sys.dm_tran_locks where resource_type = 'RID' </code></pre> Result like this: <pre><code>Resource_type Resource_description RID 3:13:192 RID 1:153496:257 </code></pre> When I use dbcc page to verify what page 3:13 contains, I get nothing: <pre><code>dbcc traceon(3604) dbcc page(db, 3, 13, 3) </code></pre> Result like this: <pre><code>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 </code></pre> 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?
Tags
<sql-server><database-partitioning><dbcc><locks>
Title
Why Row Locks Exist on An Completely Empty Partition in SQL Server?
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USLost_Painting
UserOwnerUserId
1. USchenwq
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POWhy Row Locks Exist on An Completely Empty Partition in SQL Server?
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTApproveEditSuggestion
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.