Problem with inserting records from multiple computers in the same database at the same time in SQL Server 2005

I have a web portal designed in ASP.NET in which we ask customers to enter the data. On the click of submit button all i have done is, just read the data and called a stored procedure which inserts that into a table. There seems to be a problem in SQL Server 2005 while inserting the data from multiple computers at the same time. We have test开发者_开发百科ed in our lab with three computers, result is that we get the data inserted successfully in only one machine and on the other two machines we get error on page.

I have used transactions in the stored procedure and also tried setting the isolation levels to READ_UNCOMMITTED, SERIALIZABLE and SNAPSHOT. Nothing seems to work properly. Am frustrated with this from past one day. Any help highly appreciated.

Thanks, Manoj

---

I think it's because you are using transaction...

According here:

BEGIN TRANSACTION starts a local transaction for the connection issuing the statement. Depending on the current transaction isolation level settings, many resources acquired to support the Transact-SQL statements issued by the connection are locked by the transaction until it is completed with either a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. Transactions left outstanding for long periods of time can prevent other users from accessing these locked resources, and also can prevent log truncation.

In your case, you tested your application using 3pc's at the same time. Since you are using transaction, If those users are using the same resources at the same time in your db (your SPs, tables, etc...), that particular resource will be locked and the other users will not be able to use it unless the transaction of the first user is done or commited.

Here is a good article I found: http://omaralzabir.com/linq_to_sql_solve_transaction_deadlock_and_query_timeout_problem_using_uncommitted_reads/

---

The marked as answer reply tells about deadlock. Though, there was no sufficient information provided to assume this, so it can be just blocking waiting issue.

Which error do you see:

• 1)
  deadlock victim 1205 error
  "Server: Msg 1205, Level 13, State 50, Line 1
  Transaction (Process ID 5?) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."?
• 2)
  time-out error
  "Timeout Expired. The Timeout Period Elapsed Prior To Completion Of The Operation Or The Server Is Not Responding."

Since the topic starter wrote: "But the table just locks and releases after 5 to 6 mins", I believe this is the period for 2) while deadlock victims 1) are chosen and rollback-ed in 5 sec.

Also, it was mentioned that inserts are from different computers. Queries run by transactional COM+ components will default to serializable tx iso level which exclude deadlocking but increase blocking contention (waiting).

"I have used transactions in the stored procedure and also tried setting the isolation levels to READ_UNCOMMITTED, SERIALIZABLE and SNAPSHOT. Nothing seems to work properly"

This, more than probably, means that your design and indexing are not "proper" but SQL Server just make its work properly.

With the information provided it is guessing game to advise anything.

Avoid placing CL-IX (clustered indexes) on columns that are DML-ed (updated, inserted, deleted). DMLs to clustered index key columns will require locks on the CL-IX (to move the row) and all nonclustered indexes (since the leaf level of non-clustered indexes reference rows by CL-IX key value)

You should have an indexed table or the whole table will be locked (blocked) on insert leading to increased waiting time and timeouts.

Read "How to resolve blocking problems that are caused by lock escalation in SQL Server" (note: this is outdated article. For ex., you should use TOP instead of deprecated ROWCOUNT adviced there)

In SQL Server 2005 and higher one should consider READ COMMITTED SNAPSHOT tx iso level , which is possible whenever the default READ COMMITTED was used.

---

You might wanna consider deadlocks. This usually happens when multiple users use the stored procedure simultaneously. In order to avoid deadlock and make sure every query from the user will succeed you will need to do some handling during update failures and to do this you will need a try catch. This works on Sql Server 2005,2008 only.

DECLARE @Tries tinyint

SET @Tries = 1

WHILE @Tries <= 3

BEGIN

  BEGIN TRANSACTION

  BEGIN TRY

  -- do your insert statement here

   COMMIT

   BREAK
  END TRY

  BEGIN CATCH

   SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() as ErrorMessage

   ROLLBACK

   SET @Tries = @Tries + 1

   CONTINUE

 END CATCH

END

---

How long does this proc take to run? Perhaps what you need is some serious performance tuning of the proc if it is causing timeouts for other users trying to insert. No one proc should be locking tables for 5-6 minutes.