Why NH encourage user to using transaction

[robert-bitguild]

I have a story that my friend said NH encourage using transaction. and list his point in that NH official document

https://hibernatingrhinos.com/products/nhprof/learn/#DoNotUseImplicitTransactions
A common mistake when using a database is to use transactions only when orchestrating several write statements. In reality, every operation that the database is doing is done inside a transaction, including queries and writes (update, insert, delete).

On his website which has more than 1,000,000 access per day. In his code, he created a new transaction at the start of every APIs and commit it in the outlet of API. It is a tragedy that website only support a few concurrences.

So, I don't understand, Why NH encourage users to use transaction?

Thanks

[gliljas]

That is not an "NH official document". That is the web page of a third party product.

[oskarb]

So, I don't understand, Why NH encourage users to use transaction?

I think that article does a fairly good job of explaining why you should use transactions and that in fact you are anyway using transactions even if you don't create them yourself. Not creating the transaction yourself generally leads to MORE transactions, so combining all work for a batch in a single transaction generally gives better performance and more robust logic, both for reading and writing.

This is therefore the safer choice generally.

It is a tragedy that website only support a few concurrences.

If you feel that your website is slow because of transactions I suspect you have some other problem. You can have thousands of transactions reading the same data concurrently. They would only be blocked by writers. And only if the same data is affected. If you have hundreds of transactions trying to READ some data that is also being written, why do you want the readers to possibly get partially written data? And btw if a long-running transaction has taken a write-lock on some data, a SELECT statement for that data might be blocked even if you didn't create a transaction yourself for the read call.

Some things to consider:

• Transaction isolation level. Default one in MS SQL Server means a reading transaction will be blocked if another transaction has taken a write lock on some data. Consider using READ_COMMITTED_SNAPSHOT instead, so that readers will read the previously committed instead of waiting for the writing transaction to finish.

• Keep transactions (especially writing ones) short duration. If they must be long-lived, using READ_COMMITTED_SNAPSHOT is even more important.

• Analyse what the real reason for poor concurrency comes from. It is not from using transactions. Instead, try to find what common data they are all trying to get exclusive access to and reconsider that design. Look for excessive exclusive locks.

[robert-bitguild]

I don’t agree your some options. Actually, the suggestion ask user using explicit transaction to replace implicit transaction. For my understand is it strong recommend user to create a explicit transaction to all situation(my friends also thought it is). We all knowns transaction can blocks other transaction that those transactions operate same table rows. A transaction must submit as soon as possible. Don’t need transaction if your business didn’t require it.
But from the document, totally recommend using explicit transaction, even include query operation.

For example, one API have the case
Update user A. (0.1s) Do logic(0.3s) insert order (0.1s) do other logic(0.1s), query order and other operation(0.3s)

My friends follow the suggestion and create a transaction in begin and commit it in end.

In the case. I want to ask you how much concurrence the API have?

Don’t tell me why frequently update user A. My friends have a lot of these kind of cases when I checked their code.

I know transaction may has better performance for just single execution. But most of website have concurrence. The recommend is very seriously to delivery bad solution for these developers who are just junior. Even some senior developers who don’t very familiar with database.

[oskarb]

The recommend is very seriously to delivery bad solution for these developers who are just junior

The recommendation is a safe option for "junior" developers. Usually, it is better that your web site produces the correct result slowly, instead of the wrong result fast - or, as is likely, works correct when tested but then produces incorrect result in production with more concurrent users. Certainly, a particular use case where you want to do things differently does not invalidate the general recommendation.

I have many times suffered the implications of interacting with a third-party application where the developers behind it didn't know about the concept of transactions or thought they didn't need it because "transactions are only for the big guys doing banking software". It inevitably leads to an API or user interface where you call a method and it fails with an error message, but guess what - it saved "half" of the work in the database so now you as the caller are left with trying to figure out what got saved and what you need to try again. So always using explicit transactions around the unit of work is the safer choice for robustness and correctness.

Someone that takes the time to study the concept carefully will be able to make a different choice in their application for scenarios where there is a significant benefit.

I think you will find that if you have MANY callers that all want to change THE SAME database row, you are likely to get poor concurrency (or incorrect results) even without explicit transactions. At least with common good quality database systems, the locks will be per row. So different transactions updating different rows will typically not have to wait on each other. It does certainly depend on specifics. (For example if you query for all rows but you really only wanted one or a few of them, that could lead to excessive locking.)

In any case - if the responsible developer carefully analyses the particular use case and convinces themselves that they can somehow implement the specific use case safely (i.e. no chance of data corruption or otherwise producing incorrect results, not even in corner cases thought to be "unlikely to ever happen"), they can certainly try to skip the transaction. If their analysis is wrong, whoever is the end user of the application will suffer.

Correctness, robustness and performance are not an easy topic. Removing explicit transactions is really not a good quick fix to try to gain performance, without suffering implications for correctness and robustness.

If there is a use case where for some reasons you have a true need for many concurrent callers to update the some particular database row, you have several options:

• Treat that use case specially. Devote time and effort to improve performance without sacrificing correctness for this particular use case. Continue to use safe defaults for other use cases.

• Isolate the specific update into a separate transaction so that (using your example timings), it takes 0.1s for that particular transaction instead of 0.9s.

• For some scenarios, for example multiple writers that all want to increment some number, to gain high concurrency you can increment it in memory and have a single transaction update the database once per second. This is assuming it's acceptable to miss some updates because of course if the process dies, the last second of updates will be lost. Another option is for each call to write a separate row in a table, which all these transactions can do without interfering with each other. Then a single cleanup transaction can run every few minutes to compute the total.

[robert-bitguild]

The recommendation is a safe option for "junior" developers. Usually, it is better that your web site produces the correct result slowly, instead of the wrong result fast - or, as is likely, works correct when tested but then produces incorrect result in production with more concurrent users. Certainly, a particular use case where you want to do things differently does not invalidate the general recommendation.
The first thing if we don't need transaction, we should not use it. But if we recommend using transaction, of course both produce correct result, not what you said incorrect result. It will produce big issues like what I say.
A common mistake when using a database is to use transactions only when orchestrating several write statements. In reality, every operation that the database is doing is done inside a transaction, including queries and writes (update, insert, delete).
I know execute SQL statement has their own transaction for each sql, But not a transaction. for above statement, it implies my friend it should wrap all sql execute operation to a transaction.

I have many times suffered the implications of interacting with a third-party application where the developers behind it didn't know about the concept of transactions or thought they didn't need it because "transactions are only for the big guys doing banking software". It inevitably leads to an API or user interface where you call a method and it fails with an error message, but guess what - it saved "half" of the work in the database so now you as the caller are left with trying to figure out what got saved and what you need to try again. So always using explicit transactions around the unit of work is the safer choice for robustness and correctness.
It only a little agree the point, not all. My point is:

Number 1. We should not create explicit transaction to wrap multiple SQL executions except to the requirement need to keep data consist in multiple SQL executions

Number 2. We should commit or rollback transaction as soon as possible if we really need transaction.

Number 3. We should not recommend user to using transaction. Of course, we should tell user the cost to use transaction. like it will affect concurrence.

I think you will find that if you have MANY callers that all want to change THE SAME database row, you are likely to get poor concurrency (or incorrect results) even without explicit transactions. At least with common good quality database systems, the locks will be per row. So different transactions updating different rows will typically not have to wait on each other. It does certainly depend on specifics. (For example, if you query for all rows but you really only wanted one or a few of them, that could lead to excessive locking.)
I know you tell me the code is not strong because the developer should think it. But these junior developers don't know it. As I made example from their code, it actually is update one same row. other operation not update one row, but operation update some table index. It also caused lock or block. Too much factor will affect database block. If we strongly recommend user to create an explicit transaction is better than implicit transaction without other condition. It like my friend's action, they create a transaction in begin of API and commit the transaction in end of API. When all code finished and submit to production. Big issue coming.

The other reply you always stress correctness. But both all correct. What the recommend using transaction is not good solution in both product correct LOGIC result. We should have a balance what situation will use transaction, others not.

My friends are typical a case. They wrote all APIs and surround with transaction. It is fine when they test in local even beginning in customer website (a little users). When much and much user to access APIs, the big problem coming. Block, lock occur everywhere. They spent 1 month to resolve (Because they don’t think transaction impact concurrence), No any result, util found most of API impact by transaction. But it is too late, too much APIs need changing. Too much API need think about does it need keep transaction or other can be safely remove.

Sorry for my writing, I’m not native English. But I strong insist my three point I list above.

You list three options in last I totally agree. actually my discussion focus on "We should not recommend user to use transaction". You added your point. So I do a little revision that
"We should not recommend user to use transaction when both produce correct Logic result"
"We should not recommend user to use transaction when user requirement not require data consistence in multiple operation"

Thanks your standpoint.

[oskarb]

I know you tell me the code is not strong because the developer should think it. But these junior developers don't know it.

And yet you want to ask that these junior developers make an informed choice by themselves if they need transactions or not. Or give them a recommendation that you feel is better for performance, at the cost of greater risk of incorrect results or being less robust.

At least personally I would rather deal with customers/users being annoyed that their system is slow, rather than being angry that the system is producing incorrect results and possibly lost their data.

That using no transactions or "only when really needed" would lead to higher performance in general I think is a dubious claim. I don't know what exactly is going on in the system you are talking about, but I suspect the use of transactions isn't the major issue. And even if it is, that doesn't hold true in general.

As for junior developers.... I think it is better to teach junior developers safe practices and good habits, and they can deviate from them when they know better. Rather than telling them dubious shortcuts and then excepting them to suddenly get it right in the more complex cases.

[oskarb]

Btw, perhaps instead of recommending developers to not use explicit transaction, perhaps an article with recommendations on good usage of transactions would be in order. I.e. keep them short-lived, consider lock-contention, etc.

[fredericDelaporte]

Oskarb has already said most there is to say about it.

Here is an additional NHibernate specific reason: when not using explicit transactions, the second level cache cease to work as soon as some data has been written. (At least for the entities/spaces touched by the data change.) So an application cannot benefit from the second level cache without explicit transactions.

And here is a personal experience case: I had been given a Web site to handle, which was using NHibernate. There was no transaction handling in it. At some point I have put one in place, with a one transaction per request pattern. It had at first a bad impact, because I forgot to check the concurrency level. Once I fixed that (read committed snapshot indeed), there was no more any bad impact. Quite the contrary: it did fix concurrency bugs occurring on some cases, and improved performances because the second level cache (which was in place from the beginning) could then work properly.
Also, for a few selected requests with special needs, I did part away from the "one transaction per request" model, knowing what I was doing for these special cases. But it was still using explicit transactions for all queries even in these few cases. That was meaning many consecutive transactions for a single request, but all still explicit.

Update user A. (0.1s) Do logic(0.3s) insert order (0.1s) do other logic(0.1s), query order and other operation(0.3s)

Are these operations an actual unit of work? Should the user update be done only if the order is guaranteed to also be inserted? If yes, rearrange the code to group together these two writes in a single short explicit transaction without logic in between, instead of locking the user data for half a second, with is a huge duration for a system needing to support concurrency. If no, use distinct explicit transactions, without forgetting to include in them all data reads which would be required by the logic to know what update to do, of course.

By the way, NHibernate documentation is here, and it does advise against not using explicit transactions here:

NHibernate may be used without managing transactions neither with the ITransaction API nor with transaction scopes. We advise against such a pattern, because this causes each single interaction of the session with the database to be a transaction on its own. This causes overhead, breaks the unit of work in case of errors during flushing, and causes some features to be disabled like auto-flush and second level cache. If the application encounters locks contention or deadlocks, investigate lowering the isolation level (like enabling read committed snapshot with SQL Server) or shortening your transactions rather than removing them.

[robert-bitguild]

The disadvantage of using transaction

• 1. Reduce concurrence
• 2. It may cause block/lock

Advantage of using transaction

• 1. Keep data consistency among multiple operations
• 2. Reduce the code complexity

We should know the advantage and disadvantages of transaction I list above, If you don't agree above, It should be another discussion topic.

So you underestimate the destructiveness to recommend using explicit transaction cross whole application.

The more store:

The developer(architect) who wrote code at BeginRequest to create a new transaction with default isolation level(read_commit)

protected void Application_BeginRequest()
{
   environment.Resolve<ITransactionManager>().RequireNew();
   // ...
}

There are more than 10 developers who wrote more than 500 APIs cross 2 years. everything and code work perfectly local and even in testers who just have the regular test (without pressure test, actually they are not aware that).
The tragedy comes after they published code to the website. The whole website only supports a few concurrences.

I just ask the developer why do you create a new transaction beginning of API? He answered what you said to enable "second cache level" and NH encourages to create a transaction per request.

I made an example before

Update user A. (0.1s) Do logic(0.3s) insert order (0.1s) do other logic(0.1s), query order and other operation(0.3s)
You tell me how to resolve the issue, Of course, I know, But as I mentioned more than 500 APIs have the issues. They need to look for every API.

The second one is I just made a very simple example. The more complex is that APIs impact APIs. even include complex SQL queries.

I dug the "second cache level" in NH, It's great. But I don't think NH should recommend users to use transactions in every request or NH should list the rule of using transactions.

You another suggest changing the transaction isolation level. It is an option to resolve. But the problem is the isolation level set at the global level. It should be a big problem to think about how much API will be impacted.

Btw, perhaps instead of recommending developers to not use explicit transaction, perhaps an article with recommendations on good usage of transactions would be in order. I.e. keep them short-lived, consider lock-contention, etc

@oskarb I agree with what you said. If NH recommends using explicit transaction, you should need to list the rule of using transaction.

[fredericDelaporte]

So you underestimate the destructiveness to recommend using explicit transaction cross whole application.

This is based on false premises, as already stated many time in our answers. Transaction are not an issue per se for concurrency. Only badly used transactions are. Code suffering from the usage of transaction stresses a lack of knowledge about how data access code should be designed.

As already stated by others : have short transaction duration. But also, ensure most transaction are read-only, since read-write transactions allow far less concurrency.
1 second is not short, it is huge. An API call expected to take one second can only be a disaster for concurrency if it is encapsulated in a single read-write transaction.
If all your API calls do "mundane" writes like auditing the request call, consider either not auditing all calls since it kills concurrency, or use a specialized system for that, which will use a non transactional queue mechanism of some sort and a separated process to send all that to the database.

But the problem is the isolation level set at the global level. It should be a big problem to think about how much API will be impacted.

That is a critical settings to handle for application scalability. Of course it should have been done from the very beginning of the application design. But considering it is too late to handle it will forbid actually fixing the application.

[oskarb]

I don't think I can express my thoughts on the technical issues more than I already have, at least I don't have time to.

One other thing that does occur to me.

You mention "10 developers, 500 API calls, 2 year project". You mentioned "junior developers" somewhere also. I assume that everyone involved in the project has been doing their very best, I have no reason to think otherwise. However, this is a huge project.

I don't think blaming/scapegoating some guideline about transactions that someone took from a blog article is very helpful here, to be honest. It would be unfortunate if these developers (and others) come away from this project being told that "transactions are bad for performance, don't use them".

This would certainly not be the first or the last project in the history of IT that was surprised by performance issues late in the project. More useful lessons would be to consider the process that lead to the situation. Should performance tests have been done earlier? Should we and could we have analysed some aspects in more detail? How could we have realised that earlier? Should we have had more in-depth knowledge available in the project team?

[fredericDelaporte]

The one transaction per request pattern is not a "must follow pattern otherwise bad things will happen", it is just a default pattern to follow, usually suitable for most cases. Usually, a http request is something short (below 100ms), doing a single work from a business standpoint. In such case, one transaction per request is the way to go.

But if the http request is more complex and heavy, doing many business works which can be safely done independently, not needing to be "all done or none done", then many transactions have to be used, as many as different works are done by the request. If for some reason such requests still use a single transaction instead of many, the performances trouble caused by that is not to be blamed on the transaction usage, but on the constraint causing the request to use a single transaction instead of the many it should use.

(I can not really answer point to point to you, because what you write is not clear for me.)

[robert-bitguild]

Thanks @fredericDelaporte
Yes, your viewpoint is same with me in "The one transaction per request pattern".

My claim is very clear that because of transaction is impact concurrency. We should not recommend the end user to use explicit transaction in some website, at least the website which need concern concurrency.

I also very clear made my other claim as below. So It is not blaming transaction, just using in correct scenario.

Number 1. We should not create explicit transaction to wrap multiple SQL executions except to the requirement need to keep data consist in multiple SQL executions

So I suggest NHibernate deeply think about to recommend developers which scenario we should explicit transaction(which enable second level cache). Which scenario should not. How to avoid the issues I descripted if developer use explicit transaction.

[fredericDelaporte]

No, we do not agree. Explicit transactions are the way to go in all cases. All I say is, it is usually a good starting point to use a one transaction per request pattern for Web sites and APIs, but specific cases may require to instead use many but still explicit transactions.

I will not advise using implicit transactions with NHibernate whatever the cases. ("Implicit transaction" means not using the transaction API nor transaction scopes.)

[robert-bitguild]

I summarize what you thought
"Using a one transaction per request pattern" reduces concurrency(You agreed with the viewpoint before) but you think it occurs small probability[I don't think it is a small probability personally]. It needs developers to handle themselves. Even NH doesn't need to document it to tell developers, is right?

But anyway, I suggest NH should have a pattern, which developers should do, which developers should not do. How to avoid these issues when using NH

[fredericDelaporte]

I go on disagreeing. That is anyway not NHibernate primary mission to tell developers how they should design their data access layer. We only give general guidance, which is widely approved by developers as a good starting point for most cases. Specific applications may need to use different patterns. And that is each developer responsibility to know whether the general guidance is good for their application cases or not.

We do not forbid not using transaction, we advise against it, and we give reasons for this advice, as you can read in our documentation. You are free to disagree, and do as you want anyway in your code base. But since no contributors among the three (even four but message deleted) of us who have answered here seemed convinced by your viewpoint, we are not going to change our recommendations.

I do not think this discussion is going to progress any further.