What Determines the Locking Order for a Multi-Table Query

What determines the locking order for a multi-table query?

According to https://stackoverflow.com/a/112256/14731 lock order is determined by the implementation-specific execution order. The answer further goes on to say that there isn't a deterministic way to prevent deadlocks. Whereas in imperative programming we can prevent deadlocks by acquiring locks in the same order, it seems that in declarative systems we have to work around them by retrying the operation when a deadlock is detected.

Furthermore, I argue that since database execution plans change over their lifetime it is technically impossible to prevent deadlocks.

Understanding SQL Server LOCKS on SELECT queries

A SELECT in SQL Server will place a shared lock on a table row - and a second SELECT would also require a shared lock, and those are compatible with one another.

So no - one SELECT cannot block another SELECT.

What the WITH (NOLOCK) query hint is used for is to be able to read data that's in the process of being inserted (by another connection) and that hasn't been committed yet.

Without that query hint, a SELECT might be blocked reading a table by an ongoing INSERT (or UPDATE) statement that places an exclusive lock on rows (or possibly a whole table), until that operation's transaction has been committed (or rolled back).

Problem of the WITH (NOLOCK) hint is: you might be reading data rows that aren't going to be inserted at all, in the end (if the INSERT transaction is rolled back) - so your e.g. report might show data that's never really been committed to the database.

There's another query hint that might be useful - WITH (READPAST). This instructs the SELECT command to just skip any rows that it attempts to read and that are locked exclusively. The SELECT will not block, and it will not read any "dirty" un-committed data - but it might skip some rows, e.g. not show all your rows in the table.

MySQL Multiple table locks

LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.

So, in the first case, you have one transaction which hold 2 tables locked, in the second only one, because LOCK TABLES table1 WRITE had been commited

sql server- When does table get locked when updating with join

Blocking vs. dead locking

I think you may be confusing locking and blocking with DEADLOCKS.

On any update query SQL server will lock the involved data. While this lock is active, other processes will be blocked ( delayed ) from editing the data. If the original update takes a long time ( from a users perspective' like a few seconds ) then front end system may seem to 'hang' or even timeout a users front end process and report an error.

This is not a deadlock. This blocking will resolve itself, basically non destructively by either delaying the user slightly or in some cases by forcing front end to be smart about the timeout. In the problem is blocking because of long running updates, you could fix the users having to resubmit by increasing the front end timeout.

A deadlock however cannot be resolved no matter how much you increase the timeout. One or the processes will be terminated with prejudice ( losing the update ).

Deadlocks have different root causes than blocking. Deadlocks are usually caused by inconsistent sequential logic in the front end, which accesses and locks data from two tables in different orders in two different parts of the front end. When these two parts operate concurrently in a multiuser environment they may basically, non deterministically , cause deadlocks, and essentially unsolvable data loss ( until the cause of the deadlocks is resolved ) as opposed to blocking which can usually be dealt with.

Managing blocking

Will SQL server choose row locks or whole table lock?

Generally , it depends and could be different each time. Depending on how many rows the query optimizer determines will be affected, the lock may be row or table. If its over a certain threshold, it will go table because it will be faster.

How can I reduce blocking while adhering to the basic tenets of transactional integrity?

SQL server is going to attempt to lock the tables you are joining to because their contents is material to generating the result set that gets updated. You should be able to show an estimated execution plan for the update to see what will be locked based on today's size of the tables. If the predicted lock is table, you can override perhaps with row lock hint, but this does not guarantee no blocking. It may reduce chance of inadvertent blocking of possibly unrelated data in the table. You will essentially always get blocking of data directly material to the update.

Keep in mind, however;

Also keep in mind the locks taken on the joined table will be Shared locks. Meaning other processes can still read from those tables, they just can't update them, until YOUR update is done using them as reference. In contrast, other processes will actively block on trying to simply READ data that you update has an exclusive lock on ( the main table being updated ).

So, joined table can still be read. Data to be updated will be exclusively locked as a group of records until the updates is complete or fails and is rolled back as a group.

How select for update works for queries with multiple rows/results?

The documentation explains what happens as follows:

FOR UPDATE

FOR UPDATE causes the rows retrieved by the SELECT statement to be
locked as though for update. This prevents them from being locked,
modified or deleted by other transactions until the current
transaction ends. That is, other transactions that attempt UPDATE,
DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE
or SELECT FOR KEY SHARE of these rows will be blocked until
the current transaction ends; conversely, SELECT FOR UPDATE will
wait for a concurrent transaction that has run any of those commands
on the same row, and will then lock and return the updated row (or no
row, if the row was deleted). Within a REPEATABLE READ or
SERIALIZABLE transaction, however, an error will be thrown if a row
to be locked has changed since the transaction started. For further
discussion see Section 13.4.

The direct answer to your question is that Postgres cannot lock all the rows "right off the bat"; it has to find them first. Remember, this is row-level locking rather than table-level locking.

The documentation includes this note:

SELECT FOR UPDATE modifies selected rows to mark them locked, and so
will result in disk writes.

I interpret this as saying that Postgres executes the SELECT query and as it finds the rows, it marks them as locked. The lock (for a given row) starts when Postgres identifies the row. It continues until the end of the transaction.

Based on this, I think it is possible for a deadlock situation to arise using SELECT FOR UPDATE.

Multiple Loop gives lock in SQL Server database

Here's the test data that I set up for this:

DROP TABLE IF EXISTS #Table1;
DROP TABLE IF EXISTS #Table2;

SELECT 1001999 + n AS ID
  INTO #Table1
  FROM (SELECT TOP (30000)
               n = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
          FROM sys.all_objects      AS s1
         CROSS JOIN sys.all_objects AS s2
         ORDER BY s1.[object_id]) AS x;

SELECT 1001999 + n AS ID
  INTO #Table2
  FROM (SELECT TOP (30000)
               n = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
          FROM sys.all_objects      AS s1
         CROSS JOIN sys.all_objects AS s2
         ORDER BY s1.[object_id]) AS x;

Working with (likely) a much smaller dataset than what you're trying to delete from.

When I run the DELETE pattern that you specified, with the WAITFOR DELAY in each of the cursors, I let it run for 20 minutes before just giving up on it. Nothing was being printed to the messages pane and it behaved just like you described.

When I comment out the waits, the code (modified to fit my sample) looks like this:

DECLARE @pkQ BIGINT;

DECLARE DEL_CURSOR CURSOR STATIC FOR
    SELECT ID
      FROM #Table1
     ORDER BY ID DESC;

OPEN DEL_CURSOR;

FETCH NEXT FROM DEL_CURSOR
 INTO @pkQ;
WHILE @@FETCH_STATUS = 0
    BEGIN
        DELETE TOP (10)
        FROM #Table1
         WHERE ID <= @pkQ;
        FETCH NEXT FROM DEL_CURSOR
         INTO @pkQ;
        PRINT '10 deleted from Table1';
        --WAITFOR DELAY '00:00:01';
    END;
CLOSE DEL_CURSOR;
DEALLOCATE DEL_CURSOR;
PRINT 'Cursor Closed';

PRINT N'In SecondCursor';
DECLARE DEL_CURSORR CURSOR FOR
    SELECT TOP 1000
           ID
      FROM #Table2
     ORDER BY ID DESC;

OPEN DEL_CURSORR;

FETCH NEXT FROM DEL_CURSORR
 INTO @pkQ;
WHILE @@FETCH_STATUS = 0
    BEGIN
        --WAITFOR DELAY '00:00:02';
        DELETE TOP (10)
        FROM #Table2
         WHERE ID <= @pkQ;
        FETCH NEXT FROM DEL_CURSORR
         INTO @pkQ;
        --WAITFOR DELAY '00:00:01';
        PRINT '10 deleted from Table2';
    END;
CLOSE DEL_CURSORR;
DEALLOCATE DEL_CURSORR;

And I got a successful completion in 6 seconds.
Even on a measly 30k rows, the WAITFOR DELAY 00:00:01 is going to add 50 minutes of unproductive time to this task.

Final note: Depending on the size of your tables and the amount that you're looking to delete, you may find this blog post from Brent Ozar about "Fast Ordered Deletes" - it won't get you around looping over the deletion sets, but it might help you do it without effecting concurrency
https://www.brentozar.com/archive/2018/04/how-to-delete-just-some-rows-from-a-really-big-table/

Lock multiple tables with aliases doesn't work

While your intention using aliases is right, you still have to uphold the rule

You cannot refer to a locked table multiple times in a single query using the same name. Use aliases instead, and obtain a separate lock for the table and each alias

This includes using an alias twice, which you are doing in

INSERT INTO proc_anx_tmp (...) VALUES ( 
  (SELECT proc_id +1 FROM proc_name as proc_name_1 ORDER BY proc_id DESC LIMIT 1),
  ...
  (SELECT proc_id +1 FROM proc_name as proc_name_1 ORDER BY proc_id DESC LIMIT 1)
  ... );

Replace one of those aliases. Since you are not actually using proc_name without an alias in this query, and you (write-)locked the unaliased table, you can just remove one of those aliases.

---

Related Topics