Optimistic and pessimistic locking are two types of locking mechanisms that help prevent simultaneous access to a specific record in a database by multiple users. These mechanisms are valuable when considering concurrency control, as it maintains consistency and integrity in a database.
1. Optimistic Locking: Optimistic locking allows multiple transactions to access the same record for editing. It ensures data integrity by checking whether the original data was changed by some other transactions during the time of updating. When a change is detected, the transaction is rolled back. MariaDB provides a feature called MVCC (Multi-Version Concurrency Control) to handle optimistic concurrency.
To manage optimistic locking, you might use versioning. Here’s an example using an additional column `version`: \`\`\`sql CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR, price DECIMAL, version INT ); INSERT INTO products VALUES (1, ‘Product 1’, 100.0, 1); \`\`\` When you do the update, you increase the version count: \`\`\`sql UPDATE products SET name = ‘New Product’, version = version + 1 WHERE id = 1 and version = 1; \`\`\` If the version hasn’t been increased by another transaction – the row will be updated, otherwise no changes will be made.1. Pessimistic Locking: With pessimistic locking, when a user is reading a record with the intent of updating or deleting it, a lock is applied so no other transactions can modify the data. After the transaction with the lock is complete, the record is released for other transactions.
MariaDB has `FOR UPDATE` and `LOCK IN SHARE MODE` which can be used to implement pessimistic locks by locking selected rows from `SELECT`: Example with `FOR UPDATE`: \`\`\`sql START TRANSACTION; SELECT \* FROM products WHERE id = 1 FOR UPDATE; UPDATE products SET price = 150.00 WHERE id = 1; COMMIT; \`\`\` Example with `LOCK IN SHARE MODE` which allows read but not update: \`\`\`sql START TRANSACTION; SELECT \* FROM products WHERE id = 1 LOCK IN SHARE MODE; SELECT price FROM products WHERE id = 1; COMMIT; \`\`\`Remember that unnecessary usage of locks can decrease the performance of your database, as other transactions will wait for the lock to be released. The decision about when and how to use these locks must be cautiously made according to the application requirements and traffic flow.
