Support for row locking?

[nappa85]

Hello, it's me again! xD

I just noticed there is no support for row locking (or at least I haven't find out).
Is it something you have excluded for a reason or simply you still haven't covered this feature?

[tyt2y3]

Hi there, welcome again )
No, it's just not being thoroughly investigated yet.

[tqwewe]

@nappa85 Could you briefly explain what row locking is? I haven't heard of this before. Is this something similar to a read/write mutex but for a row in the database?

[nappa85]

@nappa85 Could you briefly explain what row locking is? I haven't heard of this before. Is this something similar to a read/write mutex but for a row in the database?

Yeah, sure.
Row locking works only inside a transaction (or at least MySQL is designed like that), you can lock a row in 2 ways:
shared: like a RwLock::read(), done appending "FOR SHARE" at the end of the query;
exclusive: like a RwLock::write(), done appending "FOR UPDATE" at the end of the query;

You can read official docs here: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-shared-exclusive-locks

From a quick search, I see PostgreSQL has many more lock types: https://www.postgresql.org/docs/9.1/explicit-locking.html

I'm still not familiar with your APIs, but being only an append to a select query I see it as a set of methods of sea_orm::query::Select, aside of the existing one, all and paginate, something like

fn one_lock(self, db: &DatabaseConnection, lock_tipe: LockType) -> Result<Option<E::Model>, DbErr>;
fn all_lock(self, db: &DatabaseConnection, lock_tipe: LockType) -> Result<Vec<E::Model>, DbErr>;
fn paginate_lock(self, db: &DatabaseConnection, page_size: usize, lock_tipe: LockType) -> Paginator<'_, SelectModel<E::Model>>;

[tqwewe]

Oh wow okay thats awesome thank you! I can't believe I've never come across MySQL/Postgres row locks before! Seems like a nice feature to have.
Btw I am just a strong user of SeaORM haha not a dev on it for now 😀.

[nappa85]

I think we can close this issue, since my PR has been merged, right?

[tyt2y3]

Well only the SeaQuery PR has been merged and since SeaORM has bumped the dependency, you can use it now.

It depends on whether you want additional convenience by introducing new API in SeaORM.

[nappa85]

Well only the SeaQuery PR has been merged and since SeaORM has bumped the dependency, you can use it now.

It depends on whether you want additional convenience by introducing new API in SeaORM.

Oh, you're right, it's the sea-query PR that has been merged, not the sea-orm one