May 4, 2011 at 4:49 pm
Hi,
We have a shopping basket in our business application with a parent, child table schema as follows.
CREATE TABLE [dbo].[tblBasketItem](
[BasketItemID] [uniqueidentifier] NOT NULL,
[UserID] [uniqueidentifier] NOT NULL,
[WhenCreated] [datetime] NOT NULL,
[ProductCode] [nvarchar](50) NULL,
[CurrencyID] [uniqueidentifier] NOT NULL,
[Description] [nvarchar](255) NULL,
[Quantity] [int] NOT NULL,
[ItemType] [varchar](255) NOT NULL,
CONSTRAINT [PK_BasketItem] PRIMARY KEY NONCLUSTERED
(
[BasketItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[tblBasketItemReport](
[BasketItemReportID] [uniqueidentifier] NOT NULL,
[BasketItemID] [uniqueidentifier] NOT NULL,
[CompanyReportID] [uniqueidentifier] NOT NULL,
[ReportPurchaseID] [uniqueidentifier] NOT NULL,
[LanguageId] [uniqueidentifier] NULL,
[LanguageName] [nvarchar](100) NULL,
[TeamName] [nvarchar](30) NULL,
CONSTRAINT [PK_BasketItemReport] PRIMARY KEY NONCLUSTERED
(
[BasketItemReportID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
CONSTRAINT [UQ_BasketItemReport_BasketItemID] UNIQUE NONCLUSTERED
(
[BasketItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblBasketItemReport] WITH CHECK ADD CONSTRAINT [FK_BasketItemReport_BasketItemID] FOREIGN KEY([BasketItemID])
REFERENCES [dbo].[tblBasketItem] ([BasketItemID])
GO
We then use two stored procs to add items to the basket and another to clear the basket once they have gone through the check out.
The insert proc inserts to the parent record first and then the child record
CREATE Procedure [dbo].[usp_SaveBasketItemReport]
@BasketItemIDuniqueidentifier,
@BasketItemReportIDuniqueidentifier,
@ProductCodenvarchar(50),
@CurrencyIDuniqueidentifier,
@user-iduniqueidentifier,
@Quantityint,
@Descriptionnvarchar(255) = null,
@ItemTypevarchar(255),
@ReportPurchaseIDuniqueidentifier,
@CompanyReportIDuniqueidentifier,
@LanguageIDuniqueidentifier,
@LanguageNamenvarchar(100),
@TeamNamenvarchar(30) = null
AS
BEGIN
--Attempt to update an existing basketitem row (if one exists)
UPDATEtblBasketItem
SETUserID = @user-id, Quantity = @Quantity, ProductCode = @ProductCode, CurrencyID = @CurrencyID,
[Description] = @Description, ItemType = @ItemType
WHEREBasketItemID = @BasketItemID
--If no rows were updated then one cannot already exist so we need to insert one
IF@@ROWCOUNT = 0
BEGIN
INSERTtblBasketItem (BasketItemID, UserID, WhenCreated, Quantity, ProductCode, CurrencyID,
[Description], ItemType)
VALUES(@BasketItemID, @user-id, getdate(), @Quantity, @ProductCode, @CurrencyID,
@Description, @ItemType)
END
--Attempt to update an existing basketitemreport row (if one exists)
UPDATEtblBasketItemReport
SETCompanyReportID = @CompanyReportID, ReportPurchaseID = @ReportPurchaseID, LanguageId = @LanguageID, LanguageName = @LanguageName, TeamName = @TeamName
WHEREBasketItemReportID = @BasketItemReportID
-- If no rows were updated then one cannot exist so we need to insert one
IF@@ROWCOUNT = 0
BEGIN
INSERTtblBasketItemReport (BasketItemReportID, BasketItemID, CompanyReportID, ReportPurchaseID, LanguageId, LanguageName, TeamName)
VALUES(@BasketItemReportID, @BasketItemID, @CompanyReportID, @ReportPurchaseID, @LanguageId, @LanguageName, @TeamName)
END
END
and the clear basket stored proc
CREATE Procedure [dbo].[ups_OrdersRemoveBasket]
@user-iduniqueidentifier
AS
BEGIN
DELETEbir
FROMtblBasketItemReport bir
INNER JOINtblBasketItem bi ON bi.BasketItemID = bir.BasketItemID
WHEREbi.UserID = @user-id
DELETEtblBasketItem WHERE UserID = @user-id
END
The issue is that we are getting deadlocks with these two queries.
As the insert query
Create parent record (tblBasketItem) - creates an exclusive table lock
Create child record (tblBasketItemReport)
Delete query
Delete child record (tblBasketItemReport) - create an exclusive table lock
Delete parent record (tblBasketItem)
I thought that this was standard practice to create records from parent down and delete in the reverse order.
How can the quries be changed to stop the deadlocks
Thanks in advance.
May 4, 2011 at 5:36 pm
Hi
First thinks 1st... Nice post!
Have you turned on the deadlock tracing so you can see what is actually deadlocking...
http://msdn.microsoft.com/en-us/library/ms178104.aspx This link will give you a head start.
Then look at the log, maybe attached to a post...
May 4, 2011 at 5:46 pm
Hi thanks for the response,
Yes I've already grabbed the deadlock trace using the sql profiler this is what lead me to these queries being the culprits. I've attached the deadlock file that was created by the profiler
May 4, 2011 at 5:54 pm
I am not an expert at this but I noticed that you are doing a delete using UserID and this column does not appear to be indexed. I think that will cause a table lock during the delete.
May 4, 2011 at 6:05 pm
Thanks for the tip, I added the index as you suggested and the execution plan shows that it is now using the index.
I also ran the clear basket sp with sp_lock2 and it shows that during the delete of the child data the query it takes out a shared table lock on the tblBasketItemReport table. And then during the delete of the parent table (tblBasketItem) that it upgrades the lock to an exclusive table lock.
So although its using the index it's still doing a table lock.
May 4, 2011 at 6:22 pm
Here is the execution plan for the clear basket sp
May 5, 2011 at 9:19 am
Not sure if this will help or not but I see your CX_BasketItem_Owner index is clustered. Since this column is a unique identifier I don't think it makes a good candidate for clustering.
May 5, 2011 at 9:45 am
Hi thanks for the response.
I've now changed the indexes on the database so the primary key is now clustered and the index on the owner is now non-clustered.
I now get a rangeS-S convert to RangeI-N lock on the new non-clustered index. I'm assuming this is because the transaction is running in serializable isolation mode.
I'm trying to downgrade the isolation mode to read committed to see if that stops the deadlock.
May 6, 2011 at 10:26 am
serializable isolation, no explicit transaction control, no error handling, GUIDs all over the place, missing SET NOCOUNT ON - wow, where to start with the issues here...
See here and the associated 2 posts for a guide to deadlock troubleshooting.
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
BTW, you REALLY need to get a professional to give your stuff a review!!! he/she could also probably fix the deadlock problems pretty quickly.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply