June 23, 2010 at 8:11 pm
Hope I got the right forum for this issue ..
We have a VB.Net application (visual Studio 2008) that updates several tables in SQL 2005 database (updating single database).
The application is multi-threaded. We ensured that all code is thread safe.
In all cases where there are several tables that need to be updated we use sqlTransactions.
Table updates are done using sqlcommand.ExecuteNonQuery()
Sometimes, some tables get updated and some do not.
Eg.
BeginTransaction
update table 1 (using try/catch to trap for exceptions)
if exception was thrown then rollback and exit
update table 2 (using try/catch to trap for exceptions)
if exception was thrown then rollback and exit
update table 3 (using try/catch to trap for exceptions)
if exception was thrown then rollback and exit
update table 4 (using try/catch to trap for exceptions)
if exception was thrown then rollback and exit
Commit transaction
At times, table 1 and 2 do not updated , there is no exception thrown (so the code continues) and as a result table 3 and table 4 get updated.
This throws the database integrity out of whack.
The database server can get busy and may possibly time-out but it should still throw and exception.
Any insight would be greatly appreciated.... this is driving me crazy by now.
July 3, 2010 at 7:24 am
Hi Derek
There must be some issue with your exception handling. SQL Server transactions and .NET exceptions work correct. Try to remove all your custom exception handling and have a look for thrown exceptions.
BTW: Keep in mind, if you do not explicitly commit a .NET SqlTransaction, it becomes automatically (silently) rolled back as soon as the transaction becomes disposed (e.g. when leaving a Using-block)
Greets
Flo
July 6, 2010 at 6:09 am
Hi derek,
a nice way of find out what is happening is to put a try catch block around the code and create a table to store the expection information
so you'd create a table called ErrorTable with columns to store the info from the select in the begin catch section below (dont think I need to script this for you?)
then change your code as below
begin try
begin tran
<your code>
commit tran
end try
begin catch
rollback tran
insert into ErrorTable
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
end catch
then you can simply query this errortable and see what happend. Be aware though, anything with an error severity of 10 or below wont register be caught as an error here by default
Hope this helps
July 6, 2010 at 1:43 pm
when you say "not updated" ... do you mean as in:
update table
set [field] = 'value'
where 1 = 0
type of not updated?
update stuff
if @@rowcount < 1 raiserror('oh no', 16, 1)
July 24, 2010 at 2:00 pm
Thanks for all your replies.
The application is using a single function that does all the updates.
This function returns FALSE if an exception was thrown.
In addition, I have a class 'mthdResults' that is simply used to store results of updates.
Dbcommand commad text is set to the sql command to update each table.
Eg:
public function updateDb (byref dbCommand as SqlClient.SqlCommand) As Boolean
Try
mthdResults.recordsUpdated = dbcommand.ExecuteNonQuery()
Return True
Catch ex As SqlClient.SqlException
mthdResults.sqlError = True
mthdResults.sqlErrorMsg = ex.Message
mthdResults.sqlErrorNo = ex.Number
Return False
Catch ex2 As Exception
mthdResults.sqlError = True
mthdResults.sqlErrorMsg = ex2.Message
Return False
End Try
End Function
Every method that calls this function checks the following:
If FALSE was returned then there was an exception so ROLLBACK previous updates, report exception and exit.
I also check if there were any records updated. This is to double check that an update was ok.
If there were no records updated (ie. mthdResults.recordsUpdated = 0 ) and there should have been records updated then I also issue a ROLLBACK and inform the user of the situation.
If all updates were OK (ie. no exceptions and records were updated where the should be records updated) then I do a COMMIT.
The COMMIT is also in a try/catch to ensure that the transaction is still active.
In some situations, I get No Exceptions end everything looks good,
but the FIRST 2 or 3 tables (there are 8 tables that need to be updated together to keep database integrity in check) do not get updated.
The remaining tables do get updated since the appllcation continues with the updates as there were no issues with the previous updates.
I found out that this only happens if the server is very busy and it looks like it may be running out of resources.
The application that is updating the database is running as a Windows Service.
At this point I don't know if it's something to do with the Windows Service not intracting properly with the applicaiton (ie. reporting Exceptions ... although I can't see how that could be possible) or if there is an issue with .Net interaction with SQL Server.
Out of 1000 times when the applicaiton updates the database like this (ie. this transaction where the 8 tables need to get updated), there may be 1 time where this problem occurs.
I have had few people go through my code and none of them can see anything wrong with the application.
At this point, I have decided to change updates where I use sql transactions via sqlCommand(ie. few tables need to updated at the same time) and rewrite these updates as SQL Stored Procedures.
I'm obviously going to use transactions but they will be inside the stored procedure.
At least this way I should be guranteed that this works as there will be no intraction between the sql transaction and the .Net application while the transaction is being processed.
I've bought few brick-sized books on T-SQL as I never wrote any stored procedures in the past.
I wrote a stored procedure that does one of the largest updates (8 tables in total).
This turned out to be one LARGE stored procedure.
I would like to break it down to smaller pieces but I need to find out how does the transaction behave if I do the following in my Main Stored procedure ( this one is called by my applicaiton) :
Main stored procedure and accept parameters passed in by my applicaiton
Start a transaction
execute a procedure1 that updates table1
Check if this procedure returned with error (if so the rollback and exit)
execute a procedure2 that updates table2
Check if this procedure returned with error (if so the rollback and exit)
execute a procedure3 that updates table3
Check if this procedure returned with error (if so the rollback and exit)
Commit transaction
exit main stored procedure
Question: When I execute procedure1, procedure2 etc... do these use the transaction started in the main stored procedure *ie. the procedure that called them ?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply