In the program that uses sea-orm, I start a transaction to do select ... for update, the snippet is
let sql= "SELECT * from table_1 where id=1 for update".to_owned();
let begin = db.begin().await?;
let r = Table1::find().from_raw_sql(Statement::from_string(DatabaseBackend::MySql, sql)).one(db).await?;
sleep(std::time::Duration::from_secs(5)).await; // delay time
start.commit().await?
Then, in the MySQL client, I start a transaction to do the update operation
Begin;
update table_1 set money=1 where id=1;
Commit;
The update operation is successful without waiting for the transaction to be committed in my program. Is it something I used wrong? How to lock the row by using sea-orm?
Is the select … for updatealso within a transaction? Is it a different transaction from the update?
If it's not in a transaction, then it will be run in a single-statement transaction, which finalizes immediately once the statement returns, releasing the lock implied by the for update clause. If it's in the same transaction, then the following update will not conflict with the for update row lock.