How to use sea-orm to lock the row for innodb?

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 update also 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.

The following update statement does need to wait for the return of the first transaction. It's my misuse of the API.

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.