SqlHydra
SqlHydra copied to clipboard
SQL Lock Tables
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!
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()
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?
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()
]