SQL Deadlock Exception

SQL Deadlock Exception

We need to come up a Pooling Module that assign a record to a certain user when a button was clicked.

As a solution:

I have created a sql script that uses XLOCK table hint, when two users run the script simultaneously in SQL Management Studio the script will wait for the first user's execution to be finished before executing the second user's script.

My question is how come that when I use the same script in Advance SQL in outsystems the query is throwing a deadlock exception error and not behaving the same as when I run it in SQL Management Studio?

Is there a way to just decrease the possibility of a deadlock excception?

The end result of the query optimizer can be very differen when executing from SQL Management Studio or Advanced query.

Out of the box the Advanced query is executed with read uncommited while the default setting in SQL Management studio is read commited.

This factor alone would minimize the dead lock. But it is not the only one.

The best way to investigate this would be to:

Intercept the advanced query in the trace

Copy the query to SQL Management

Declare all variables, etc

Change the SQL Management studio connection to read uncommited

And then look at the execution plan

Although I dont know the use case, the programatic way to place locks in the platform is by using the GetForUpdate table action.

Assuming there is a row which you want to change, use the get for update of that row in the start of the submit. Then update it normaly. Once the submit is done the commit is executed and the row released.

Like Rui mentioned the execution depends on the optimizer.
In particular most advanced queries will execute on a separate transaction to avoid the multiple readers limitation of the sql client driver (MARS).

So forcing a explicit lock in an advanced query is a bad idea.
The correct way to do it in OutSystems is to use the GetForUpdate entity action and lock a specific row on an entity. That will make sure you keep the lock until the end of the transaction without getting any deadlocks.