MariaDB WAIT and NOWAIT Explained: Controlling Lock Acquisition

• NOWAIT tells the query to not wait at all. If the lock cannot be acquired immediately, the query will fail straightaway.

• WAIT instructs the query to wait for a specified number of seconds (represented by "n") to acquire the lock. If the lock isn't obtained within that timeframe, the query will eventually fail.

Points to Consider

• There are advantages and disadvantages to using WAIT and NOWAIT. They can improve performance by preventing deadlocks (situations where two transactions are waiting for each other's locks) but can also lead to retries and potential inconsistencies if not used strategically.

• WAIT and NOWAIT can be used with various SQL statements, including:

  • SELECT with FOR UPDATE clause (used for locking rows for potential update)
  • DELETE
  • UPDATE
  • ALTER TABLE (introduced in MariaDB 10.3)

• WAIT was introduced in MariaDB 10.3. In earlier versions, you would control lock wait time by setting the lock_wait_timeout or innodb_lock_wait_timeout system variables. Setting these variables to 0 has the same effect as using NOWAIT.

---

UPDATE with WAIT

UPDATE user_table
SET name = 'New Name'
WHERE id = 123
WAIT 5;  -- Wait for up to 5 seconds to acquire lock

This code attempts to update the name of a user with ID 123. The WAIT 5 clause tells the query to wait for a maximum of 5 seconds if another transaction has locked the row. If the lock isn't acquired within 5 seconds, the update will fail.

DELETE with NOWAIT

DELETE FROM product_table
WHERE stock = 0
NOWAIT;

This code deletes products with zero stock from the product_table. The NOWAIT keyword ensures the deletion proceeds only if the rows are not locked by another transaction. If a lock is present, the deletion for that specific row will fail, but the process will continue for other rows that are not locked.

SELECT FOR UPDATE with WAIT

SELECT * FROM order_table
WHERE order_id = 456
FOR UPDATE WAIT 2;

This code selects all information from the order table for order ID 456. The FOR UPDATE clause acquires a lock on the row, preventing other transactions from modifying it. The WAIT 2 clause specifies a 2-second wait time to obtain the lock. If another transaction holds the lock and the wait time elapses, the query will fail.

• It's essential to consider the potential consequences of retries and data inconsistencies when using NOWAIT.
• These are just basic examples. The choice between WAIT and NOWAIT depends on your specific needs and how you want to handle potential locking situations.

---

1. Optimistic Locking
   

• The update succeeds only if the version number in the database matches the one retrieved earlier. This ensures another transaction hasn't modified the data since it was read.
• During update, the version number is included in the WHERE clause.
• When a row is read, a version number is retrieved alongside the data.

Advantages

• Reduces deadlocks.
• Improves concurrency as transactions don't wait for locks explicitly.

Disadvantages

• Requires additional logic in your application to handle conflicts (e.g., retrying updates).

1. Stored Procedures
   

You can encapsulate your logic within stored procedures that manage locking behavior internally. This allows for more granular control over how locks are acquired and released within the procedure.

Advantages

• Reusable code for common operations.
• Offers flexibility in handling locking scenarios.

Disadvantages

• Might impact performance if not implemented efficiently.
• Requires additional development effort.

1. Locking Hints
   

MariaDB supports locking hints that can be used to suggest a specific locking strategy to the optimizer. These hints don't guarantee a specific behavior but can influence how the query acquires locks.

Example

SELECT * FROM table_name HIGH_PRIORITY LOCK IN SHARE MODE;

This query suggests acquiring a shared lock on the table with high priority. However, the optimizer might choose a different strategy based on the current locking situation.

Advantages

• Can potentially improve performance by influencing the locking strategy.

Disadvantages

• Hints might be ignored by the optimizer in certain situations.
• Limited control over actual locking behavior.

1. Transaction Isolation Levels
   

MariaDB supports different transaction isolation levels that define how transactions see changes made by other transactions. Choosing an appropriate isolation level can impact locking behavior.

• REPEATABLE READ - Transactions see a consistent snapshot of the data as of the moment they start reading. This can lead to more locking but ensures consistent reads.
• READ COMMITTED - Transactions only see changes committed by other transactions after they start their own read operation.