CODEWITHSUNDEEP
c#sqlsql-serverdeadlocksqltransaction
stoney78us
stoney78us

Reputation: 305

Multiple transactions on the same table, at the same time

I am running into a problem which I have a table that can be edited by many thread, each thread creates a transaction to work with this table, if a transaction takes so long to complete, other threads will throw the exception "Query Timeout Expired". I guess there is a timeout setting somewhere for transactions.

I could use a thread lock to deal with this; however, is there a way to do this on the server side? Can I create a lock in SQL server ? Any helps would be appreciated.

Upvotes: 0

Views: 4106

Answers (1)

Randy Minder
Randy Minder

Reputation: 48392

The timeout you are receiving is not a "transaction" timeout. It is a general query timeout. This query could be a Select, Update, Delete etc. The timeout value is generally controlled in the connection string.

I don't believe you can specifically lock rows, pages or tables in SQL Server, which is probably a good thing. Frankly, this isn't the crux of the problem. Your transactions are taking way too long to execute. They should never take so long as to cause other queries to timeout (probably at least 30 seconds). You are going to bring your server to its knees with transactions taking this long. A transaction should be kept to a couple seconds, at the very most.

I would suggest you first take a look at the statements requiring the transaction (Update, Delete or Insert), run them through Query Analyzer in SSMS, and make sure no table or index scans are being done. My guess is this is happening, and the tables involved in the transaction are on the large side (hundred of thousands of rows or more).

Upvotes: 1

Related Questions