asked
query timeout expired problem
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
are you managing IDENTITY values with the code rather than an a column with an IDENTITY property and SCOPE_IDENTITY()? is INSERT INTO [tblContracts] the last statement in the series (insert number 12)? None of the other 12 inserts or 3 updates are on tblContracts? -- just reread the post and see that this is in the middle of the transaction. Are any inserts/updates further downstream hitting tblContracts or tblLasts (or other tables that may not have been mentioned yet)?
my tables have an ID column with the IDENTITY property. This column, PublicID does not have the IDENTITY property, for that table the column ContractID does have the IDENTITY property. So for PublicID, I guess I manage the identity values with code. INSERT INTO [tblContracts] is in the middle of the transaction, more inserts/selects/updates follow it. The other inserts/selects/updates that I don't mention are hitting other tables, other than tblLasts and tblContracts.
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.
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).
15 People are following this question.
