Oracle vs SQL Server: reads, locks and deadlocks

SQL (oracle_writer.sql) · readers/oracle_writer.sql

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1 with the practice schema.
-- Client: SQL*Plus, window 1. The writer: it takes 260.25 out of account 3 (760.25) and keeps the change open
-- for ten seconds. Start it, then run oracle_reader.sql in a second window within five seconds.
-- a row lock on account 3, not committed
UPDATE accounts SET balance = balance - 260.25 WHERE account_id = 3;
EXEC DBMS_SESSION.SLEEP(10)
-- puts the practice data back
ROLLBACK;
-- 760.25 again
SELECT balance FROM accounts WHERE account_id = 3;

SQL (oracle_reader.sql) · readers/oracle_reader.sql

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1 with the practice schema.
-- Client: SQL*Plus, window 2, while oracle_writer.sql holds its change. The plain query returns at once with the
-- committed balance, rebuilt from undo data; only a query that asks for the row lock has to wait.
-- 760.25 at once: no waiting
SELECT balance FROM accounts WHERE account_id = 3;
-- asks for the lock: after 3 s it fails, with ORA-00054 and ORA-40097 on 26ai
SELECT balance FROM accounts WHERE account_id = 3 FOR UPDATE WAIT 3;

SQL (mssql_writer.sql) · readers/mssql_writer.sql

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab with the practice schema, READ_COMMITTED_SNAPSHOT OFF
-- (the SQL Server default). Client: sqlcmd, window 1. The writer: it takes 260.25 out of account 3 and keeps the
-- change open for ten seconds. Start it, then run mssql_reader.sql in a second window within five seconds.
SET NOCOUNT ON;
BEGIN TRANSACTION;
  UPDATE dbo.accounts SET balance = balance - 260.25 WHERE account_id = 3;   -- an X lock on account 3's key
  WAITFOR DELAY '00:00:10';
ROLLBACK TRANSACTION;                                                       -- puts the practice data back
SELECT balance FROM dbo.accounts WHERE account_id = 3;                      -- 760.25 again
GO

SQL (mssql_reader.sql) · readers/mssql_reader.sql

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab with the practice schema. Client: sqlcmd, window 2,
-- while mssql_writer.sql holds its X lock. READ COMMITTED with locking: the reader's shared lock waits for the
-- writer. SET LOCK_TIMEOUT 3000 ends the wait after 3 seconds with error 1222; the session itself goes on.
SET NOCOUNT ON;
SET LOCK_TIMEOUT 3000;                                                      -- wait at most 3000 ms for any lock
BEGIN TRY
  SELECT balance FROM dbo.accounts WHERE account_id = 3;                    -- waits, then fails
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS message;        -- 1222, Lock request time out ...
END CATCH;
SET LOCK_TIMEOUT -1;                                                        -- back to the default: wait for ever
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = DB_NAME();   -- 0: readers use locks
GO

SQL (oracle_session_1.sql) · deadlock/oracle_session_1.sql

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1 with the practice schema.
-- Client: SQL*Plus, window 1. Session 1 moves 100.00 from account 1 to account 4: it locks account 1, waits five
-- seconds, then needs account 4. Start it, then oracle_session_2.sql in a second window within five seconds.
-- Oracle chooses no victim: either session may receive ORA-00060, so both scripts handle it the same way.
SET SERVEROUTPUT ON
SET LINESIZE 120
DECLARE
  e_deadlock EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_deadlock, -60);
  v_one accounts.balance%TYPE;
  v_four accounts.balance%TYPE;
BEGIN
  UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;   -- session 1 locks account 1
  DBMS_SESSION.SLEEP(5);
  UPDATE accounts SET balance = balance + 100 WHERE account_id = 4;   -- needs session 2's row
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('session 1 committed');
EXCEPTION
  WHEN e_deadlock THEN
    SELECT balance INTO v_one FROM accounts WHERE account_id = 1;
    SELECT balance INTO v_four FROM accounts WHERE account_id = 4;
    DBMS_OUTPUT.PUT_LINE('session 1: ORA-00060; this transaction still sees account 1 at ' || v_one
                         || ' and account 4 at ' || v_four);           -- the first UPDATE is still in place
    ROLLBACK;                                                          -- frees account 1 for session 2
END;
/

SQL (oracle_session_2.sql) · deadlock/oracle_session_2.sql

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1 with the practice schema.
-- Client: SQL*Plus, window 2. Session 2 moves 50.00 from account 4 to account 1, in the opposite order to session 1,
-- so a cycle forms. The session that receives ORA-00060 loses only that statement: its first UPDATE and the lock
-- that came with it stay until it commits or rolls back, and the other session waits until then.
SET SERVEROUTPUT ON
SET LINESIZE 120
DECLARE
  e_deadlock EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_deadlock, -60);
  v_one accounts.balance%TYPE;
  v_four accounts.balance%TYPE;
BEGIN
  UPDATE accounts SET balance = balance - 50 WHERE account_id = 4;    -- session 2 locks account 4
  DBMS_SESSION.SLEEP(5);
  UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;    -- needs session 1's row: a cycle
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('session 2 committed');
EXCEPTION
  WHEN e_deadlock THEN
    SELECT balance INTO v_one FROM accounts WHERE account_id = 1;
    SELECT balance INTO v_four FROM accounts WHERE account_id = 4;
    DBMS_OUTPUT.PUT_LINE('session 2: ORA-00060; this transaction still sees account 1 at ' || v_one
                         || ' and account 4 at ' || v_four);           -- 2500 and 41200.5 when session 2 is hit
    ROLLBACK;                                                          -- frees account 4 for session 1
END;
/
-- Put the practice data back after both windows have finished: undo whichever transfer committed.
-- UPDATE accounts SET balance = 2500.00 WHERE account_id = 1;
-- UPDATE accounts SET balance = 41250.50 WHERE account_id = 4;
-- COMMIT;

SQL (mssql_session_1.sql) · deadlock/mssql_session_1.sql

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab with the practice schema. Client: sqlcmd, window 1.
-- Session 1 moves 100.00 from account 1 to account 4: it locks account 1, waits five seconds, then needs account 4.
-- Start it, then mssql_session_2.sql in a second window within five seconds; session 2 asks to be the victim.
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRANSACTION;
  UPDATE dbo.accounts SET balance = balance - 100.00 WHERE account_id = 1;   -- session 1 locks account 1
  WAITFOR DELAY '00:00:05';
  UPDATE dbo.accounts SET balance = balance + 100.00 WHERE account_id = 4;   -- waits until session 2 is the victim
COMMIT TRANSACTION;
PRINT N'session 1 committed';
UPDATE dbo.accounts SET balance = balance + 100.00 WHERE account_id = 1;     -- put the practice data back
UPDATE dbo.accounts SET balance = balance - 100.00 WHERE account_id = 4;
GO

SQL (mssql_session_2.sql) · deadlock/mssql_session_2.sql

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab with the practice schema. Client: sqlcmd, window 2.
-- Session 2 moves 50.00 from account 4 to account 1, in the opposite order, with DEADLOCK_PRIORITY LOW so that SQL
-- Server picks it as the victim. Error 1205 rolls back its whole transaction and frees account 4 for session 1;
-- inside TRY...CATCH the transaction is doomed until ROLLBACK runs.
SET NOCOUNT ON;
SET XACT_ABORT ON;
SET DEADLOCK_PRIORITY LOW;                                                   -- the lower priority is the victim
BEGIN TRY
  BEGIN TRANSACTION;
    UPDATE dbo.accounts SET balance = balance - 50.00 WHERE account_id = 4;  -- session 2 locks account 4
    WAITFOR DELAY '00:00:05';
    UPDATE dbo.accounts SET balance = balance + 50.00 WHERE account_id = 1;  -- needs session 1's row: a cycle
  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  PRINT CONCAT(N'session 2: error ', ERROR_NUMBER(), N', XACT_STATE() ', XACT_STATE());   -- 1205 and -1
  IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;                                -- ends the doomed transaction
  PRINT CONCAT(N'session 2: @@TRANCOUNT ', @@TRANCOUNT, N' after ROLLBACK'); -- 0: nothing of the transfer is left
END CATCH;
GO