Initial lock support

[nappa85]

Depends on SeaQL/sea-query#117, adds lock support to select statements

[nappa85]

Are there blockers that deny merging this PR?

[tyt2y3]

Sorry was busy. Yes given SeaQuery is upgraded it now can be merged.

I think these methods belongs to the QuerySelect trait (in query/helper.rs) where other Selects can use them too.

I am also thinking of whether we can provide a higher level abstraction over some common scenario?

[nappa85]

Ok for moving the methods.
I think what's missing is the transactions management, so start a transaction optionally with different isolation levels, committing it, reverting it, etc...

[tyt2y3]

I think transaction is highly driver specific. I don't think we can cleanly wrap and generalizes over different database+driver. Perhaps we can start by exposing some of the underlying types and methods from SQLx.

https://docs.rs/sqlx/0.5.7/sqlx/trait.Connection.html#method.transaction

[nappa85]

It can be a starting point, if we're able to maintain sea-orm typing.
What I mean is, sqlx Connection::transaction takes an fn(&mut Transaction), we would need to wrap it up so inside the function we can still use our DatabaseConnection type.

And it's kind of an overkill IMHO, Transaction is only a sugar wrapper over a normal connection, where a START TRANSACTION has been executed.

I would prefere an approach like this: https://docs.rs/mysql_async/0.28.0/mysql_async/struct.Conn.html#method.start_transaction
where Transaction is simply a tiny wrapper over Connection and can be used in any method that thakes &Conn because it impl Deref<Conn>

About isolation levels, mysql and postgres are quite similars:
https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html
https://www.postgresql.org/docs/9.5/sql-set-transaction.html

Sqlite has transactions too, it has a little different dialect, like BEGIN [TRANSACTION] instead of START TRANSACTION, and isolation levels have to be set using the PRAGMA keyword, like https://www.sqlite.org/pragma.html#pragma_read_uncommitted

[tyt2y3]

Thank you for the investigation. Since this PR is closed, I think it's better we move the discussion to a different thread to follow up.