LOCK

MySQL

Example

START TRANSACTION;
SELECT * FROM Employees WHERE EmployeeID = 1 FOR UPDATE;

Output

+------------+----------+----------+
| EmployeeID | Name     | Position |
+------------+----------+----------+
|          1 | John Doe | Manager  |
+------------+----------+----------+

Explanation

The above SQL commands begin a transaction, then select a record from the ‘Employees’ table with ‘EmployeeID’ of 1 and lock it for update. This prevents other transactions from modifying or deleting the selected row until the current transaction completes.

PostgreSQL

Example

BEGIN;
LOCK TABLE users IN ACCESS EXCLUSIVE MODE;
-- other transactions
COMMIT;

Output

BEGIN
LOCK TABLE

Explanation

The above example initiates a new transaction. It then locks the users table preventing other transactions from accessing it. Finally, it commits the transaction which releases the lock. The response from PostgreSQL signifies successful execution.

SQL Server

Example

BEGIN TRANSACTION;
SELECT * FROM Employees WITH (HOLDLOCK, ROWLOCK)
WHERE EmployeeId = 1;
COMMIT;

Output

EmployeeId | EmployeeName | Position
----------------------------------
1          | John Doe     | Manager

Explanation

In this example, the HOLDLOCK and ROWLOCK hints are used to lock the specific row with EmployeeId 1 in the Employees table for the duration of the transaction. This prevents other transactions from modifying or deleting that row until this transaction is committed or rolled back.

Oracle

Example

BEGIN
    LOCK TABLE employees IN EXCLUSIVE MODE NOWAIT;
    UPDATE employees SET salary = salary + 100 WHERE employee_id = 101;
COMMIT;
END;

Output

No output would be displayed for this SQL statement as it does not return anything. The assumption is the SQL statement ran without any errors, which would mean the table employees was successfully locked, updates were made, and then the lock was released through the COMMIT statement.

Explanation

This example demonstrates the locking of a table in Oracle SQL. The LOCK TABLE employees IN EXCLUSIVE MODE NOWAIT; line locks the ‘employees’ table in an exclusive mode immediately. This prevents other sessions from obtaining any conflicting locks on the rows and/or the table until the operation completes. Changes are then made to the ‘salary’ in the ‘employees’ table where employee_id = 101. The COMMIT; statement then releases the lock by ending the transaction, which saves any changes made to the database from that transaction. The END; closes the anonymous PL/SQL block.