SELECT * FROM table_name WHERE condition FOR UPDATE;

-- 事务1
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- 此时其他事务无法修改id=1的记录
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

START TRANSACTION;
SELECT stock FROM products WHERE id = 1001 FOR UPDATE;
-- 检查库存并扣减
UPDATE products SET stock = stock - 1 WHERE id = 1001;
COMMIT;

START TRANSACTION;
SELECT balance FROM accounts WHERE user_id = 123 FOR UPDATE;
-- 确保余额充足后进行转账
UPDATE accounts SET balance = balance - 500 WHERE user_id = 123;
COMMIT;

START TRANSACTION;
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;
-- 处理待处理订单
UPDATE orders SET status = 'processing' WHERE status = 'pending';
COMMIT;

-- 错误用法（不在事务中）
SELECT * FROM table FOR UPDATE;

-- 正确用法
START TRANSACTION;
SELECT * FROM table FOR UPDATE;
-- ... 其他操作
COMMIT;

-- 使用主键索引（高效）
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 无索引字段可能导致表锁（性能差）
SELECT * FROM users WHERE name = 'John' FOR UPDATE;

-- 银行转账场景
START TRANSACTION;

-- 锁定转出账户
SELECT balance FROM accounts WHERE account_no = 'A001' FOR UPDATE;
-- 锁定转入账户  
SELECT balance FROM accounts WHERE account_no = 'B002' FOR UPDATE;

-- 执行转账操作
UPDATE accounts SET balance = balance - 1000 WHERE account_no = 'A001';
UPDATE accounts SET balance = balance + 1000 WHERE account_no = 'B002';

COMMIT;