Interesting thread from the internal dbtalk alias that is used for supporting SQL Server technical questions.

The thread that triggered this post references the following forum problem;

http://www.unixadmintalk.com/f46/sp_send_dbmail-wont-send-email-using-query-175160/

Summary of the problem is that when you try and send mail in a transaction that uses queries then it appears to hang, if you remove the transaction all is well, so what's going on? Well BOL gives us a pretty big hint;

sp_send_dbmail (Transact-SQL)

[ @query = ] 'query'

Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

http://msdn2.microsoft.com/en-us/library/ms190307.aspx

As it transpires the use of the separate session to run the query can cause WAY more problems than the local variable problem.

David Browne a Technology Architect out of Dallas hits the nail on the head (probably<g>):

The query is issued immediately (not queued), and in a separate session.  If the query must wait to acquire locks held by the session calling sp_send_dbmail then the query execution will block, and you will experience a deadlock between the session running the query and the session waiting for sp_send_dbmail to finish.  Since the deadlock involves one lock wait and a non-lock wait, however, it is not detected as a deadlock and the two sessions simply hang.

Either commit the data so the other session can see it or change the query to read the uncommitted data with (nolock).