Difference in Usage Mysql SELECT FOR UPDATE

Publish on Sep 13, 2024 by Alfin F
Subscribe dengan Account Google untuk mendapatkan News Letter terbaru dari Halovina !
SUBSCRIBE
Difference in Usage Mysql SELECT FOR UPDATE

I'd explain the differences between SELECT FOR UPDATE and SELECT FOR NO KEY UPDATE in MySQL:

SELECT FOR UPDATE:


  • Purpose: Acquires a lock on the selected rows, preventing other transactions from modifying or deleting them until the current transaction commits or rolls back.

  • Locking Mechanism: Uses a shared lock (S-lock) on each row, allowing other transactions to read the rows but preventing them from modifying or deleting them.

  • Usage Scenarios:

    • Pessimistic locking: When you want to ensure data consistency and avoid conflicts during updates or deletions.

    • Preventing race conditions: In scenarios where multiple transactions might try to update the same rows simultaneously.

    • Implementing optimistic concurrency control: By combining SELECT FOR UPDATE with a subsequent UPDATE or DELETE statement, you can check for conflicts before making changes.




SELECT FOR NO KEY UPDATE:


  • Purpose: Acquires a lock on the selected rows, but only if they have a unique key or primary key. If a row doesn't have a unique key, no lock is acquired.

  • Locking Mechanism: Uses a shared lock (S-lock) on rows with unique keys, preventing other transactions from modifying or deleting them until the current transaction commits or rolls back.

  • Usage Scenarios:

    • When you want to lock rows based on their uniqueness, but you don't need to lock rows that don't have a unique key.

    • In situations where you want to avoid unnecessary locking overhead for rows that are unlikely to be modified or deleted.




Key Differences:


  • Locking Scope: SELECT FOR UPDATE locks all selected rows, while SELECT FOR NO KEY UPDATE only locks rows with unique keys.

  • Locking Granularity: SELECT FOR UPDATE locks individual rows, while SELECT FOR NO KEY UPDATE locks rows based on their uniqueness.

  • Performance Implications: SELECT FOR NO KEY UPDATE can be more efficient in scenarios where many rows don't have unique keys, as it avoids unnecessary locking overhead.


Choosing the Right Option:


  • Use SELECT FOR UPDATE when you need to ensure data consistency and prevent conflicts for all selected rows.

  • Use SELECT FOR NO KEY UPDATE when you want to lock rows based on their uniqueness but avoid unnecessary locking overhead for rows without unique keys.


Example:


SQL



-- SELECT FOR UPDATE: Locks all selected rows
SELECT * FROM my_table WHERE id > 10 FOR UPDATE;



-- SELECT FOR NO KEY UPDATE: Locks only rows with unique keys
SELECT * FROM my_table WHERE name = 'John' FOR NO KEY UPDATE;







I hope this explanation clarifies the differences between SELECT FOR UPDATE and SELECT FOR NO KEY UPDATE in MySQL!

Baca artikel lainya :

Copyright © 2024 halovina.com

Develop by Halovina Tech