Sql-server – Controlled Transaction Blocking – Resume Behavior

blockinglockingsql server

When several procedures are blocked on a shared resource, how does sql server pick which one to resume?

Example 1

proc sp_a
begin tran

  select 1 from dbo.lock_tbl with ( tbllockx )
  
  delay wait for '00:00:01' 
  commit / rollback;

Execute

exec sp_a -- on terminal 1
exec sp_a -- on terminal 2
exec sp_a -- on terminal 3

Does sql server choose whether to start the instance on 2 or 3 first?

Example 2

Closer to intended use

proc sp_b @source_param

declare @rows cursor = ... get_rows( @source_param ) 
while ( ... for each @row )
begin
  begin tran  
    select 1 from dbo.lock_tbl with ( tbllockx )

    -- do logic
    exec sp_g @row

  commit / rollback;

  delay wait for '00:00:01' 
end

Execute

exec sp_b 'd' -- on terminal 1
exec sp_b 'e' -- on terminal 2
exec sp_b 'f' -- on terminal 3

Will the code above round-robin execute procedure sp_g from each procedure
eg: will it execute sp_g on terminal 1, then execute sp_g on terminal 2, then execute sp_g on terminal 3, then execute g on terminal 1, etc?

Followups :

Does it pass the lock to the procedure which has been waiting the longest?

Is there a way to encourage round robin behavior?

Rationale

( optional )
The database is a low transaction database, but will hit deadlocks when multiple transactions occur at the same time, due to complex execution business logic. A global lock table can be used to eliminate deadlocks via throttling the execution flow to one thread at a time. Keeping the execution synchronous seems more desirable than restructuring the database and UI into asynchronous queues. The work will take place in small transactions and hand off procedure control between the procedures, which will take turns to complete their respective work in chunks, thus slowing the completion of the tasks in exchange for reliable synchronous completion.

Best Answer

Example 1: You shouldn't care. The process is much more involved than it seems, and I don't think you have any guarantee. The 3 commands go into the queue in order. The first one is pulled and executed, when the second is attempted, the engine may choose to put it back in queue if it waits for more than some threshold, and the third may be pulled and attempted. if it happens that the resources are now free, the 3rd will be executed first. if not, it may go back in queue again.

Example 2: I think the above explains it, and the answer to both your questions is no AFAIK, unless you develop an execution buffer of your own. Don't.

Rationale: Now you got to the core challenge, which should really have been the title - "How to deal with deadlocks". You assumed that your 'global lock table' solution is the only way to make things right, but I think the price of losing all concurrency is not worth it. It would be easier just to have the procedures executed serially using a scheduler of some sort. You will open a Pandora box with this approach that will be hard to close. Queuing and syncing is a very complicated challenge.

If I were in your place, I would first investigate the root cause of the deadlocks. Sometimes, something as simple as adding or modifying an index can solve it. If you post your deadlock graphs here, along with the involved queries with their execution plans, people will help you pin point the root issue.

HTH

Related Solutions

Mysql – In InnoDB, does a Transaction imply any implicit locking of a table

In repeatable reads, there is always row-level locking imposed via the gen_clust_index (aka the Clustered Index). This is the beauty of Transactions. What is even more interesting is that InnoDB has four transaction isolation levels, not just one:

There are four values for tx_isolation you can set:

In your particular case, inserting data into TableA actually does not get written to disk. The necessary changes are recorded in three(3) distinct places:

log buffer in memory
in ibdata1
- undo tablespace
- rollback segments
- double write buffer
redo log info in either ib_logfile0 or ib_logfile1

The same applies with the delete in step 5.

Executing a rollback will undo the delete and then undo the inserts.

You must remember something very important: If you want to rollback multiple SQL commands, you must begin like this:

SET autocommit = 0;
START TRANSACTION;

Transaction begins
Read data from Table A
If (Table A has Any Data) End Transaction (via ROLLBACK) and exit
If Table A has No Data, Proceed further
Delete a record in Table B
Transaction ends

COMMIT;

Give it a Try !!!

When everyone is using repeatable reads

your INSERTs are only seen by you
someone else's DELETEs are only seen by the other person

CAVEAT : Table level locking is never implicit for InnoDB. If you want to lock a table, you must issue, LOCK TABLE explicitly.

Sql-server – connection pooling, transactions, nested transaction and rollback

There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).

From the documentation:

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A demonstration:

CREATE TABLE dbo.foo(a INT);

BEGIN TRANSACTION;
  INSERT dbo.foo(a) SELECT 1;
  BEGIN TRANSACTION;
    INSERT dbo.foo(a) SELECT 2;
    BEGIN TRANSACTION;
    ROLLBACK TRANSACTION;

SELECT tc = @@TRANCOUNT;

SELECT a FROM dbo.foo;

DROP TABLE dbo.foo;

Results:

tc    
----
0

1 row(s) affected.

a
----

0 row(s) affected.

So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.