Name
SQL-07: Use SELECT FOR UPDATE when retrieving rows for later update
Synopsis
Use the SELECT FOR UPDATE
statement to request that locks be placed on all rows identified
by the query. You should do this whenever you expect to change
some or all of those rows, and you don’t want another session to
change them out from under you. Any other session trying to update
the rows, or lock the rows (perhaps using FOR UPDATE
), will have to wait.
Example
Here we are processing a special bonus payment for needy
employees. We issue the FOR
UPDATE
clause so that the rows concerned are locked
until our transaction completes:
CREATE PROCEDURE needy_bonus( )
BEGIN
DECLARE v_employee_id INT;
DECLARE v_salary NUMERIC(8,2);
DECLARE v_last_emp INT DEFAULT 0;
DECLARE emp_csr CURSOR FOR
SELECT employee_id,salary
FROM employees
WHERE salary <45000
FOR UPDATE
;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_last_emp=1;
START TRANSACTION;
OPEN emp_csr;
emp_loop:LOOP
FETCH emp_csr INTO v_employee_id, v_salary;
IF v_last_emp THEN
LEAVE emp_loop;
END IF;
CALL grant_raise( v_employee_id, v_salary);
END LOOP emp_loop;
CLOSE emp_csr;
SET v_last_emp=0;
COMMIT;
END;
You can also use the LOCK IN SHARE
MODE
clause to lock the rows against update but continue
to allow reads.
Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.