Hi,
I have a server action that executes multiple SQL statements to update an entity. Each statement updates a set of records in the entity and each SQL statement updates only a specific field in the entity. So one statement formats a date, another lookups a reference Id and updates it etc. The server action is executed by light BPT processes so the entity is updated by multiple processes at the same time.
In order to prevent an update of the same record by multiple processes, each SQL statement contains an IN statement filtering a specific recordset by primary key (Id). At the start of the server action, a part of an IN statement is formed, e.g. 1, 2, 3, and that's placed inside each SQL statement. So each SQL statement has the following where statement: "WHERE Id IN (@Ids)". This where statement on the primary key should lead to a record lock instead of a table lock. Correct me if I'm wrong here. Each server action updates a specific recordset and although the server action is executed multiple times at the same moment, because of the where statement one record is can only be updated by one process. Again, correct me if I'm wrong here.
Despite this, I'm getting a deadlock. I'm updating about 5600 records at the same time, so you would expect multiple deadlocks but it's only one. I can't figure out why I'm getting the deadlock. Any help, suggestions or better solutions would be highly appreciated.
Kind regards,
Ramon
Hi @Ramon Pigmans,
Is there a reason why you need Light BPT to update these records at the same time?
If you are updating about 5600 records, you probably don't even need a timer, one single server action with the update queries should suffice.
If you are dealing with >100K of records or even >1M, I still believe a timer with the update queries would be better. Even if they are complex, update queries usually run pretty fast.
If the server doesn't respond, you would have to either paginate the update queries or also update a new flag in the record (e.g. IsUpdated), to filter the records you already updated.
Let me know if any of this makes sense to you.
Thank you,
Hi Emily,
Thanks for your reply. 5600 records is my development recordset. The actual load will probably be around 500K records. I've tried a commit after each SQL statement and that seems to do the trick. But I still don't understand the deadlock.
Hi Ramon,
Just for my understanding, are you 100% sure each LBPT process has a unique set of Ids it updates? And all SQL updates in a single LBPT process update the same set of Ids? (Which, I might add, seems a bit inefficient, why not update all attributes in one go?)
Thanks for your answer @Kilian Hekhuis. I'm pretty sure each LBPT proces has a set of unique id's but I'll double check again. The reason for the multiple SQL statements is that a lot of attributes are looked up in other tables but I guess your right, I'll try to combine some of the statements.