How to figure out what record locks my record?

[Q&A] General
1

Hi,

Lately we’ve been getting a lot of errors when migrating insurances that my data is locked by another record and to rerun the transaction. Is there a way to figure out what process is locking my record (besides a trace)?

That way maybe we can figure out the timing issue of why newly imported data can be locked already.

Thanks in advance!

2

@Mila_Sparreboom, hi! My first though was about M10693 – Novulo Event Logging: to configure logs.

3

Hi, thanks for your response.

Quick question: how configurable is that? I opened the component quickly and it looks like you do need an existing datasource to look into. And that’s not included in the Novulo migereren van verzekeringen component. Could we still use that to figure out my deadlock error of is there another way?

4

@Mila_Sparreboom, what you can try first then (it is quicker): kendoopenlockslog tabel. So you can create an export definition for the data type = kendoopenlockslog ([kendoopenlockslog]), filter it like this:

boolean:and([
 boolean:not(this.lockedrecorddata.equals("[]")),
 this.kendoprocesslog.datetime.isgreater(datetime:load("xxxx-xx-xxTxx:xx:xx")),
 this.kendoprocesslog.datetime.islower(datetime:load("xxxx-xx-xxTxx:xx:xx"))
])

And add fields, for example:

afbeelding
afbeelding1300×441 29.7 KB

Then you should be able to see locked records. @Redmer, correct me if I am wrong.

5

@Mila_Sparreboom, does it work for you?

6

To be clear, is this about a lock set by the ExclusiveLockRecordAction from the Novulo Record Tools plug-in? Or about a deadlock triggered by SQL Server? The latter will show an error stating that your request has been chosen as a deadlock victim.

7

Hi Wim,

My error shows that I’ve been chosen as the deadlock victim. Do you have tips to remedy that?

8

First, you need to find the (other) query/queries involved in the deadlock. The problem with that is that the thread that “won” the deadlock situation never knew there was an issue and therefore won’t log any errors in the application log. Luckily SQL Server logs all queries involved. If you connect to your SQL Server using SQL Server Management Studio, you can find these logs under Management - Extended Events - Sessions - system_health - package0.event_file. You’re looking for an event around the time the deadlock happend, with name “xml_deadlock_report”. If you select it, you get a ‘Deadlock’ tab in the bottom part of the screen. If you hover the oval parts of the diagram, it shows the query involved. Hopefully the query is recognizable enough to trace down the culprit. The diagram also shows the indexes or other objects that were (about to be) locked by both queries.

The solution is usually to simplify or cut up the queries, or to add additional indexes.