Hi Greg,
The index added covers the fields in the locking strategy.
*CREATE NONCLUSTERED INDEX [IX_Shipping_StatusID_IsLocked_LockedBy]*
*ON [dbo].[Shipping] ([StatusID],[IsLocked],[LockedBy])*
*INCLUDE ([OrderID])*
Yes, locking strategies like this aren't always the best strategies, but
it's not something I haven't seen before in distributed web based apps. If
someone wants to do some work on an order, they lock the whole order (for a
period), do the work, then release the order, preventing someone from doing
a whole lot of work just to have the whole lot clobbered. And timestamp
columns certainly aren't used in this legacy system :)
They haven't considered using Read Committed Snapshot Isolation. I have put
forward that suggestion now and a ticket has been raised to look at it.
I don't believe the DISTINCT is needed. They are using LLBLGEN to generate
their SQL and I'm not sure if they set an option. Don't really won't to go
near that code. We are stripping it out and replacing it with Dapper calls.
LLBLGEN is worse than LINQ.
Thanks for the advice Greg.
On Wed, Oct 12, 2022 at 10:14 AM Dr Greg Low <g...@sqldownunder.com> wrote:
> Hi Tony,
>
>
>
> That summary will be pretty close to the mark.
>
>
>
> What’s the index that was added though?
>
>
>
> Adding an index can lead to deadlocks, as it’s another object that needs
> to be updated when the data is updated.
>
>
>
> Few questions though:
>
>
>
> - What’s the index that was added look like?
> - Have you considered having RCSI enabled for the database? (Chances
> are it might even fix the issue in this case)
> - What’s the deal with the “IsLocked” stuff anyway. Smells like a
> roll-your-own locking system. (Which are almost always not a great idea)
> - Is that DISTINCT really needed? The most important part of resolving
> deadlocks is to start by making sure all the queries involved run as fast
> as possible, to avoid them holding unnecessary locks in the first place, or
> holding them too long.
>
>
>
> We often get called in with customers who have “blocking” problems.
> Invariably they have a bunch of deadlocks. 99% of those disappear with
> appropriate indexing to make the queries run fast, and by using RCSI where
> that makes sense.
>
>
>
> Regards,
>
>
>
> Greg
>
>
>
> Dr Greg Low
>
>
>
> 1300SQLSQL (1300 775 775) office | +61 419201410 mobile
>
> SQL Down Under | Web: https://sqldownunder.com | About Greg:
> https://about.me/greg.low
>
>
>
> *From:* Tony Wright via ozdotnet <ozdotnet@ozdotnet.com>
> *Sent:* Wednesday, 12 October 2022 9:22 AM
> *To:* ozDotNet <ozdotnet@ozdotnet.com>
> *Cc:* Tony Wright <tonyw...@gmail.com>
> *Subject:* Resolving a SQL server deadlock
>
>
>
> Hi there,
>
>
>
> I am currently trying to resolve a sql server deadlock issue. Someone in
> the team is adamant that the deadlock is caused by a new index we've added.
> I'm not sure I've ever heard of an index causing a deadlock, but I'm
> always interested in something new to me, anyway! I have attached a
> deadlock graph from SolarWinds Sql Sentry Plan Explorer and I'm hoping
> someone else can help me understand it.
>
>
>
> There are essentially 2 queries. (I've renamed all objects)
>
> The first query is along the lines of:
>
>
>
> SELECT DISTINCT *
>
> FROM dbo.Shipping
>
> WHERE StatusID=@StatusID
>
> AND IsLocked=@IsLocked AND TypeID=@TypeID AND ShipDate < @ShipDate.
>
>
>
> The field list is expanded and there are lots of fields. It was generated
> inside a third party tool.
>
>
>
> The second query is an UPDATE statement in a stored proc, along the lines
> of:
>
> UPDATE dbo.Shipping
>
> SET IsLocked=@IsLocked,
>
> LockedBy=CASE WHEN @Locked=1 THEN @PersonWhoLocked ELSE NULL END,
>
> LockedDate=CASE WHEN @Locked=1 THEN GetUtcDate() ELSE ModificationDate END
>
> WHERE ShippingID=@ShippingID
>
>
>
> Process 1 (126) is the SELECT, which is the deadlock victim
>
> Process 2 (133) is the UPDATE stored proc
>
> Process 3 (139) is the UPDATE stored proc
>
>
>
> I'm going to take a stab at what might be happening. Hopefully someone can
> fill the gaps in my knowledge.
>
>
>
> A deadlock occurs when Process A has a lock on Resource 1 and wants to
> acquire a lock on Resource 2, while Process B has a lock on Resource 2 and
> wants to acquire a lock on Resource 1.
>
>
>
> So reading this graph, it looks like:
>
>
>
> The 2 resources are the Page Lock on dbo.Shipping, and the Key Lock on the
> shipping Primary Key.
>
>
>
> So on the graph it looks to me like Process 1 holds a lock on the Page
> Lock resource and wants a lock on the Key Lock. Process 3 holds a lock on
> the Key lock resource and wants a lock on the Page Lock resource
>
>
>
> So:
>
> (1) The SELECT occurs on the shipping table and it takes a Page lock (S)
>
> (2) The first UPDATE occurs (133) and that requires an exclusive lock, but
> it can't get it because there's already a Shared lock, so it puts in an
> Intent to get an Exclusive lock (IX)
>
> (3) The second UPDATE occurs and it acquires an Exclusive lock (X) on the
> Primary Key Index.
>
> (4) Not sure what happens here and how this is relevant
>
> (5) The second UPDATE requests an exclusive lock on the Page of Shipping
> records.
>
> (6) Meanwhile the SELECT tries to get a Shared lock on one of the keys
> that are locked by the second UPDATE statement
>
>
>
> Can anyone help me understand what is going on here and how this is going
> to help me prevent further deadlocks?
>
>
>
> Kind regards,
>
> Tony
>
