TIA for your help. I am using an OleDbTransaction in `
ASP.net`, Page Language="vb", the database is SQL Server 2008 R2 Express. I get a "query timeout expired", and the transaction is rolled back, then the webpage user has to try again, and usually it succeeds. Is it a race condition that is causing this? Inside the transaction there are Inserts on 12+ tables, Updates on 3+ tables, and 7+ Selects. The Selects are generally used to get the IDENTITY ID of the Insert that just happened. The query timeout always happens on one specific Insert in the middle of the transaction. The table is one of our busiest. The time taken up until that Insert is generally 1/100th of a second. Does an insert inside of an OleDbTransaction place a lock on the last page of the table? So that another user trying to insert will get the timeout? It is possible that two different users are getting the same PublicID (see below code) and trying to use it. There is a UNIQUE index on tblContracts.PublicID. Is it something to do with my insert being too slow because of my indexes being wrong for that table? More detail. The series of events looks something like Sub ConvertAppCall(ByRef udtErrorReport As ErrorReport) Dim conDB As OleDbConnection Dim traDB As OleDbTransaction Dim cmdDB As OleDbCommand Dim prmDB As OleDbParameter Dim drdDB As OleDbDataReader conDB = New OleDbConnection(ConfigurationSettings.AppSettings("MDB")) conDB.Open() traDB = conDB.BeginTransaction() Try ...inserts/selects... then the statements with a ** are inside a utilities file, having passed the OleDbConnection and OleDbTransaction **SELECT TOP 1 PublicId FROM tblContracts ORDER BY PublicId DESC **then use OleDbDataReader to read the PublicID and add 1 to it, then **UPDATE [tblLasts] SET LastPublicId = " & lngPublicId then back on the main page, immediately do this INSERT INTO [tblContracts] (using the lngPublicID) cmdDB = New OleDbCommand(strSQL, conDB) prmDB = New OleDbParameter prmDB.ParameterName = "ContractNotes" prmDB.Value = IIf(txtContractNotes.Text.Length > 0, txtContractNotes.Text, System.DBNull.Value) prmDB.DbType = System.Data.DbType.[String] cmdDB.Parameters.Add(prmDB) cmdDB.Transaction = traDB cmdDB.ExecuteNonQuery and if any query timeout happens, it happens here
Any reason you are not using the whole business logic in a single OR multiple stored proc?...The advantages of using stored procedures are quite a few... a, Less network badnwidth would be used b, You would be able to use SCOPE_IDENTITY() to see the last identity inserted values for that specific session without using SELECT TOP 1 ORDER BY ID DESC...That way no other user would be able to share any unwanted identity value....It will also be more optimized as less table reads would be required c, Code reusability d, No need to rebuild the web pages for the DB code change e, Better debugging options like a single query execution plan BTW, a workaround possible is to increase the timout period which I think by default is 15 sec.....but this is not recommended as it is enough for a good application and could lead to excessive locking and blocking...the work needs to be done on the root cause which might be slowness of the queries.
If OledbConnection works like SqlConnection, I don't think you're enlisting the second command into the transaction correctly. It's therefore a separate session on the server and is blocked by the locks from the first transaction that hasn't committed yet. For that second command, instead of cmdDB = New OleDbCommand(strSQL, conDB) I think you need to say cmdDB = New OleDbCommand(strSQL, traDB.Connection).