We are using our select statement inside transaction scope because of concurrency concerns. The question is, if I put my transaction in using statement, do I still have to call Commit() method explicitly to be sure that the transaction is closed or Dispose() method will do the job? Here’s example code: …
Tag: transactions
Returning a value before committing transaction and (UPDLOCK, HOLDLOCK)
I need to check if a record exists – if yes: then returns its id, if not: creates a new record and returns its id. I am using WITH (UPDLOCK, HOLDLOCK) in SELECT to prevent from duplicates (it creates lock). I wonder if I should commit the transaction if a record exists in a database for realising the lo…
Is start transaction always needed with Mariadb storedprocedure?
I have a mariadb database and a stored procedure. If a select statement is true then a delete query is done. Like this: My question is, do I need to place the start transaction at the beginning before the select statement or is the following possible? This second code doesn’t require a rollback and only…
Why does MySQL not lock rows while searching indexed column
For example, I lock some rows: c2 is not indexed. It means MySQL has to search entire table and if it read uncommitted or read committed isolation levels it places locks on every scanned row and if it doesn’t satisfy the WHERE condition it immediately release the lock. If it is repeatable read those loc…
PostgreSQL: deadlock without a transaction
I have a route (in a node JS app) that inserts and updates some data in a PostgreSQL database (version 13). In pseudo-code, here are all queries that are done in sequential order: On some instances of the app without that much traffic that writes on their own table, I have many deadlocks. I don’t unders…
SQL – on which context do SELECT…FOR UPDATE and UPDATE work?
I would like to have a question about data contexts on which do the transaction operate with different commands. Are there any differences in SELECT…FOR UPDATE and UPDATE in terms of contexts of data? What I mean by this: if I run the following transaction (pseudo-code): What this actually does is that …
How exactly transactions work in Microsoft SQL Server
I’ve read that creating for insert trigger creates transaction implicitly. So I can rollback transaction from within trigger and “undo” the insert. Is that so? create trigger TRIGGER_NAME …
Is it possible to lock on a value of a column in SQL Server?
I have a table that looks like that: Id GroupId 1 G1 2 G1 3 G2 4 G2 5 G2 It should at any time be possible to read all of the rows (committed only). When there will be an …
Create database transaction and commit/rollback later [closed]
Can we create database transaction and commit/rollback later. I mean we do not committing/rollback at the same machine/host/server. Let say we return the transaction and let other to decide to commit …
Why is my “CREATE INDEX CONCURRENTLY ..” command blocked by a “SELECT FROM” query? (Postgres 9.6)
I was running a migration to create an index, but the migration was blocked by another query. I resolved the problem after discovering that there was another query blocking my migrations; and after …