SQL Server Performance Forum – Threads Archive

Deadlock in Select Max() statement

We are using SQL2k with Sp4. SQL server reports a deadlock occurance which involved the following statement from two different SPID. SPID: 183 Select max(Link_ID) as NewLinkID from TableA where FieldA = 6212 and QID = 4111 SPID 171: Select max(Link_ID) as NewLinkID from TableA where FieldA = 6309 and QID = 4111 Both processes are using the same index based on QID. The link_id field is an identity field. Frankly I didn’t know two select statements would cause deadlock. Secondly, the statement is very simple so I don’t know how it would caus deadlock. Is it something to do with Max() with an indentity field? If anyone can give me some idea, I would appreciate it. Wingman

---

Are the queries executed twice in a single sql connection?

---

Based on my posted statements, each has a different FieldA value so I don’t believe the queries executed twice. Plus each has a different SPID so this would suggest not. Did I understand your question correctly?

---

any application you use for the two queries execution? (eg..asp.net) if so, did you place both queries when you established the sql connection only once?

---

We are using coldfusion so the connection is established via it. The statements were submitted via the ColdFusion web application by users.

---

maybe you can try: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED Select max(Link_ID) as NewLinkID from TableA

---

]]>