Explain the connection pool and transactions in EF and SQL Server - why is it written in the links?

Question

Transact SQL

Here https://coderwall.com/p/jnniww/why-you-shouldn-tu... they write:

Here we go, since DbContext use the same connection string , a connection we get is the same connection returned from connection pool, therefore we run into the ambient transaction and see partial data which has not been fixed in database yet.

Here https://stackoverflow.com/questions/21202982/one-t... they write:

No, this was a coincidence because the 2nd context reused the connection of the 1st from the connection pool. This is not guaranteed and will break under load.

As I understand it, in both sources, the chelas mean that the connections to the sql server are stored in the connection pool. And it says that if I create a new DBContext in the Entity Framework, then dispose it and create a new new DBContext again, then due to the fact that both DBContexts will have the same connection string (I have only one DB in my application , so there is only one connection string in the config), then it turns out that after the first DBContext is closed, the real connection with the sql server will not break, but will remain hanging in the connection pool (and why?)?
According to the first link, it is generally written that due to the fact that 2 different DBContexts (from different threads) will have a common connection string, then the same connection will come to them from the connection pool, so no matter what isolation levels they will run requests in both DBContext , sql server will treat requests from both DBContext as one transaction (even though both DBContext are called from different threads independently). But this is nonsense, because then there would be no point in using transactions in multi-user systems, because the database connection string is in the config and is always common for all DBContext , and for different users of the system in different threads, different DBContext are launched, which must execute requests in their own transactions, but due to the fact that the connection string is shared, it will turn out,
On the first link, they write further

you're right, this doesn't work if you use one DbContext instance for multiple threads.

I never run into the problem that the connection pool was an issue. Every DbContext instance is associated with a single connection (standard settings). No matter what you do, the DbContext will reset the connection on dispose so that it can be used again by other consumers.

that is, here people mean that if the DbContext is created anew, then it should not fumble with a common connection with the sql server. But the previous chelas explicitly write that if the connection string is shared, then the connection from the pool will be shared, i.e. even if you create a new DbContext, it must use the old connection, which will lead to problems with transactions.
In general, someone explain it in a normal way. What did those chelas on the links mean (maybe I misunderstood something there). And in general, is there a danger that two different new DbContext , each of which will be in different transactions and at the same time both have the same connection string, will be executed in the same connection to the sql server and cause bugs with transactions?

Answer 1

It is very easy to check - run SQL Server Profiler and see the SPID under which requests come to the server. If SPID are different then the sessions are different.

Answer 2

If SPID are different then the sessions are different.

Sessions are different - it means that the connections are also different, the session is one - the connection is one, right?
I was given a program on EntityFramework, inside it a new DbContext is created several times in a row. Each DbContext is wrapped in its own TransactionScope. Because all DbContexts represent one logical. operation, now it is implemented clumsily, and I need to remove all individual transactions from each DbContext and wrap them all in 1 transaction. In order not to redo a lot, I have so far decided not to replace the creation of several DbContexts with 1, but simply wrapped all DbContexts inside one TransactionScope. I deleted its own transaction from the first DbContext, I forgot to delete it from the second one (it turned out that I had 2 transactions in the program - external and nested). This is the abbreviated code:

using (var transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions {IsolationLevel = IsolationLevel.Snapshot})) 
{
   var data1Id;
   using (var dbContext = new DB()) 
   {
     var data1= new Data1();
     dbContext.Table1.Add(data1);
     /*1*/dbContext.SaveChanges();
     data1Id = data1.Id;
   }
   if (data1Id != 0)
   {
    using (var transactionScope2 = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions {IsolationLevel = IsolationLevel.Snapshot})) 
    {
      using (var dbContext = new DB()) 
      {
        dbContext.Table2.Add(new Data2() { Data1Id = data1Id, ... });
         /*2*/dbContext.SaveChanges();
        transactionScope2.Complete();
      }
    }
   }
   transactionScope.Complete();
}

I launched the program, the first DbContext executed queries inside the outer transaction (an insert occurred, but so far without a commit), the second DbContext started executing queries inside its nested transaction. As a result, the second DbContext stood in debugging for half a minute, and then something like "waiting for the end of the operation has expired" was thrown, all transactions were rolled back. I looked in SQL Server Profiler - queries from different DbContext had different SPID. As a result, I made myself such a conclusion - because. SPIDs are different, so the connections were different, which means that the sql server perceived requests from different DbContext as parallel (each in its own transactions), although according to the logic of the program, these should have been interdependent sequential requests within one transaction. As a result, a deadlock occurred (by the way, I did not understand why, because
It turns out that the transaction was rolled back because 2 different DbContexts had different transactions and different sessions (connections). If there was 1 common DbContext (respectively, 1 common connection), then the transaction would have been completed successfully, as I understand it.
I started googling and found those links. If I understand them correctly, they say that it is not a fact that 2 different DbContexts will have different connections, because "they share a common connection string, and therefore the connection for the second DbContext can be the same as for the first DbContext, because the connection of the first DbContext will remain in the connection pool even after the first DbContext is closed." That's what got me hooked and made me ask a question here. Because it turns out that if I have a multi-user system and different controllers process independent requests from different users in different threads, and each controller, of course, creates its own new DbContext , then even if I wrap all the code in a transaction, then the line connection to the database is the same for all DbContext, which means that they can use a single connection from the connection pool, which means that the PSID will be the same, and the sql server will execute them in a common transaction, and not in individual ones. But the same cannot be, otherwise there would simply be no point in opening transactions.
Can anyone explain this?
Another thing that I did not understand:
1) in the code above, after passing the point /*1*/dbContext.SaveChanges(); the profiler writes "BEGIN TRANSACTION" and PSID = 53 (everything seems to be correct, the transaction started during the first insert), and at the point /*2*/dbContext.SaveChanges();the profiler writes "BEGIN TRANSACTION" and PSID = 59 (but I didn't understand here, the transaction of the second DbContext is nested, but only one "BEGIN TRANSACTION" is opened in a new session with PSID = 59. This means that the first (external) transaction is lost for all subsequent open sessions after the first session "captured" this transaction during the execution of the insert, despite the fact that all subsequent sessions in the code are opened inside this outer transaction? Or how? This means that the second session was executed only inside one transaction , although in the code it is nested in 2 transactions?).
2)the isolation level is set to snaphot, but it doesn't show up in the profiler. The profiler only shows the line "BEGIN TRANSACTION". I want to make sure that the isolation level is correctly passed to the sql server, but I can't, because I don't see it in the profiler. It is possible that more checkboxes should be enabled in the "selection of events" tab, but I cannot find by the name of the events which checkbox should be enabled for this. In general, tell me what to do so that the profiler shows the isolation level.