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

Question

I have a web portal developed in ASP.NET in which we ask customers to enter data. When I clicked the submit button, all I did was just read the data and name the stored procedure that inserts it into the table. In SQL Server 2005, a problem occurs while inserting data from multiple computers. We tested three computers in our laboratory, as a result we received data inserted successfully on only one machine, and on two other machines we get an error on the page.

I used transactions in a stored procedure, and also tried to set the isolation levels READ_UNCOMMITTED, SERIALIZABLE, and SNAPSHOT. Nothing seems to work. I am upset about this from the last day. Any help is much appreciated.

Thanks Manoj

Answer 1

You might think of dead ends. This usually happens when multiple users use the stored procedure at the same time. To avoid deadlocks and ensure that each request from the user is successful, you will need to do some processing during update failures, and for this you will need a catch attempt. This only works on Sql Server 2005,2008.

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

Answer 2

, , ...

:

BEGIN TRANSACTION . , , Transact-SQL , COMMIT TRANSACTION, ROLLBACK TRANSACTION. , .

, 3pc . , db ( SP, ..), , .

, : http://omaralzabir.com/linq_to_sql_solve_transaction_deadlock_and_query_timeout_problem_using_uncommitted_reads/

Answer 3

. , , , .

:

• 1)
  - 1205
  ": Msg 1205, 13, 50, 1
  ( 5?) . . "?
• 2)
  -
  "- . , , ".

: " 5-6 ", , 2), 1) 5 .

, . , COM +, tx iso, , ().

" , READ_UNCOMMITTED, SERIALIZABLE SNAPSHOT. "

, "", SQL Server .

, - .

CL-IX ( ) , DML-ed (, , ). DML CL-IX ( ) ( CL-IX)

, () , -.

" , SQL Server" (: ., TOP ROWCOUNT , )

In SQL Server 2005 and later, consider READ COMMITTED SNAPSHOT tx iso level, which is possible whenever READ COMMITTED is used by default.

Answer 4

How long does this process take? Perhaps you need a serious proc performance tuning if it causes timeouts for other users trying to insert. No proc should block tables for 5-6 minutes.