Skip to main content
www.usoft.com To www.usoft.com

Hiya,

We use a scheduled clean-up task to delete data from a SQL Server 2019 table when it has become obsolete. During high loads, we regularly run into time-outs in a second process which is attempting to insert data into the same table.

In the SQL Profiler screenshot below, you can see clean-up process 73 performing a ‘SELECT FOR DELETE’ statement. That uses the UPDLOCK hint, which result in a lock escalation on (a page beneath) the IN_ICM_EVENT table .

Process 69 (which is actually a second rules service) attempts to do an insert into that same table, but times out after 10 seconds due to the lock held by the clean-up.

We know the data being deleted is not going to be updated (or even selected) by any other process, so we don’t need the lock held by the clean-up process.

Does anyone know if there is any way to force the rules engine to forego that lock?

Hi Marc,


When data is manipulated in a USoft application the USoft Rules engine must check a number of things. In case of a delete statement this mainly concerns referential integrity and whether business rules (constraints) apply. To do so, the Rules engine creates a kind of cursor using the "select ... with holdlock" statement that you mentioned.


In your case, no extra work is involved for the Rules Engine, so a set manipulation could be used. Three settings in USoft 10.1 determine whether the Rules Engine can use set manipulations.
The first is a Rules engine parameter that you can find in the USoft Definer:  ALLOW_SET_MANIPULATIONS

Definer Rules Engine parameters

You can find the description of this parameter at: Community: Allow Set Manipulations

It shows ten cases when a set manipulation is not possible. In your case none of them apply.

The second setting is a deployment configuration in the USoft Authorizer: Allow_disable_constraint. This is relevant if you use a Rules Service for your cleanup job. See:
Community: Allow Disable constraints
 

The third setting is a check box for the statement in your SQL task: Disable Rules. This flag has the same effect as calling the action DeactivateAllConstraintChecks() before calling the SQL task.

SQL Task with disable Rules

Keep in mind that a delete statement in SQL Server will demand an exclusive lock. Yet because the set manipulation requires just one statement, it will be faster and probably prevent you from causing the other process to run into a lock timeout.

 

Regards,

 

Robert

Excellent information, Robert, thanks!

For now we have only disabled the rules for the relevant SQL statements, which seems to do the job for us. If necessary, I’ll dive a bit deeper into the other settings.

Reply


Terms & ConditionsCookie settings