SQL Server Performance Forum – Threads Archive

Before Deadlock/ Blocking… ??

There is a table, T1, with one row in it. The row has an ID=1. One user has a session S1 in which he begins a transaction to insert a row with the ID=2 into the table. The transaction is still open as another user in his session S2 tries to read the first row (ID=1) from the table. The read of S2 is blocked! Here’s the code: – Preparation:
———————–
create table T1 (ID int NOT NULL,
Message varchar(500) NULL)
insert T1 values(1, ‘Message1’) Session S1:
———————–
begin tran
insert T1 values(2, ‘Message2’) Session S2:
———————–
select * from T1 where ID = 1 — This select is blocked until S1 is not done; here i want to send a notification message to session S2 user that ‘Insert is going on by other user..pls wait’ Is this possible to detect this situation by any way, and send a notification??
Ref:http://groups.google.co.in/group/co…g in sql server&rnum=2&hl=en#abafef260226f67c
Deepak Kumar –An eye for an eye and everyone shall be blind

http://www.sqlservercentral.com/columnists/lPeysakhovich/monitoringblocks.asp for your amusement.
In SQL 2005 we can take help of service broker in advance to notify such alerts. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thats really good..but do we have any alternates of executing "master..xp_sendmail" for sending notification.. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Deepak Kumar –An eye for an eye and everyone shall be blind

You could build your own mail queue system:
http://www.sqlteam.com/item.asp?ItemID=5908 If you use cdonts, jmail, aspmail or any other component to send the mail is up to you.

I mean, instead of sending email to session user.. is there any way to notify him? something easy/fast.. Like SQL engine send pop-up when it kill session of a user which was victim in deadlock chain. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> More smiles Deepak Kumar –An eye for an eye and everyone shall be blind

Using SQLAgent job you can use NET SEND provided the client machine must be logged on to the network. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

‘Net Send’ will not help.. as session use can be outsite network. Anything else? if possible. Deepak Kumar –An eye for an eye and everyone shall be blind

what is it youre looking for?
If NET SEND is no use, and email is not ideal, what other methods do you have in mind ?
SMS? ICQ? All doable but nothing is as easy as email

But also, to answer your initial question, there is no way in SQL2000 to reliably detect in advance whether a given query will be blocked. SQL Server detects deadlocks and will kill one of the connections causing it, so and an exception raised + error message returned

Hey Paul, good to see ya.. hows going. Deepak, as I said above in SQL 2005 you can take advantage of service broker.
If not you may try notification services under SQL 2000 for your task, checkhttp://www.microsoft.com/sql/NS/default.asp page. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>