I was reading through the BNF grammar for transaction statements and saw that transaction modes can be specified directly in the START TRANSACTION statement. Is there a difference in specifying transaction modes in START TRANSACTION vs SET TRANSACTION statements? Answer No difference. The SET TRANSACTION sett…
Tag: transactions
What are the differences between QueryRow and Exec in Golang SQL package?
In Golang SQL package there are QueryRow and Exec for executing query. If I’m executing insertion query inside transaction, which is better to be used in term of performance? err = tx.QueryRow(query, …
Oracle: When are constraints checked?
In my understanding constraints should generally be checked at the end of a transaction. So, in a simple example where we have one table A with the only column P, which is its primary key, and a table B with the primary key P and the foreign key F on A.P, the following should work (on empty tables): However, …
avoiding write conflicts while re-sorting a table
I have a large table that I need to re-sort periodically. I am partly basing this on a suggestion I was given to stay away from using cluster keys since I am inserting data ordered differently (by time) from how I need it clustered (by ID), and that can cause re-clustering to get a little out of control. Sinc…
Why does SQL Server read uncommitted data when the default isolation level is READ COMMITTED?
You have a dbo.inventory table and the itemsInStock is 10. Let’s say you run this query: (and you don’t commit the transaction) Why is SQL Server reading a 5 if it hasn’t been committed yet and the default isolation is read committed? Answer Obviously a transaction needs to see everything it…
How to know if an uncommitted transaction tries to insert a specific unique key into SQL
I’m writing a programm which inserts data to a MariaDB-Server and can be used by different people on the same time. The transactions take some time, so the following problem might occur: Person A …
Deploying multiple SQL jobs in the same .sql file
So this is something I already do with Stored Procedures, and a bunch of other database items, and now I’m trying to do it with jobs. I write a bunch of items to a single .sql file. Other programs I …
Are changes made to DB only through transactions?
I am not able to get a clear complete understanding regarding the role of transactions in databases. I know operations clubbed in a transactions will be executed together and then either committed or …
What’s the point of BEGIN TRANSACTION; … ROLLBACK;
I understand that a transaction will automatically roll back whenever an error occurs. I read up on this for T-SQL from Microsoft and I found this syntax: BEGIN TRANSACTION; STATEMENT1; …
Multiple batches in a SQL transaction
I want to create several stored procedures (or user-defined functions) within a transaction. CREATE PROCEDURE statement must be the only statement in the batch, so I have to use following template: …