November 5, 2020 at 6:50 pm
We're curious about locks that might revolve around ghost records that have been deleted. Does anyone have an explanation for this scenario?
Given the following table:
CREATE TABLE [dbo].[TEST_TABLE](
[column_A] [nvarchar](30) NOT NULL,
[column_B] [tinyint] NOT NULL,
[column_C] [bigint] NOT NULL,
[column_D] [int] NOT NULL,
CONSTRAINT [i01TESTTABLEKEY] PRIMARY KEY CLUSTERED
(
[column_A] ASC,
[column_B] ASC,
[column_C] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TEST_TABLE] ADD CONSTRAINT [DF_TEST_TABLE1_column_A] DEFAULT (replace(CONVERT([varchar](30),context_info()),char((0)),'')) FOR [column_A]
GO
ALTER TABLE [dbo].[TEST_TABLE] ADD CONSTRAINT [DF_TEST_TABLE1_column_B] DEFAULT ((0)) FOR [column_B]
GO
ALTER TABLE [dbo].[TEST_TABLE] ADD CONSTRAINT [DF_TEST_TABLE1_column_C] DEFAULT ((0)) FOR [column_C]
GO
ALTER TABLE [dbo].[TEST_TABLE] ADD CONSTRAINT [DF_TEST_TABLE1_column_D] DEFAULT ((0)) FOR [column_D]
GO
--Step 1 - Start fresh
TRUNCATE TABLE [dbo].[TEST_TABLE]
SELECT * FROM [dbo].[TEST_TABLE]
--step 2 Insert a record with a particular key, and then Delete that same record (using DELETE FROM or TRUNCATE)
Insert into TEST_TABLE (column_A, column_B, column_C, column_D) values ('AAAA', 0, 1234, 0)
delete from TEST_TABLE
--step 3 try to update that same non-existent record again and check locks (there will be a key lock that should not be there)
begin transaction
update TEST_TABLE set column_D = column_D + 1 Where column_C = 1234 and column_A = 'AAAA' and column_B = 0
go
sp_lock @@spid
This will show a KEY lock on the deleted record that we think should not be there. This is causing other transactions that want to insert records based on that key to hang or fail. Microsoft talks about these being ghost records that some process will clean up later, but how can we be expected to design an application if SQL Server tells us there are 0 records with that key, but then won't allow us to insert that record due to an existing Key lock on a record that really doesn't exist. That key lock only looks like it happens after you try to update it when it used to exist, but has since been deleted. Anyone know what is going on with this? Would this be considered a Microsoft bug? We simply can't see how this can be expected behavior.
November 5, 2020 at 6:59 pm
You don't have a commit or rollback on the transaction.
November 5, 2020 at 7:08 pm
That was done on purpose to show that right after the update to the record that doesn't exist, the open transaction has a key lock on a record that doesn't exist. This key lock in the open transaction will not allow other transactions to insert that key. So, if the transaction had a lot of other things in it, it will be blocking any inserts with that key for other transactions for the entire duration of this particular transaction. We don't think that's valid.
November 5, 2020 at 7:15 pm
I would see that more as an object lesson in not doing long running transactions on tables that might be highly active.
November 5, 2020 at 7:18 pm
So you're ok with this breaking every rule of ACID in which SQL Server says a record doesn't exist but won't let another transaction insert it?
November 5, 2020 at 7:35 pm
The first part of ACID is atomic, transactions either commit or rollback as one unit, they don't do partial units of work.
I don't see this as any different than if you changed that update to an insert, which 100% should lock that key for the duration of the transaction.
November 5, 2020 at 8:16 pm
You should see it different, because that row in the update DOESN'T EXIST. How can there be a lock on a non-existent row? On an insert, the row exists....big difference.
November 5, 2020 at 8:30 pm
Right but the transaction doesn't know the record doesn't exist until it tries to find it, which requires locking it.
November 5, 2020 at 9:02 pm
Again, how exactly do you lock a record that doesn't exist? And even if it was possible, why would SQL Server throw a key lock out there for record it determines it never found? Both of those sound very illogical. Microsoft is telling us a different story, and that it comes down to ghosted records that aren't cleaned up right away. I'm wondering if after a delete, the ghosted record is still there and that's what the subsequent update transaction is actually taking the lock on. Which again, doesn't seem like it should be allowed with respect to other currently running transactions wanting to insert that key.
November 6, 2020 at 2:40 pm
Hmm... okay I see what you're saying. It seems to be something to do with extremely small tables. And both truncating the table or simply inserting a chunk of data into the table makes it go away, I just did this and the behavior stopped happening.
Insert into TEST_TABLE (column_A, column_B, column_C, column_D) values (NEWID(), 0, 1234, 0)
GO 1000
So yeah it does seem like a bug since those records should be clearing on their own based on the MS docs. But i'd still advise against having transactions running unnecessarily long 🙂
November 6, 2020 at 4:21 pm
SQL probably takes the key lock before / as it's reading the row. SQL's not taking a lock on a "row that doesn't exist", it's taking a lock on the *key values you specified*.
I believe UPDATE locks exist for the duration of the transaction they're in (whether an implied transaction or an explicit one). This isn't a typical lock you run into issues with, but I would think the same rules apply to it.
It's likely the only solution is to keep the UPDATE (and DELETE and INSERT) transactions short for this table.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 6, 2020 at 4:47 pm
@ScottPetcher
This is a READ COMMITTED transaction. The lock should only be held for the duration of the statement, not the entire transaction. So that doesn't seem to answer why the key lock is being held and no other transaction can insert using that key.
November 6, 2020 at 5:30 pm
@ScottPetcher
This is a READ COMMITTED transaction. The lock should only be held for the duration of the statement, not the entire transaction. So that doesn't seem to answer why the key lock is being held and no other transaction can insert using that key.
I don't think so. Once you start an explicit transaction, it's my understanding that any UPDATE locks (and I think DELETE and INSERT as well) within that trans are held until the end of the trans. I don't see how else SQL could guarantee integrity of the data.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 6, 2020 at 5:49 pm
@ScottPletcher I stand corrected, and you're right about the Read Committed....However, I still can't understand how the update is locking on key values specified that don't exist. That just doesn't seem correct to me and seems like a bug in my mind. If a record doesn't exist, it doesn't exist. It should not keep other transactions from inserting that key.
November 8, 2020 at 12:12 am
If there isn't a ghost record, the update does not take a lock on the *key values you specified*. Below is almost the same test, just without the ghost record created. The locks look normal and the INSERT is successful. I can't see a reasonable explanation for the presence of the ghost record resulting in behavior where the UPDATE ends up blocking the INSERT.
--step 0 - make sure the table doesn't already have the row and that there are no ghost records
TRUNCATE TABLE TEST_TABLE
--step 1 - attempt an update where there are no row
BEGIN TRANSACTION
UPDATE TEST_TABLE SET column_D = column_D + 1 WHERE column_C = 1234 AND column_A = 'AAAA' AND column_B = 0
GO
sp_lock @@spid
-- Step 2 - run on a separate connection
INSERT INTO TEST_TABLE (column_A, column_B, column_C, column_D) VALUES ('AAAA', 0, 1234, 0)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply