Can I open a transaction with a stop with SQL Server?

Question

I am looking for something similar to SQL transaction. I need the usual protection that transactions provide, but I do not want it to slow down others.

Imagine that client A connects to the database and executes the following commands:

BEGIN TRAN SELECT (something) (Wait a few seconds maybe.) UPDATE (something) COMMIT 

Between SELECT and UPDATE, client B comes in and tries to execute a query that, under normal circumstances, will have to wait for A to be COMMIT.

I would like client A to open the transaction so that B comes in and executes the request, client A finds that the transaction is immediately rolled back and subsequent commands are not executed. Client B will experience minimal delay.

(Note that SELECT and UPDATE are merely illustrative.)

Updating ...

I have a high priority task (client B) that sometimes (once a month) gets an SQL timeout error and a low priority task (client A) with a transaction that causes this timeout. I would prefer the low priority task to fail and restart in the next loop.

In the end, I solved this problem by completely eliminating transactions and replacing them with an unofficial set of flags. The requests were reorganized only to do something only if the correct set of flags was raised, and I added something that cleared the abandoned records that the rollback had deleted in the past.

I fixed my transactions by excluding transactions.

Answer 1

While the transaction is not running at all, Optimistic Concurrency can be useful - it is used by default in LINQ2SQL, etc.

The general idea is that the data is read - modifications can be made independently, and then the data is written using a "check" (this is completely incompatible with Compare and Swap). If the verification fails, this application should decide what to do (restart the process, continue in any case, failure).

This, of course, does not work for all scenarios and may not detect a number of interactions, such as new elements added between read and write. And the actual read and write can be in separate transactions with the appropriate level of isolation; individual transactions may allow alternation of additional transactions.

Of course, depending on the exact problem and interaction ... there may be quite different levels of isolation and / or finer granular fixation.

Happy coding.

Answer 2

Using SNAPSHOT isolation level will prevent blocking B B will see the data in the state it was before A issued by BEGIN TRANSACTION . If B does not modify the data, they will never block each other.

Answer 3

This is again to the front.

You cannot have later clients interrupting earlier transactions: this chaos.

You can have snapshot locks so that client B has a consistent view and is not blocked (mainly) by client A. Also Wikipedia for more general things

Perhaps a more detailed description of your problem, so that we can offer suggestions for this ...

Answer 4

One thing I saw is used (but I'm afraid that I don't have code convenient for it) has a transaction. Another process appears, which then tracks the transaction. If he sees any blocks caused by the transaction, he immediately returns KILL for spid.

If I can find the code for this, I will add it here.