6 Minor technical improvements; incorporated comment
source | link

This is more of an elaborate comment than an answer, but youYou should know that this:

no other transaction holds any of the locks the current one holds

no other transaction holds any of the locks the current one holds

For overhead reasons, sql server uses an internal function to identifySQL Server hashes the rows to lock. This function creates aThe 6 byte-byte hash value for each row and can be exposed by using the undocumented %%lockres%% function.  

Even moreMore information on the probability of %%lockres%% collisions in an article by Remus Rusanu can be found in herean article by Remus Rusanu.

In other words, when SQL Server identifies a row to lock, it doesn't lock the physical row, rather it enters the hash value corresponding to that row in an internal table of locks. Because other rows could have that same hash value, those other, unrelated rows, are effectively locked, because they share the same lock hash and would therefore appear locked by another transaction.

An example that illustrates these hash collisions:

When I ran this example, I had one duplicate lockreslockres value: (45642dc72eae)

On a second check, I inserted the values in an actual table, found another lockreslockres value, (a006f9bf8d84) and tried to update this table in two query windows with the two hash values returned:

This is more of an elaborate comment than an answer, but you should know that this:

no other transaction holds any of the locks the current one holds

For overhead reasons, sql server uses an internal function to identify the rows to lock. This function creates a 6 byte hash value for each row and can be exposed by using the undocumented %%lockres%% function.  

Even more information on the probability of %%lockres%% collisions in an article by Remus Rusanu can be found here.

An example that illustrates these hash collisions:

When I ran this example, I had one duplicate lockres value: (45642dc72eae)

On a second check I inserted the values in an actual table, found another lockres value, (a006f9bf8d84) and tried to update this table in two query windows with the two hash values returned:

You should know that this:

no other transaction holds any of the locks the current one holds

For overhead reasons, SQL Server hashes the rows to lock. The 6-byte hash value for each row can be exposed using the undocumented %%lockres%% function.

More information on the probability of %%lockres%% collisions can be found in an article by Remus Rusanu.

In other words, when SQL Server identifies a row to lock, it doesn't lock the physical row, rather it enters the hash value corresponding to that row in an internal table of locks. Because other rows could have that same hash value, those other, unrelated rows, are effectively locked, because they share the same lock hash and would therefore appear locked by another transaction.

An example that illustrates these hash collisions:

When I ran this example, I had one duplicate lockres value: (45642dc72eae)

On a second check, I inserted the values in an actual table, found another lockres value, (a006f9bf8d84) and tried to update this table in two query windows with the two hash values returned:

5 added 321 characters in body
source | link

An important part of this excellent article:

So the SQL %%lockres%% hash will produce two records with the same hash, with a 50% probability, out of the table, any table, of only 16,777,215 records

An example that illustrates thisthese hash collisions:

SET NOCOUNT ON;

CREATE TABLE #HashCollision(ID UNIQUEIDENTIFIER  PRIMARY KEY NOT NULL);


DECLARE @i int = 1
WHILE @i <= 33
BEGIN
INSERT INTO #HashCollision WITH(TABLOCK)
(ID)
SELECT TOP(1000000)  -- 33554430 * 21M
NEWID()
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
SET @i+=1;
END

--33m rows 

SELECT %%lockres%% as lockress
INTO #temp
FROM #HashCollision#HashCollision;


select COUNT(DISTINCT lockress)
from #temp#temp;
--onwhen myI exampletested, =the count was 32999999. 
--Looks like it fluctuates between 32999999 and 32999998 on additional tests

SELECT lockress FROM 
#temp
group by lockress
having COUNT(*)  > 11;

An example that illustrates this:

SET NOCOUNT ON;

CREATE TABLE #HashCollision(ID UNIQUEIDENTIFIER  PRIMARY KEY NOT NULL);


DECLARE @i int = 1
WHILE @i <= 33
BEGIN
INSERT INTO #HashCollision WITH(TABLOCK)
(ID)
SELECT TOP(1000000)  -- 33554430 * 2
NEWID()
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
SET @i+=1;
END

--33m rows
SELECT %%lockres%% as lockress
INTO #temp
FROM #HashCollision


select COUNT(DISTINCT lockress)
from #temp
--on my example = 32999999

SELECT lockress FROM 
#temp
group by lockress
having COUNT(*)  > 1

An important part of this excellent article:

So the SQL %%lockres%% hash will produce two records with the same hash, with a 50% probability, out of the table, any table, of only 16,777,215 records

An example that illustrates these hash collisions:

SET NOCOUNT ON;

CREATE TABLE #HashCollision(ID UNIQUEIDENTIFIER  PRIMARY KEY NOT NULL);


DECLARE @i int = 1
WHILE @i <= 33
BEGIN
INSERT INTO #HashCollision WITH(TABLOCK)
(ID)
SELECT TOP(1000000)  1M
NEWID()
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
SET @i+=1;
END

--33m rows 

SELECT %%lockres%% as lockress
INTO #temp
FROM #HashCollision;


select COUNT(DISTINCT lockress)
from #temp;
--when I tested, the count was 32999999. 
--Looks like it fluctuates between 32999999 and 32999998 on additional tests

SELECT lockress FROM 
#temp
group by lockress
having COUNT(*)  > 1;
4 deleted 3 characters in body
source | link

Since we've established that no other transaction holds any of the locks the current one holds, it logically follows that no other transaction would be attempting to update or lock any of the records with those same object ids in the personal inventory table.

This is more of an elaborate comment than an answer, but you should know that this:

no other transaction holds any of the locks the current one holds

is technically not established.

In some cases, more than one row lock can be taken for a single update of one value.

Even if every key/row value is unique.

For overhead reasons, sql server uses an internal function to identify the rows to lock. This function creates a 6 byte hash value for each row and can be exposed by using the undocumented %%lockres%% function.

Depending upon the # of rows, structure of the primary key, the data distribution and the complexity of the hashing algorithm, one can get hash collisions. For example, one calculated lockhash value can lock more than one row within a B-Tree

Source

Hash collisions are possible. When? it depends™.

Even more information on the probability of %%lockres%% collisions in an article by Remus Rusanu can be found here.

An example that illustrates this:

SET NOCOUNT ON;

CREATE TABLE #HashCollision(ID UNIQUEIDENTIFIER  PRIMARY KEY NOT NULL);


DECLARE @i int = 1
WHILE @i <= 33
BEGIN
INSERT INTO #HashCollision WITH(TABLOCK)
(ID)
SELECT TOP(1000000)  -- 33554430 * 2
NEWID()
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
SET @i+=1;
END

--33m rows
SELECT %%lockres%% as lockress
INTO #temp
FROM #HashCollision


select COUNT(DISTINCT lockress)
from #temp
--on my example = 32999999

SELECT lockress FROM 
#temp
group by lockress
having COUNT(*)  > 1

When I ran this example, I had one duplicate lockres functionvalue: (45642dc72eae)

The uniqueidentifier values that had the same %%lockres%% hash value are

51300BD6-EE42-435F-92D3-A23AB965C6D6 & A9FEFC2E-3BA9-4C56-8F32-A69811C95092

On a second check I inserted the values in an actual table, found another lockres value, (a006f9bf8d84) and tried to update this table in two query windows with the two hash values returned:

Transaction 1

BEGIN TRAN
UPDATE dbo.HashCollision
SET ID = NEWID() 
WHERE ID = 'D616D1C1-D609-448F-A1F4-5F959AB344F3';

Transaction 2 (blocked)

BEGIN TRAN
UPDATE dbo.HashCollision
SET ID = NEWID() 
WHERE ID = '0B6846D9-3E47-4B75-9F7B-89CA0B090524';

until transaction #1 was rolled back or committed.

Since we've established that no other transaction holds any of the locks the current one holds, it logically follows that no other transaction would be attempting to update or lock any of the records with those same object ids in the personal inventory table.

This is more of an elaborate comment than an answer, but you should know that this:

no other transaction holds any of the locks the current one holds

is technically not established.

In some cases, more than one row lock can be taken for a single update of one value.

Even if every key/row value is unique.

For overhead reasons, sql server uses an internal function to identify the rows to lock. This function creates a 6 byte hash value for each row and can be exposed by using the undocumented %%lockres%% function.

Depending upon the # of rows, structure of the primary key, the data distribution and the complexity of the hashing algorithm, one can get hash collisions. For example, one calculated lockhash value can lock more than one row within a B-Tree

Source

Hash collisions are possible. When? it depends™.

Even more information on the probability of %%lockres%% collisions in an article by Remus Rusanu can be found here.

An example that illustrates this:

SET NOCOUNT ON;

CREATE TABLE #HashCollision(ID UNIQUEIDENTIFIER  PRIMARY KEY NOT NULL);


DECLARE @i int = 1
WHILE @i <= 33
BEGIN
INSERT INTO #HashCollision WITH(TABLOCK)
(ID)
SELECT TOP(1000000)  -- 33554430 * 2
NEWID()
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
SET @i+=1;
END

--33m rows
SELECT %%lockres%% as lockress
INTO #temp
FROM #HashCollision


select COUNT(DISTINCT lockress)
from #temp
--on my example = 32999999

SELECT lockress FROM 
#temp
group by lockress
having COUNT(*)  > 1

When I ran this example, I had one duplicate lockres function: (45642dc72eae)

The uniqueidentifier values that had the same %%lockres%% hash value are

51300BD6-EE42-435F-92D3-A23AB965C6D6 & A9FEFC2E-3BA9-4C56-8F32-A69811C95092

On a second check I inserted the values in an actual table, found another lockres value, (a006f9bf8d84) and tried to update this table in two query windows with the two hash values returned:

Transaction 1

BEGIN TRAN
UPDATE dbo.HashCollision
SET ID = NEWID() 
WHERE ID = 'D616D1C1-D609-448F-A1F4-5F959AB344F3';

Transaction 2 (blocked)

BEGIN TRAN
UPDATE dbo.HashCollision
SET ID = NEWID() 
WHERE ID = '0B6846D9-3E47-4B75-9F7B-89CA0B090524';

until transaction #1 was rolled back or committed.

Since we've established that no other transaction holds any of the locks the current one holds, it logically follows that no other transaction would be attempting to update or lock any of the records with those same object ids in the personal inventory table.

This is more of an elaborate comment than an answer, but you should know that this:

no other transaction holds any of the locks the current one holds

is technically not established.

In some cases, more than one row lock can be taken for a single update of one value.

Even if every key/row value is unique.

For overhead reasons, sql server uses an internal function to identify the rows to lock. This function creates a 6 byte hash value for each row and can be exposed by using the undocumented %%lockres%% function.

Depending upon the # of rows, structure of the primary key, the data distribution and the complexity of the hashing algorithm, one can get hash collisions. For example, one calculated lockhash value can lock more than one row within a B-Tree

Source

Hash collisions are possible. When? it depends™.

Even more information on the probability of %%lockres%% collisions in an article by Remus Rusanu can be found here.

An example that illustrates this:

SET NOCOUNT ON;

CREATE TABLE #HashCollision(ID UNIQUEIDENTIFIER  PRIMARY KEY NOT NULL);


DECLARE @i int = 1
WHILE @i <= 33
BEGIN
INSERT INTO #HashCollision WITH(TABLOCK)
(ID)
SELECT TOP(1000000)  -- 33554430 * 2
NEWID()
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
SET @i+=1;
END

--33m rows
SELECT %%lockres%% as lockress
INTO #temp
FROM #HashCollision


select COUNT(DISTINCT lockress)
from #temp
--on my example = 32999999

SELECT lockress FROM 
#temp
group by lockress
having COUNT(*)  > 1

When I ran this example, I had one duplicate lockres value: (45642dc72eae)

The uniqueidentifier values that had the same %%lockres%% hash value are

51300BD6-EE42-435F-92D3-A23AB965C6D6 & A9FEFC2E-3BA9-4C56-8F32-A69811C95092

On a second check I inserted the values in an actual table, found another lockres value, (a006f9bf8d84) and tried to update this table in two query windows with the two hash values returned:

Transaction 1

BEGIN TRAN
UPDATE dbo.HashCollision
SET ID = NEWID() 
WHERE ID = 'D616D1C1-D609-448F-A1F4-5F959AB344F3';

Transaction 2 (blocked)

BEGIN TRAN
UPDATE dbo.HashCollision
SET ID = NEWID() 
WHERE ID = '0B6846D9-3E47-4B75-9F7B-89CA0B090524';

until transaction #1 was rolled back or committed.

3 added 33 characters in body
source | link
2 added 1 character in body
source | link
1
source | link