Large transactions causing "System.Data.SqlClient.SqlException: Timeout expired" error?
- by Michael
My application requires a user to log in and allows them to edit a list of things. However, it seems that if the same user always logs in and out and edits the list, this user will run into a "System.Data.SqlClient.SqlException: Timeout expired." error. I've read comments about increasing the timeout period but I've also read a comment about it possibly caused by uncommitted transactions. And I do have one going in the application.
I'll provide the code I'm working with and there is an IF statement in there that I was a little iffy about but it seemed like a reasonable thing to do.
I'll just go over what's going on here, there is a list of objects to update or add into the database. New objects created in the application are given an ID of 0 while existing objects have their own ID's generated from the DB. If the user chooses to delete some objects, their IDs are stored in a separate list of Integers. Once the user is ready to save their changes, the two lists are passed into this method. By use of the IF statement, objects with ID of 0 are added (using the Add stored procedure) and those objects with non-zero IDs are updated (using the Update stored procedure). After all this, a FOR loop goes through all the integers in the "removal" list and uses the Delete stored procedure to remove them. A transaction is used for all this.
Public Shared Sub UpdateSomethings(ByVal SomethingList As List(Of Something), ByVal RemovalList As List(Of Integer))
Using DBConnection As New SqlConnection(conn)
DBConnection.Open()
Dim MyTransaction As SqlTransaction
MyTransaction = DBConnection.BeginTransaction()
Try
For Each SomethingItem As Something In SomethingList
Using MyCommand As New SqlCommand()
MyCommand.Connection = DBConnection
If SomethingItem.ID > 0 Then
MyCommand.CommandText = "UpdateSomething"
Else
MyCommand.CommandText = "AddSomething"
End If
MyCommand.Transaction = MyTransaction
MyCommand.CommandType = CommandType.StoredProcedure
With MyCommand.Parameters
If MyCommand.CommandText = "UpdateSomething" Then
.Add("@id", SqlDbType.Int).Value = SomethingItem.ID
End If
.Add("@stuff", SqlDbType.Varchar).Value = SomethingItem.Stuff
End With
MyCommand.ExecuteNonQuery()
End Using
Next
For Each ID As Integer In RemovalList
Using MyCommand As New SqlCommand("DeleteSomething", DBConnection)
MyCommand.Transaction = MyTransaction
MyCommand.CommandType = CommandType.StoredProcedure
With MyCommand.Parameters
.Add("@id", SqlDbType.Int).Value = ID
End With
MyCommand.ExecuteNonQuery()
End Using
Next
MyTransaction.Commit()
Catch ex As Exception
MyTransaction.Rollback()
'Exception handling goes here
End Try
End Using
End Sub
There are three stored procedures used here as well as some looping so I can see how something can be holding everything up if the list is large enough. Other users can log in to the system at the same time just fine though.
I'm using Visual Studio 2008 to debug and am using SQL Server 2000 for the DB.
