定义

for update是一种行级锁，又叫排它锁，一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行．如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁．即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。行锁永远是独占方式锁。释放共享更新锁的条件：

1、commit

2、退出数据库

3、程序终止

死锁: 使用 SELECT ... FOR UPDATE 时，如果多个事务相互等待锁，可能会导致死锁。确保事务以一致的顺序锁定行

-- 事务 1
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
SELECT balance FROM accounts WHERE id = 2 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 事务 2
BEGIN;
SELECT balance FROM accounts WHERE id = 2 FOR UPDATE;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

示例

https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#SQLRF01702

The following statement locks only those rows in the employees table with purchasing clerks located in Oxford. No rows are locked in the departments table:SELECT中包含多张表时（比如JOIN），FOR UPDATE可以指定要LOCK哪张表的匹配到的ROWS

SELECT e.employee_id, e.salary, e.commission_pct  
   FROM employees e JOIN departments d  
   USING (department_id)  
   WHERE job_id = 'SA_REP'  
   AND location_id = 2500  
   FOR UPDATE OF e.salary  
   ORDER BY e.employee_id;  

场景

数据独占场景

悲观锁

悲观锁：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它解锁。

sqlalchemy

user = session.query(User).filter(User.email == email).with_for_update().first()

user.is_active = True
session.add(user)
session.commit()

方法1: 直接更新

session.query(Account).filter_by(id=1)\
     .update({"amount": Account.amount + 100})
session.commit()

方法2：add lock

with_for_update()

方法3： 乐观锁，版本跟踪

class Account(Base):
    __tablename__ = "account"
    ...
    version = Column(Integer, nullable=False)

    __mapper_args__ = {"version_id_col": version}


def version_tracking(change):
    try:
        account = session.query(Account).get(1)
        account.amount = account.amount + change
        print_account(account, change)
        session.commit()
    except StaleDataError:
        print("someone has changed the account, plz retry.")