C# - TSQL Parallel transactions on same connection instance

Question

I am developing a C# ORM with php's Laravel-like Syntax. When the ORM starts, it connects to the database and performs any query (it also supports two different connections for reading and writing), and reconnect to the db only if connection is losen or missing.

As this is a Web Framework ORM, how can I handle concurrent transactions on the same connection? Are they supported?

I saw I can manually assign the transaction object to the sqlcommand, but can I create parallel sqltransactions?

Example:

There is an URL of a REST action that will cause a transaction to be opened, some actions performed, and then transaction committed (e.g. perform an order on a shopping cart). What if, multiple users (so different WebOperationContext) call that URL? Is it possible to open and work with multiple "parallel" transactions and then commit them?

How do other ORM's handle this case? Did they use multiple connections?

Thanks for any support!

Mattia

usr · Accepted Answer · 2016-01-21 18:13:10Z

3

SQL Server does not support parallel transactions on the same connection.

Normally, there is no need for that. Just open connections as you need them. This is cheap thanks to pooling.

A common model is to open connection and transaction right after another. Then, commit and dispose everything at the end.

That way concurrent HTTP requests do not interact at all which is good.

answered Jan 21, 2016 at 18:13

usr

172k38 gold badges251 silver badges380 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mattia Manzo Manzati Over a year ago

So this is SQL Server specific right? Other DB engines or ODBC drivers may allow parallel transactions in the same connection, right?

usr Over a year ago

Yes, they might. The DB engine and the driver must support this. Is this relevant for you? The question is tagged SQL Server.

Collectives™ on Stack Overflow

C# - TSQL Parallel transactions on same connection instance

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related