Timeline for Is there some fundamental reason that TransactionScope does not work as expected?
Current License: CC BY-SA 4.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jul 4, 2023 at 11:27 | comment | added | Charlieface |
Note by the way my workaround stackoverflow.com/a/25606151/14868997 which is to always pass at least one parameter, so that the isolation level is scoped to the batch only, because it goes via sp_executesql rather than an ad-hoc batch.
|
|
| Jul 4, 2023 at 0:48 | comment | added | Martin Smith |
@DanGuzman - Thanks. Interesting. If I add "Enlist=false" - I see "read committed snapshot" in Azure for all three Console.Write and three different TransactionIds so it no longer seems to be respecting the scope for either transactions or isolation level!
|
|
| Jul 3, 2023 at 23:24 | comment | added | Dan Guzman |
I see only snapshot with both Azure SQL Database and on-prem when I add Enlist=false to the connection string so the behavior difference seems related to distributed transaction support in Azure SQL Database. I suspect Azure SQL Database sp_reset_connection behaves differently when the TDS RESETCONNECTIONSKIPTRAN flag is set on the reused pooled connection due to the transaction scope.
|
|
| Jul 3, 2023 at 23:15 | answer | added | J.D. | timeline score: 2 | |
| Jul 3, 2023 at 18:54 | history | edited | Martin Smith | CC BY-SA 4.0 |
added 27 characters in body
|
| Jul 3, 2023 at 18:39 | comment | added | Martin Smith | @DanGuzman - yes in this case I see it when same session id is used for all three calls | |
| Jul 3, 2023 at 18:37 | history | edited | Martin Smith | CC BY-SA 4.0 |
added 127 characters in body
|
| Jul 3, 2023 at 18:32 | comment | added | Dan Guzman | Connection pooling may also come into play, where a different connection might get acquired. Do you see the same @@SPID if you log the value? | |
| Jul 3, 2023 at 18:23 | history | asked | Martin Smith | CC BY-SA 4.0 |