Pracuję nad tym impasem już od kilku dni i bez względu na to, co robię, problem występuje w ten czy inny sposób.
Po pierwsze, ogólne założenie: mamy wizyty z VisitItems w relacji jeden do wielu.
Informacje istotne na stronie VisitItems:
CREATE TABLE [BAR].[VisitItems] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[VisitType] INT NOT NULL,
[FeeRateType] INT NOT NULL,
[Amount] DECIMAL (18, 2) NOT NULL,
[GST] DECIMAL (18, 2) NOT NULL,
[Quantity] INT NOT NULL,
[Total] DECIMAL (18, 2) NOT NULL,
[ServiceFeeType] INT NOT NULL,
[ServiceText] NVARCHAR (200) NULL,
[InvoicingProviderId] INT NULL,
[FeeItemId] INT NOT NULL,
[VisitId] INT NULL,
[IsDefault] BIT NOT NULL DEFAULT 0,
[SourceVisitItemId] INT NULL,
[OverrideCode] INT NOT NULL DEFAULT 0,
[InvoiceToCentre] BIT NOT NULL DEFAULT 0,
[IsSurchargeItem] BIT NOT NULL DEFAULT 0,
CONSTRAINT [PK_BAR.VisitItems] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_BAR.VisitItems_BAR.FeeItems_FeeItem_Id] FOREIGN KEY ([FeeItemId]) REFERENCES [BAR].[FeeItems] ([Id]),
CONSTRAINT [FK_BAR.VisitItems_BAR.Visits_Visit_Id] FOREIGN KEY ([VisitId]) REFERENCES [BAR].[Visits] ([Id]),
CONSTRAINT [FK_BAR.VisitItems_BAR.VisitTypes] FOREIGN KEY ([VisitType]) REFERENCES [BAR].[VisitTypes]([Id]),
CONSTRAINT [FK_BAR.VisitItems_BAR.FeeRateTypes] FOREIGN KEY ([FeeRateType]) REFERENCES [BAR].[FeeRateTypes]([Id]),
CONSTRAINT [FK_BAR.VisitItems_CMN.Users_Id] FOREIGN KEY (InvoicingProviderId) REFERENCES [CMN].[Users] ([Id]),
CONSTRAINT [FK_BAR.VisitItems_BAR.VisitItems_SourceVisitItem_Id] FOREIGN KEY ([SourceVisitItemId]) REFERENCES [BAR].[VisitItems]([Id]),
CONSTRAINT [CK_SourceVisitItemId_Not_Equal_Id] CHECK ([SourceVisitItemId] <> [Id]),
CONSTRAINT [FK_BAR.VisitItems_BAR.OverrideCodes] FOREIGN KEY ([OverrideCode]) REFERENCES [BAR].[OverrideCodes]([Id]),
CONSTRAINT [FK_BAR.VisitItems_BAR.ServiceFeeTypes] FOREIGN KEY ([ServiceFeeType]) REFERENCES [BAR].[ServiceFeeTypes]([Id])
)
CREATE NONCLUSTERED INDEX [IX_FeeItem_Id]
ON [BAR].[VisitItems]([FeeItemId] ASC)
CREATE NONCLUSTERED INDEX [IX_Visit_Id]
ON [BAR].[VisitItems]([VisitId] ASC)
Odwiedź informacje:
CREATE TABLE [BAR].[Visits] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[VisitType] INT NOT NULL,
[DateOfService] DATETIMEOFFSET NOT NULL,
[InvoiceAnnotation] NVARCHAR(255) NULL ,
[PatientId] INT NOT NULL,
[UserId] INT NULL,
[WorkAreaId] INT NOT NULL,
[DefaultItemOverride] BIT NOT NULL DEFAULT 0,
[DidNotWaitAdjustmentId] INT NULL,
[AppointmentId] INT NULL,
CONSTRAINT [PK_BAR.Visits] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_BAR.Visits_CMN.Patients] FOREIGN KEY ([PatientId]) REFERENCES [CMN].[Patients] ([Id]) ON DELETE CASCADE,
CONSTRAINT [FK_BAR.Visits_CMN.Users] FOREIGN KEY ([UserId]) REFERENCES [CMN].[Users] ([Id]),
CONSTRAINT [FK_BAR.Visits_CMN.WorkAreas_WorkAreaId] FOREIGN KEY ([WorkAreaId]) REFERENCES [CMN].[WorkAreas] ([Id]),
CONSTRAINT [FK_BAR.Visits_BAR.VisitTypes] FOREIGN KEY ([VisitType]) REFERENCES [BAR].[VisitTypes]([Id]),
CONSTRAINT [FK_BAR.Visits_BAR.Adjustments] FOREIGN KEY ([DidNotWaitAdjustmentId]) REFERENCES [BAR].[Adjustments]([Id]),
);
CREATE NONCLUSTERED INDEX [IX_Visits_PatientId]
ON [BAR].[Visits]([PatientId] ASC);
CREATE NONCLUSTERED INDEX [IX_Visits_UserId]
ON [BAR].[Visits]([UserId] ASC);
CREATE NONCLUSTERED INDEX [IX_Visits_WorkAreaId]
ON [BAR].[Visits]([WorkAreaId]);
Wielu użytkowników chce jednocześnie aktualizować tabelę VisitItems w następujący sposób:
Oddzielne żądanie internetowe utworzy wizytę z VisitItems (zwykle 1). Następnie (żądanie problemu):
- Przychodzi żądanie sieciowe, otwiera sesję NHibernate, rozpoczyna transakcję NHibernate (przy użyciu Repeatable Read z włączonym READ_COMMITTED_SNAPSHOT).
- Przeczytaj wszystkie elementy wizyty dla danej wizyty przez VisitId .
- Kod ocenia, czy elementy są nadal aktualne lub czy potrzebujemy nowych, stosując złożone reguły (tak długo trwające, np. 40 ms).
- Kod znajduje 1 element, który należy dodać, dodaje go za pomocą NHibernate Visit.VisitItems.Add (..)
- Kod identyfikuje, że jeden element musi zostać usunięty (nie ten, który właśnie dodaliśmy), usuwa go za pomocą NHibernate Visit.VisitItems.Remove (element).
- Kod zatwierdza transakcję
Za pomocą narzędzia symuluję 12 jednoczesnych żądań, co może się zdarzyć w przyszłym środowisku produkcyjnym.
[EDYCJA] Na żądanie usunąłem wiele szczegółów dochodzenia, które tu dodałem, aby było krótkie.
Po wielu badaniach następnym krokiem było wymyślenie sposobu, w jaki mogę zablokować wskazówkę dotyczącą innego indeksu niż ten użyty w klauzuli where (tj. Klucz podstawowy, ponieważ jest on używany do usuwania), więc zmieniłem moją instrukcję blokady na :
var items = (List<VisitItem>)_session.CreateSQLQuery(@"SELECT * FROM BAR.VisitItems WITH (XLOCK, INDEX([PK_BAR.VisitItems]))
WHERE VisitId = :visitId")
.AddEntity(typeof(VisitItem))
.SetParameter("visitId", qi.Visit.Id)
.List<VisitItem>();
To nieznacznie zmniejszyło impasy częstotliwości, ale nadal się zdarzały. I tutaj zaczynam się gubić:
<deadlock-list>
<deadlock victim="process3f71e64e8">
<process-list>
<process id="process3f71e64e8" taskpriority="0" logused="0" waitresource="KEY: 5:72057594071744512 (a5e1814e40ba)" waittime="3812" ownerId="8004520" transactionname="user_transaction" lasttranstarted="2015-12-14T10:24:58.010" XDES="0x3f7cb43b0" lockMode="X" schedulerid="1" kpid="15788" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2015-12-14T10:24:58.013" lastbatchcompleted="2015-12-14T10:24:58.013" lastattention="1900-01-01T00:00:00.013" clientapp=".Net SqlClient Data Provider" hostname="ABC" hostpid="10016" loginname="bsapp" isolationlevel="repeatable read (3)" xactid="8004520" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="254" sqlhandle="0x0200000024a9e43033ef90bb631938f939038627209baafb0000000000000000000000000000000000000000">
unknown
</frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown
</frame>
</executionStack>
<inputbuf>
(@p0 int)SELECT * FROM BAR.VisitItems WITH (XLOCK, INDEX([PK_BAR.VisitItems]))
WHERE VisitId = @p0
</inputbuf>
</process>
<process id="process4105af468" taskpriority="0" logused="1824" waitresource="KEY: 5:72057594071744512 (8194443284a0)" waittime="3792" ownerId="8004519" transactionname="user_transaction" lasttranstarted="2015-12-14T10:24:58.010" XDES="0x3f02ea3b0" lockMode="S" schedulerid="8" kpid="15116" status="suspended" spid="65" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-12-14T10:24:58.033" lastbatchcompleted="2015-12-14T10:24:58.033" lastattention="1900-01-01T00:00:00.033" clientapp=".Net SqlClient Data Provider" hostname="ABC" hostpid="10016" loginname="bsapp" isolationlevel="repeatable read (3)" xactid="8004519" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="98" sqlhandle="0x0200000075abb0074bade5aa57b8357410941428df4d54130000000000000000000000000000000000000000">
unknown
</frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown
</frame>
</executionStack>
<inputbuf>
(@p0 int)DELETE FROM BAR.VisitItems WHERE Id = @p0
</inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594071744512" dbid="5" objectname="BAR.VisitItems" indexname="PK_BAR.VisitItems" id="lock449e27500" mode="X" associatedObjectId="72057594071744512">
<owner-list>
<owner id="process4105af468" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process3f71e64e8" mode="X" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594071744512" dbid="5" objectname="BAR.VisitItems" indexname="PK_BAR.VisitItems" id="lock46a525080" mode="X" associatedObjectId="72057594071744512">
<owner-list>
<owner id="process3f71e64e8" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process4105af468" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
Śledzenie wynikowej liczby zapytań wygląda następująco.
[EDYCJA] Whoa. Co za tydzień. Zaktualizowałem teraz ślad o niezreagowany ślad odpowiedniego stwierdzenia, które moim zdaniem prowadzi do impasu.
exec sp_executesql N'SELECT * FROM BAR.VisitItems WITH (XLOCK, INDEX([PK_BAR.VisitItems]))
WHERE VisitId = @p0',N'@p0 int',@p0=3826
go
exec sp_executesql N'SELECT visititems0_.VisitId as VisitId1_, visititems0_.Id as Id1_, visititems0_.Id as Id37_0_, visititems0_.VisitType as VisitType37_0_, visititems0_.FeeItemId as FeeItemId37_0_, visititems0_.FeeRateType as FeeRateT4_37_0_, visititems0_.Amount as Amount37_0_, visititems0_.GST as GST37_0_, visititems0_.Quantity as Quantity37_0_, visititems0_.Total as Total37_0_, visititems0_.ServiceFeeType as ServiceF9_37_0_, visititems0_.ServiceText as Service10_37_0_, visititems0_.InvoiceToCentre as Invoice11_37_0_, visititems0_.IsDefault as IsDefault37_0_, visititems0_.OverrideCode as Overrid13_37_0_, visititems0_.IsSurchargeItem as IsSurch14_37_0_, visititems0_.VisitId as VisitId37_0_, visititems0_.InvoicingProviderId as Invoici16_37_0_, visititems0_.SourceVisitItemId as SourceV17_37_0_ FROM BAR.VisitItems visititems0_ WHERE visititems0_.VisitId=@p0',N'@p0 int',@p0=3826
go
exec sp_executesql N'INSERT INTO BAR.VisitItems (VisitType, FeeItemId, FeeRateType, Amount, GST, Quantity, Total, ServiceFeeType, ServiceText, InvoiceToCentre, IsDefault, OverrideCode, IsSurchargeItem, VisitId, InvoicingProviderId, SourceVisitItemId) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15); select SCOPE_IDENTITY()',N'@p0 int,@p1 int,@p2 int,@p3 decimal(28,5),@p4 decimal(28,5),@p5 int,@p6 decimal(28,5),@p7 int,@p8 nvarchar(4000),@p9 bit,@p10 bit,@p11 int,@p12 bit,@p13 int,@p14 int,@p15 int',@p0=1,@p1=452,@p2=1,@p3=0,@p4=0,@p5=1,@p6=0,@p7=1,@p8=NULL,@p9=0,@p10=1,@p11=0,@p12=0,@p13=3826,@p14=3535,@p15=NULL
go
exec sp_executesql N'UPDATE BAR.Visits SET VisitType = @p0, DateOfService = @p1, InvoiceAnnotation = @p2, DefaultItemOverride = @p3, AppointmentId = @p4, ReferralRequired = @p5, ReferralCarePlan = @p6, UserId = @p7, PatientId = @p8, WorkAreaId = @p9, DidNotWaitAdjustmentId = @p10, ReferralId = @p11 WHERE Id = @p12',N'@p0 int,@p1 datetimeoffset(7),@p2 nvarchar(4000),@p3 bit,@p4 int,@p5 bit,@p6 nvarchar(4000),@p7 int,@p8 int,@p9 int,@p10 int,@p11 int,@p12 int',@p0=1,@p1='2016-01-22 12:37:06.8915296 +08:00',@p2=NULL,@p3=0,@p4=NULL,@p5=0,@p6=NULL,@p7=3535,@p8=4246,@p9=2741,@p10=NULL,@p11=NULL,@p12=3826
go
exec sp_executesql N'DELETE FROM BAR.VisitItems WHERE Id = @p0',N'@p0 int',@p0=7919
go
Teraz moja blokada wydaje się działać, ponieważ pokazuje się na wykresie impasu. Ale co? Trzy ekskluzywne zamki i jeden wspólny zamek? Jak to działa na tym samym obiekcie / kluczu? Myślałem, że dopóki masz wyłączną blokadę, nie możesz uzyskać wspólnej blokady od kogoś innego? I na odwrót. Jeśli masz wspólną blokadę, nikt nie może uzyskać blokady na wyłączność, muszą czekać.
Myślę, że brakuje mi głębszego zrozumienia tutaj, jak działają zamki, kiedy są one przyjmowane na wielu klawiszach na tym samym stole.
Oto niektóre z rzeczy, które próbowałem i ich wpływ:
- Dodano kolejną wskazówkę dotyczącą indeksu IX_Visit_Id do instrukcji lock. Brak zmiany
- Dodano drugą kolumnę do IX_Visit_Id (identyfikator kolumny VisitItem); daleko posunięte, ale i tak próbowałem. Brak zmiany
- Zmieniono poziom izolacji z powrotem do odczytu zatwierdzony (domyślnie w naszym projekcie), nadal występują impasy
- Zmieniono poziom izolacji na możliwy do serializacji. Wciąż występują impasy, ale gorsze (różne wykresy). Zresztą tak naprawdę nie chcę tego robić.
- Zabranie blokady stołu sprawia, że odchodzą (oczywiście), ale kto by to zrobił?
- Przyjmowanie pesymistycznej blokady aplikacji (przy użyciu sp_getapplock) działa, ale to prawie to samo co blokowanie tabeli, nie chcę tego robić.
- Dodanie podpowiedzi READPAST do podpowiedzi XLOCK nie miało znaczenia
- Wyłączyłem PageLock na indeksie i PK, bez różnicy
- Dodałem podpowiedź ROWLOCK do podpowiedzi XLOCK, bez różnicy
Uwaga dodatkowa na temat NHibernate: sposób, w jaki jest używany i rozumiem, że działa, polega na tym, że buforuje instrukcje sql, dopóki nie uzna, że konieczne jest ich wykonanie, chyba że wywołasz flush, czego nie próbujemy zrobić. Dlatego większość instrukcji (np. Leniwie załadowana lista agregatów VisitItems => Visit.VisitItems) jest wykonywana tylko wtedy, gdy jest to konieczne. Większość rzeczywistych instrukcji aktualizacji i usuwania z mojej transakcji jest wykonywana na końcu, gdy transakcja zostanie zatwierdzona (jak wynika z powyższego śledzenia SQL). Naprawdę nie mam kontroli nad zleceniem wykonania; NHibernate decyduje, kiedy co zrobić. Moja początkowa instrukcja blokady jest tak naprawdę tylko obejściem.
Ponadto, dzięki instrukcji lock, po prostu czytam elementy na nieużywaną listę (nie próbuję przesłonić listy VisitItems w obiekcie Visit, ponieważ nie o to mi chodzi, o ile wiem, że NHibernate powinien działać). Nawet jeśli najpierw przeczytałem listę za pomocą instrukcji niestandardowej, NHibernate nadal załaduje listę ponownie do swojej kolekcji obiektów proxy Visit.VisitItems za pomocą oddzielnego wywołania sql, które widzę w śladzie, gdy nadszedł czas, aby leniwie gdzieś go załadować.
Ale to nie powinno mieć znaczenia, prawda? Mam już blokadę na wspomnianym kluczu? Ładowanie go ponownie nie zmieni tego?
Na koniec, być może wyjaśnienie: każdy proces najpierw dodaje własną wizytę za pomocą VisitItems, a następnie wchodzi i modyfikuje ją (co spowoduje uruchomienie operacji usuwania i wstawiania oraz impasu). W moich testach nigdy nie ma żadnego procesu zmieniającego dokładnie tę samą wartość Visit lub VisitItems.
Czy ktoś ma pomysł, jak podejść do tego dalej? Czy mogę coś zrobić w inteligentny sposób (bez blokowania stolików itp.)? Chciałbym również dowiedzieć się, dlaczego ta potrójna blokada x jest możliwa nawet w tym samym obiekcie. Nie rozumiem.
Daj mi znać, jeśli do rozwiązania zagadki będą potrzebne dodatkowe informacje.
[EDYCJA] Zaktualizowałem pytanie o DDL dla dwóch zaangażowanych tabel.
Poproszono mnie również o wyjaśnienie oczekiwań: tak, kilka impasów tutaj i są w porządku, po prostu spróbujemy ponownie lub poprosimy użytkownika o ponowne przesłanie (ogólnie rzecz biorąc). Ale przy obecnej częstotliwości z 12 równoczesnymi użytkownikami spodziewałbym się, że będzie tylko jedna co kilka godzin. Obecnie pojawiają się wiele razy na minutę.
Oprócz tego otrzymałem więcej informacji o trancount = 2, co może wskazywać na problem z zagnieżdżonymi transakcjami, których tak naprawdę nie używamy. Ja również to zbadam i udokumentuję wyniki tutaj.
SELECT OBJECT_NAME(objectid, dbid) AS objectname, * FROM sys.dm_exec_sql_text(0x0200000024a9e43033ef90bb631938f939038627209baafb0000000000000000000000000000000000000000)
za SQLHandle na każdej ramce executionStack dalszego określenia tego, co jest rzeczywiście wykonywana.