Skip to main content
63 votes

Asked to Not Use Transactions and to Use A Workaround to Simulate One

You can not not use transactions in SQL Server (and probably any other proper RDBMS). In the absence of explicit transaction boundaries (begin transaction ... commit) each SQL statement starts a new ...
mustaccio's user avatar
  • 26.2k
33 votes

What exactly is the 'online' in OLAP and OLTP?

It is simply a remnant of olden times, when it was used in contrast to batch processing. "Online" here means "interactive", that is, requests to the database are processed as they come and responses ...
mustaccio's user avatar
  • 26.2k
32 votes
Accepted

Is this an error in SQL Server's documentation for ROLLBACK?

The ROLLBACK TRANSACTION documentation is correct, albeit not clear without additional context. ROLLBACK is shorthand for the similar ROLLBACK WORK statement rather than ROLLBACK TRANSACTION. Below is ...
Dan Guzman's user avatar
  • 28.3k
28 votes

How is this SQL Server PK violation possible?

Now I have a SQL statement, which selects the MAX value, adds to it, and INSERTs that value into the same table -- all inside one statement. So in theory, it's not possible to get a PK violation on ...
Sean Gallardy's user avatar
26 votes
Accepted

Transaction in a stored procedure

You need to wrap that code in CREATE PROCEDURE ... syntax, and remove the GO statements after BEGIN TRANSACTION and before COMMIT TRANSACTION. GO CREATE PROCEDURE dbo.AssignUserToTicket ( @...
Hannah Vernon's user avatar
  • 70.2k
25 votes
Accepted

Deferrable unique index in postgres

A index cannot be deferred - doesn't matter if it is UNIQUE or not, partial or not, only a UNIQUE constraint. Other types of constraints (FOREIGN KEY, PRIMARY KEY, EXCLUDE) are also deferrable - but ...
ypercubeᵀᴹ's user avatar
25 votes
Accepted

MySQL: will a transaction lock the row?

A single statement like that works the same with MyISAM or InnoDB, with a transaction or with autocommit=ON. It blocks enough to do the query, thereby blocking the other connection. When finished, ...
Rick James's user avatar
  • 78.5k
20 votes
Accepted

PostgreSQL: How does COPY FROM behave inside a transaction?

Does COPY FROM create a subtransaction? No. It creates a single transaction if not run in a transaction; otherwise it will use the current transaction. Will a failure roll back the parent ...
Craig Ringer's user avatar
  • 56.7k
20 votes
Accepted

Why does this query result in deadlock?

The FOREIGN KEY user_chat_messages_user_chat_id_foreign is the cause of your deadlock, in this situation. Fortunately, this is easy to reproduce given the information you've provided. Setup CREATE ...
Willem Renzema's user avatar
20 votes
Accepted

What is the point of TRY CATCH block when XACT_ABORT is turned ON?

You are right that it is not necessary to catch errors you are not intending on handling. SET XACT_ABORT ON; ensures a rollback in all circumstances (except for a couple of very weird edge cases of ...
Charlieface's user avatar
  • 13.3k
18 votes
Accepted

How to inspect global temp table from outside the transaction it is created in

File this under "just because you can, doesn't mean you should" If you generate a bind token in the first session, and somehow publish it, you can join its transaction from another session. Eg from ...
David Browne - Microsoft's user avatar
17 votes
Accepted

Does SQL Server allow (make visible) DDL inside a transaction to the transaction prior to commit?

Generally speaking, no. SQL Server compiles the whole batch at the current scope before execution so referenced entities have to exist (statement-level recompilations may also happen later). The main ...
Paul White's user avatar
  • 86.3k
16 votes
Accepted

Why am I getting a deadlock for a single UPDATE query?

Your statement modifies several rows. Each of these rows is locked when it is updated. It is well possible that a statement in a concurrent transaction has already locked one of these rows, blocking ...
Laurenz Albe's user avatar
  • 53.4k
16 votes

Why am I getting a deadlock for a single UPDATE query?

Laurenz explained the mechanism that can lead to deadlocks, and you already included a link yourself to a more detailed explanation by Kevin: Deadlocks in PostgreSQL when running UPDATE Here are ...
Erwin Brandstetter's user avatar
16 votes
Accepted

Why is part of transaction committed when stored procedure faults?

Transactions don't automatically roll back on error--that's not what they are designed to do. They are designed to give you the ability to rollback. However, you still need to do something to make ...
AMtwo's user avatar
  • 16.2k
15 votes
Accepted

What is the effect of having an opened transaction in MSSQL for too long?

Having an open transaction by itself will have almost no consequence. A simple BEGIN TRANSACTION -- wait for a while, doing nothing -- wait a bit longer COMMIT will, at worst, hold a few bytes of ...
Michael Green's user avatar
15 votes
Accepted

Difference between a trigger and a transaction

Comparing TRIGGERs and TRANSACTIONs is like comparing apples and oranges! No, wait, it's not, because apples and oranges are similar - they are both fruit and can both be eaten. It's more like chalk ...
Vérace's user avatar
  • 30.2k
15 votes

Concurrent transactions result in race condition with unique constraint on insert

The root of the problem is that, with default READ COMMITTED isolation level, each concurrent UPSERT (or any query, for that matter) can only see rows that were visible at the start of the query. The ...
Erwin Brandstetter's user avatar
15 votes
Accepted

How to use variables in procedure (not function)?

SQL functions or procedures (LANGUAGE sql) do not have a DECLARE section - nor BEGIN / END. These are keywords for the block structure of the procedural language PL/pgSQL - in functions and procedures ...
Erwin Brandstetter's user avatar
15 votes

transaction log in RAM or physical file?

You can find a pretty comprehensive guide to this question here, but to summarise, SQL Server will not return control to the application that committed a transaction until that transaction has been ...
HandyD's user avatar
  • 9,982
14 votes

Asked to Not Use Transactions and to Use A Workaround to Simulate One

There are some errors which are so severe that the CATCH block is never entered. From the documentation Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task ...
Michael Green's user avatar
14 votes
Accepted

Lost Update Understanding

There are two separate transactions (T1 and T2) that each add ₽100 to the customer's balance. The intended outcome is: T1 reads the current balance as ₽1000, adds ₽100, and writes ₽1100 T2 reads the ...
Paul White's user avatar
  • 86.3k
14 votes
Accepted

Commit or rollback SQLServer-SQLTransaction when no data changed

My advice Think about it the other way around. Only issue a ROLLBACK if you have a reason to undo everything since the start of the transaction--such as an exception, a bad state, or an explicit ...
AMtwo's user avatar
  • 16.2k
13 votes
Accepted

Does a single rollback affect all transactions?

Yes commit and rollback behave differently in this respect. Commit just decrements the @@trancount. Nothing is actually committed until that reaches zero. Rollback will always rollback the ...
Martin Smith's user avatar
  • 84.9k
13 votes
Accepted

How is this SQL Server PK violation possible?

Your theory is wrong. Reading from your "identity" table doesn't lock it, the insert into it locks it. While they are part of a single compound statement, they are still seperate actions. Two (or ...
jmoreno's user avatar
  • 1,065
13 votes
Accepted

Why can SELECT see updated row before commit?

Assuming the second SSMS window is using the default READ_COMMITTED isolation level, you will see the before value instead of being blocked by the uncommitted transaction if the database has the ...
Dan Guzman's user avatar
  • 28.3k
13 votes
Accepted

ROLLBACK after COMMIT

Generally, a transaction is exactly one of committed rolled back Committed transactions are never rolled back. It's how all RDBMS operate, on ACID principles Now, there are some different cases ...
gbn's user avatar
  • 69.9k
13 votes
Accepted

ERROR: current transaction is aborted, commands ignored until end of transaction block SQL state: 25P02

This error occurs when a previous query has failed and the client still issues queries in that transaction. The only thing to do in that state is a ROLLBACK. It's not particularly tied to deadlocks or ...
Daniel Vérité's user avatar
12 votes

What risks are there if we enable read committed snapshot in sql-server?

We are using SNAPSHOT ISOLATION in all of our projects that use SQL Server DB. No more 1205 SQL Errors, that are caused not because of wrong application code, but from default page locking and row ...
Alexander Nemsadze's user avatar
12 votes
Accepted

At what point does a database update its indexes in a transaction?

I work with SQL Server and Oracle. There are probably some exceptions, but for those platforms the general answer is that data and indexes will be updated at the same time. I think that it would be ...
Joe Obbish's user avatar
  • 32.5k

Only top scored, non community-wiki answers of a minimum length are eligible