SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
CREATE PROCEDURE tfer_funds
(from_account int, to_account int, tfer_amount numeric(10,2))
BEGIN
SET autocommit=0;
UPDATE account_balance SET balance=balance-tfer_amount WHERE account_id=from_account;
UPDATE account_balance SET balance=balance+tfer_amount WHERE account_id=to_account;
COMMIT;
END;
CREATE PROCEDURE tfer_funds
(from_account int, to_account int, tfer_amount numeric(10,2))
BEGIN
START TRANSACTION;
UPDATE account_balance SET balance=balance-tfer_amount WHERE account_id=from_account;
UPDATE account_balance SET balance=balance+tfer_amount WHERE account_id=to_account;
COMMIT;
END;
CREATE PROCEDURE nested_tfer_funds
(in_from_acct INTEGER,
in_to_acct INTEGER,
in_tfer_amount DECIMAL(8,2))
BEGIN
DECLARE txn_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
SET txn_error=1;
END
SAVEPINT savepint_tfer;
UPDATE account_balance
SET balance=balance-in_tfer_amount
WHERE account_id=in_from_acct;
IF txn_error THEN
ROLLBACK TO savepoint_tfer;
SELECT 'Transfer aborted';
ELSE
UPDATE account_balance
SET balance=balance+in_tfer_amount
WHERE account_id=in_to_acct;
IF txn_error THEN
ROLLBACK TO savepoint_tfer;
SELECT 'Transfer aborted';
END IF:
END IF;
END;
SELECT select_statement options [FOR UPDATE|LOCK IN SHARE MODE]
mysql > CALL tfer_funds(1,2,300);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
CREATE PROCEDURE tfer_funds3
(from_account INT, to_account INT, tfer_amount NUMERIC(10,2))
BEGIN
DECLARE local_account_id INT;
DECLARE lock_cursor CURSOR FOR
SELECT account_id
FROM account_balance
WHERE account_id IN (from_account, to_account)
ORDER BY account_id
FOR UPDATE;
START TRANSACTION;
OPEN lock_cursor;
FETCH lock_cursor INTO local_account_id;
UPDATE account_balance
SET balance=balance-tfer_amount
WHERE account_id=from_account;
UPDATE account_balance
SET balance=balance+tfer_amount
WHERE account_id=to_account;
CLOSE lock_cursor;
COMMIT;
END;
CREATE PROCEDURE tfer_funds
(from_account INT, to_account INT,tfer_amount NUMERIC(10,2),
OUT status INT, OUT message VARCHAR(30))
BEGIN
DECLARE from_account_balance NUMERIC(10,2);
START TRANSACTION;
SELECT balance
INTO from_account_balance
FROM account_balance
WHERE account_id=from_account
FOR UPDATE;
IF from_account_balance>=tfer_amount THEN
UPDATE account_balance
SET balance=balance-tfer_amount
WHERE account_id=from_account;
UPDATE account_balance
SET balance=balance+tfer_amount
WHERE account_id=to_account;
COMMIT;
SET status=0;
SET message='OK';
ELSE
ROLLBACK;
SET status=-1;
SET message='Insufficient funds';
END IF;
END;
CREATE PROCEDURE tfer_funds
(from_account INT, to_account INT, tfer_amount NUMERIC(10,2),
OUT status INT, OUT message VARCHAR(30) )
BEGIN
DECLARE from_account_balance NUMERIC(8,2);
DECLARE from_account_balance2 NUMERIC(8,2);
DECLARE from_account_timestamp1 TIMESTAMP;
DECLARE from_account_timestamp2 TIMESTAMP;
SELECT account_timestamp,balance
INTO from_account_timestamp1,from_account_balance
FROM account_balance
WHERE account_id=from_account;
IF (from_account_balance>=tfer_amount) THEN
-- Here we perform some long running validation that
-- might take a few minutes */
CALL long_running_validation(from_account);
START TRANSACTION;
-- Make sure the account row has not been updated since
-- our initial check
SELECT account_timestamp, balance
INTO from_account_timestamp2,from_account_balance2
FROM account_balance
WHERE account_id=from_account
FOR UPDATE;
IF (from_account_timestamp1 <> from_account_timestamp2 OR
from_account_balance <> from_account_balance2) THEN
ROLLBACK;
SET status=-1;
SET message=CONCAT("Transaction cancelled due to concurrent update",
" of account" ,from_account);
ELSE
UPDATE account_balance
SET balance=balance-tfer_amount
WHERE account_id=from_account;
UPDATE account_balance
SET balance=balance+tfer_amount
WHERE account_id=to_account;
COMMIT;
SET status=0;
SET message="OK";
END IF;
ELSE
ROLLBACK;
SET status=-1;
SET message="Insufficient funds";
END IF;
END$$
欢迎光临 MySQL社区 (http://www.mysqlpub.com/) | Powered by Discuz! X3.2 |