This is our template (error logging removed) This is designed to handle Paul Randal’s article “No such thing as a nested transaction in SQL Server” Error 266 Trigger Rollbacks Explanations: all TXN begin and commit/rollbacks must be paired so that @@TRANCOUNT is the same on entry and exit mismatches of @@TRANCOUNT cause error 266 because … Read more
It can’t work that way. Consider: program one, you open a transaction and insert into a table FOO which has an autoinc primary key (arbitrarily, we say it gets 557 for its key value). Program two starts, it opens a transaction and inserts into table FOO getting 558. Program two inserts into table BAR which … Read more
There are 2 main kinds of transactions; connection transactions and ambient transactions. A connection transaction (such as SqlTransaction) is tied directly to the db connection (such as SqlConnection), which means that you have to keep passing the connection around – OK in some cases, but doesn’t allow “create/use/release” usage, and doesn’t allow cross-db work. An … Read more
After much pulling out of hair I discovered that the foreach loops were the culprits. What needs to happen is to call EF but return it into an IList<T> of that target type then loop on the IList<T>. Example: IList<Client> clientList = from a in _dbFeed.Client.Include(“Auto”) select a; foreach (RivWorks.Model.NegotiationAutos.Client client in clientList) { var … Read more
What gives this away is the word transaction. It is evident by the statement that the query was attempting to change at least one row in one or more InnoDB tables. Since you know the query, all the tables being accessed are candidates for being the culprit. From there, you should be able to run … Read more
The answer your question is no – @Transactional will have no effect if used to annotate private methods. The proxy generator will ignore them. This is documented in Spring Manual chapter 10.5.6: Method visibility and @Transactional When using proxies, you should apply the @Transactional annotation only to methods with public visibility. If you do annotate … Read more
With the Entity Framework most of the time SaveChanges() is sufficient. This creates a transaction, or enlists in any ambient transaction, and does all the necessary work in that transaction. Sometimes though the SaveChanges(false) + AcceptAllChanges() pairing is useful. The most useful place for this is in situations where you want to do a distributed … Read more
I think transactions belong on the service layer. It’s the one that knows about units of work and use cases. It’s the right answer if you have several DAOs injected into a service that need to work together in a single transaction.
SQL Server 2008 can use multiple SQLConnections in one TransactionScope without escalating, provided the connections are not open at the same time, which would result in multiple “physical” TCP connections and thus require escalation. I see some of your developers have SQL Server 2005 and others have SQL Server 2008. Are you sure you have … Read more
The idea I generally use when working with transactions looks like this (semi-pseudo-code): try { // First of all, let’s begin a transaction $db->beginTransaction(); // A set of queries; if one fails, an exception should be thrown $db->query(‘first query’); $db->query(‘second query’); $db->query(‘third query’); // If we arrive here, it means that no exception was thrown … Read more