Tuesday, March 11, 2008 1:01 PM tonyrogerson

sp_send_dbmail in a transaction with @query causes unresolvable deadlock

So you are using the @query parameter of sp_send_dbmail and all of a sudden you start getting blocks and your connection running sp_send_mail is the cause, your connection running sp_send_mail just sits there in a runnable state with the lastwaittype of MSQL_XP.

What is happening?

create table ##ed_volval ( acol int not null )

 

begin tran

 

insert ##ed_volval values( 1 )

 

    EXEC msdb.dbo.sp_send_dbmail

        @recipients='tonyrogerson@torver.net',

        @subject = N'Bad Idea',

        @body = N'Bad idea',

            @query = N'select * from ##ed_volval',

        @body_format = 'HTML',

        @profile_name = 'TORVER' ;

The above connection starts a transaction then inserts rows into a table; you then call sp_send_dbmail to fire off an email message.

Sp_send_dbmail actually spawns another completely separate connection into SQL Server using an extended stored procedure, because your connection has the table locked the spawned connection blocks which in turn prevents the connection that issued the sp_send_dbmail from continuing – stale mate!

To identify the spawned process you can track it through, on a real system it can be a tad problematic.

select program_name, spid, blocked, lastwaittype, waitresource

from master..sysprocesses

where blocked > 0

   or spid in ( select blocked from master..sysprocesses where blocked > 0 )


You will see two runs...

One will be the connection running sp_send_dbmail, this will have the lastwaittype of MSQL_XP; the second row will have a program_name of SQLCMD and will be blocked by the connection running sp_send_dbmail.

This should never happen if sp_send_dbmail used Service Broker properly; why it can’t just plonk the row onto the queue and let activation stored procedures pick up the task I don’t understand. It would allow the caller process to continue and the activation stored procedure would just get blocked until the transaction released.

So, if you ever get the lastwaittype MSQL_XP then look for any connections with a program_name SQLCMD and trace it through to see if it’s a sp_send_dbmail problem and if you can guarentee then use KILL on it to release the process running sp_send_dbmail.

Note – the pain here is that the connection running sp_send_dbmail (the blocker) cannot be killed!

Filed under:

Comments

# Interesting Finds: March 11, 2008

Tuesday, March 11, 2008 2:16 PM by Jason Haley

# re: sp_send_dbmail in a transaction with @query causes unresolvable deadlock

Tuesday, March 11, 2008 2:54 PM by simonsabin

I guess the issue is the sending of the email and the generating of the email. The latter needs to run the query to build the email which I guess isn't using service broker. Once the email content is generated then it puts that on the service broker queue.

From a security perspective I can understand it, otherwise you would need to let database mail have access to your database and data which is a bad thing.

# re: sp_send_dbmail in a transaction with @query causes unresolvable deadlock

Tuesday, March 11, 2008 3:04 PM by tonyrogerson

Actually that makes perfect sense.

The security context needs to be the connection running the sp_send_dbmail - but, perhaps the connection in use should make use of CLR rather than an extended stored procedure and use the current connection context??

Must be a better way.

Tony.

# re: sp_send_dbmail in a transaction with @query causes unresolvable deadlock

Wednesday, March 12, 2008 2:42 PM by Alex_Kuznetsov

Yep. In my system one process comes up with message bodies and stores them in a table, and another one only sends them out and does nothing else.

# re: sp_send_dbmail in a transaction with @query causes unresolvable deadlock

Tuesday, March 18, 2008 5:19 PM by AndyC London

If they read their own documentation... then they should use srv_getbindtoken in the XP to get a token and then exec sp_bindsession before running the query, this puts the loopback connection into the same connection as the initiating one.

However, I'd agree with the previous comments, I'd like to see this using the serivce broker instead to queue up the mails. I'm not sure how on one hand they are suggesting that XPs are depreciated and on the otherhand they are still using them, themselves.

When I've seen this scenario, I call them "external deadlocks", I'm not sure if there's a proper name for these.