Handle deadlock in polling Scenarii

Situation

In some scenarii, you must poll a datatable and you use WCF-SQL. This polling must update a column for  rows which are red. Due to the polling frequency you might encounter deadlock.

Consequence there is no more activity in your BizTalk Server, and you can loose some newly data which are never inserted

So you need to know if you have deadlock, here is the query that retrieve all status’ queries.

SELECT P.spid, P.ecid, P.status, P.loginame, P.hostname, P.blocked AS blk, D.name as dbname, P.cmd, P.request_id, T.text FROM sys.sysprocesses P CROSS APPLY sys.dm_exec_sql_text(P.sql_handle) T LEFT JOIN sys.sysdatabases D ON D.dbid = P.dbid

If you find suspended, then you’ve got DeadLock

The problem come from the fact that you try to insert new data while you try to update with biztalk already existing data.

Workaround

You must specify a deadlock victim. Most of time, we declare BizTalk as deadlock victim. Indeed, BizTalk have a frequence polling, if it can’t read this time, it could read the next time. But it’s just a general case.

To set a deadlock victim, in your SQL sentence you have to specify the deadlock priority. there are three levels

LOW

Specifies that the current session will be the deadlock victim if it is involved in a deadlock and other sessions involved in the deadlock chain have deadlock priority set to either NORMAL or HIGH or to an integer value greater than -5. The current session will not be the deadlock victim if the other sessions have deadlock priority set to an integer value less than -5. It also specifies that the current session is eligible to be the deadlock victim if another session has set deadlock priority set to LOW or to an integer value equal to -5.

NORMAL

Specifies that the current session will be the deadlock victim if other sessions involved in the deadlock chain have deadlock priority set to HIGH or to an integer value greater than 0, but will not be the deadlock victim if the other sessions have deadlock priority set to LOW or to an integer value less than 0. It also specifies that the current session is eligible to be the deadlock victim if another other session has set deadlock priority to NORMAL or to an integer value equal to 0. NORMAL is the default priority.

HIGH

Specifies that the current session will be the deadlock victim if other sessions involved in the deadlock chain have deadlock priority set to an integer value greater than 5, or is eligible to be the deadlock victim if another session has also set deadlock priority to HIGH or to an integer value equal to 5.

Source MSDN

For my problem, BizTalk will have a low deadlock priority and the program which insert data the hight deadlock priority. We never loose data again

Publicités

Laisser un commentaire

Choisissez une méthode de connexion pour poster votre commentaire:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s