This is an automated archive.
The original was posted on /r/mysql by /u/i_am_not_you_or_me on 2023-08-04 16:05:13+00:00.
Hello All,
New to MySql in charge of migrating a complete c#.net based system from Oracle to MySql. We’re not using EF.
One of the largest changes I’ve noticed is that if an OracleCommand object fails (throws an exception) nothing is commited, which does not seem to be the case using the MySqlConnector. I’ve also read that nested transcations do not work in mysql. So I’m curious as to the best way to implement transactions.
- START TRANSACTION/COMMIT inside the stored procs does not seem to be an ideal solution as many procs call other procs and I’d very quickly run into the issue of nested transactions.
a) If an transaction is not explicitly commited it will be rolled back right? eg an exception or error is thrown within a START TRANSACTION before the commit and no rollback is called.
b) Is there a way inside the stored proc to check if it’s inside a transaction already? I could code around the nested transcations that way.
-
set autocommit=0 inside stored all procs and just be sure the ‘main’ procs have a COMMIT? Seems like this would avoid nested transactions?
-
Handle it all in the app DataAccess layer? explicitly use MySqlConnector transactions on all proc calls