SQL Lock Tables

[SeanWilken]

Hello and thanks for this awesome library!

While we have been successful in utilizing it for setup, type generation and querying / inserting data into our database, we were wondering if there was any documentation or features that allow for the locking of a table when using SQLHydra.Query. Any insight on a feature set, documentation or configuration that may need to occur for this to be possible (if it even is or is planned) would be greatly appreciated :)

Thank you!

[JordanMarr]

You should be able to wrap your insert/update in a transaction with the appropriate IsolationLevel.

For example:

use ctx = openContext()
ctx.BeginTransaction(System.Data.IsolationLevel.Serializable)

try
    // do insert or update
    ctx.CommitTransaction()
with ex -> 
    ctx.RollbackTransaction()
    reraise()

[MangelMaxime]

Thank you for the pointer System.Data.IsolationLevel.Serializable does works for locking rows inside of a table when selecting them.

However, in our case we want to lock the whole table to prevent any insert in it. The way to do it with SQL is:

BEGIN TRAN;

select * from dbo.Users (TABLOCKX);

COMMIT TRAN;

How can we add (TABLOCKX) to the generated SQL? Is it possible to extends the CE?

[JordanMarr]

Yes, you can manually edit the underlying SqlKata.Query and then use the HydraReader manually.

For example:

/// Gets the query text from the underlying SqlKata.Query.
let toSql (query: SqlKata.Query) = 
    let compiler = SqlKata.Compilers.SqlServerCompiler()
    compiler.Compile(query).Sql

let addLock (sql: string) = 
    $"""
    BEGIN TRAN;
    {sql} (TABLOCKX);
    COMMIT TRAN;
    """

let getCustomers () = 
    let query = 
        select {
            for c in customerTable do
            select c
        }

    let sql = 
        query.ToKataQuery() 
        |> toSql
        |> addLock

    use conn = new SqlConnection("connection string...")
    use cmd = new SqlCommand(sql, conn)
    conn.Open()
    use! reader = cmd.ExecuteReaderAsync()
    let hydra = HydraReader(reader)

    return [
        while reader.Read() do
            hydra.``SalesLT.Customer``.Read()
    ]