C#, Mysql Transaction, and unexpected shutdown/crash?

[Red Squirrel]

I am using a Sql transaction in a program to ensure that if the program crashes half way through a set of queries, it gets rolled back, but I'mthinking maybe transactions don't do what I think they do. Here is example code:

		private static bool SqlSnapshot()
		{
			bool res=true;
		  
			m_connector.Query("START TRANSACTION;");
			
			for(int i=0;i<SerializeObjectTableNum;i++)
			{
				//delete anything with pending status 2:				
				res = m_connector.Query(String.Format("DELETE FROM serializeobjects{0} WHERE o_pendingstatus=2;",i));				
				if(!res)break;
				
				//update anything with pending status 1
				res = m_connector.Query(String.Format("UPDATE serializeobjects{0} set o_data=o_newdata, o_newdata='', o_pendingstatus='0' WHERE o_pendingstatus=1;",i));
				if(!res)break;
				
				//debug - test crash!  
				int a = 0;
				int b = 5/a;
			}
			
			
			if(!res)m_connector.Query("ROLLBACK;");
			else m_connector.Query("COMMIT;");
		
			return res;
		}

This basically performs some queries on 4 identical tables so it just loops through. Idealy if the program crashes for any reason in the middle of that loop, I want to make sure none of the changes actually take effect. However, when I run this code, with the purposeful crash after the first loop, the queries end up having been committed to the first table.

Is this normal behavior when dealing with transactions, or is there something wrong?

Suppose worse case scenario I can set a flag somewhere when it starts, and then set it back off when it ends, and then when the program loads if the flag is set it means it did not save correctly and it would either raise a warning, or simply halt startup requiring to restore from backup. 99% of the time this won't cause an issue though, so maybe I should just leave it alone, but ideally it's always better safe than sorry I suppose.

 

[Flayed]

bool res=true; does it work if you change that to false?

 

[Red Squirrel]

Yeah same result. It works as desired as far as rest of program goes, like it detects there is an error etc, but it's like the transaction is not actually happening. Never really worked with transactions before so wondering if I'm doing something wrong or expecting too much of the feature? Ex: does it only work if it's all within a single query statement? Maybe I'm not using it right?

 

[nakedfrog]

Yes, transactions work they way you think, but you probably need to implement it differently (m_connector.BeginTransaction or something like that versus in-line statement).

 

[Flayed]

I don't know much about it but I would expect to use a try catch block with the transactions happening in the try block with commit at the end of the block and the rollback happening in the catch block for when an exception has been caught or something similar to that.

 

[Red Squirrel]

Didn't think of trying built in transaction commands though turns out I'm using MyISAM and not InnoDB and it looks like MyISAM does not support transactions. If I recall the reason I chose MyISAM was that the performance was better for the type of data I'm dealing with (it's not really optimized for a DB so the queries are basically text blobs of data). So think I will stick with that and simply look for a work around in case it does crash mid loop while running those commands. The odds of a crash/issue in middle of that function is slim, so I have a few ideas how to deal with it such as setting a flag before and after. Oddly enough it ran more or less fine for years as far as I knew and I was just revisiting and optimizing the code then realized it was not actually working as intended.

 

[Cogman]

If you want a "fake" transaction, you can always try versioning + active (which is basically what transactions are doing in modern dbs). That is, write out the new version of the data, with active = 0 and the new version number. if that completes successfully, set active = 1. If that completes successfully, either inactivate the old version, or delete it out (where version = old version).

When selecting things out, select out latest version where active = 1.

Of course, lots to get wrong here and easy to not do the right thing, but a thought if you want to do it without native transactions.

Otherwise, yeah, a different storage engine is definitely needed.

 

[Red Squirrel]

Yeah that's what I was thinking, might do something like that. That is sorta what is happening now with the snapshots, the data is always being updated but because there is often a queue of data and continuous changes, the data set is never considered "atomic". But when it hits a point where it is, a snapshot is issued, which involves copying the data over to the "current" field and setting flag back to 0. There is also a flag for delete, which only happens during snapshot. Only thing is there are 4 tables so if something happens half way through that's where there can be issues. So what I just did now is added a flag (in a separate table that is used to track misc info) that is set before and after a snapshot. It will just issue a startup warning/error if the flag is set when it should not be. This won't exactly fix anything but it will prevent potential inconsistent data. The snapshots take like 6 seconds to do and happen every 5 minutes so I figure the odds of a crash at that moment are slim enough that this is good enough I think.

I may look at just combining the 4 tables together though then the snapshot is only 2 queries and may also be even faster. I can just have another field to determine what container it is within the game, but as far as SQL is concerned it will just be one table.

 

[urvile]

Personally I would use an ORM like entity framework. It's been designed with the Unit of Work Pattern. Which is good for transactions. Without directly messing around with transactions.

Sorry. MySQL I should read more closely. Although you may be able to use EF with MySQL? It's all about making your life easier.

 

[Red Squirrel]

I ended up just implementing a flag that is set to true before the critical set of queries and then set back to false after. It's checked at startup, if it's set to true then it shows a warning that the data may not be consistent with option to load anyway. In production I would restore a backup if this happened, to play things safe. In dev/test it's not a big deal. Maybe not the BEST approach, but it's acceptable for this case as the chance of an issue during that critical time (around 10 seconds per 5 minute period of run time) is slim enough. Of course if I have an issue with constant crashes that are happening often enough to hit that critical point I would be prioritized at finding out why they are happening so I can solve that issue.

 

[sao123]

I'm not sure why you are trying to do the logic in c# with micro transactions.
With ado.net support of mysql, you should be able to create a full SQL statement and process it all at once using this template.

BEGIN TRY
   BEGIN TRANSACTION
       -- Do your stuff that might fail here
   COMMIT
END TRY
BEGIN CATCH
   IF @@TRANCOUNT > 0
       ROLLBACK TRAN

       DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
       DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
       DECLARE @ErrorState INT = ERROR_STATE()

   -- Use RAISERROR inside the CATCH block to return error
   -- information about the original error that caused
   -- execution to jump to the CATCH block.
   RAISERROR (@ErrorMessage, -- Message text.
              @ErrorSeverity, -- Severity.
              @ErrorState -- State.
              );
END CATCH

 

[mxnerd]

I'm not sure why you are trying to do the logic in c# with micro transactions.

OP is using MySQL with MyISAM, which does not support transaction according to his other thread.

 

[Red Squirrel]

I usually prefer to keep logic in code. Databases are for storage. I *could* try to switch to InnoDB and then try transactions again, but I think the switch to MyISAM was a performance decision, I don't actually remember 100%. I'm happy with where I'm at now anyway. There's some rooms for tweaking but I realized in the end I don't really NEED the transactions.

 

[Merad]

I usually prefer to keep logic in code. Databases are for storage.

Have we worked together? I had a former co worker who liked to say the same thing, and took it so far that if left to his own devices he wouldn't even put foreign keys in his DBs.

 

[mxnerd]

Have we worked together? I had a former co worker who liked to say the same thing, and took it so far that if left to his own devices he wouldn't even put foreign keys in his DBs.

How did he link records in different tables then?

 

[Merad]

How did he link records in different tables then?

Poor phrasing. Obviously he had to have "foreign key" columns, but he wouldn't actually declare a foreign key constraint on them. In his mind enforcing referential integrity was a business rule that belonged in the application.

 

[cytg111]

Ooof Ive been in this boat too... IIRC mysql lets you create the tables with foreign keys as well, and then just ignores them at run time.. Mayb isam works different today but then it just failed silently.. remember it was one reason to ditch mysql completely and never turn from postgres again... Too many silent failures in mysql.