64 votes

How to rollback when 3 stored procedures are started from one stored procedure

Given only the code shown in the question, and assuming that none of the three sub-procs have any explicit transaction handling, then yes, an error in any of the three sub-procs will be caught and the ...
user avatar
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 ...
user avatar
  • 21.2k
42 votes

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

I know this is an old thread but I would say to a large degree snapshot isolation is a magic bullet. It will eliminate all of your blocking between readers and writers. It will however not prevent ...
user avatar
  • 601
31 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 ...
user avatar
  • 24.1k
30 votes
Accepted

Can I select data inserted in the same uncommited transaction?

Yes. Everything you did inside the same transaction is visible to later commands inside the same transaction. Just not to other transactions until committed. This is true for all isolation levels ...
user avatar
30 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 ...
user avatar
  • 21.2k
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 ...
user avatar
26 votes
Accepted

Are we required to handle Transaction in C# Code as well as in stored procedure

First, you should always have proper transaction handling in all of your procedures so that it does not matter if they are called by app code, by another procedure, individually in an ad-hoc query, by ...
user avatar
25 votes
Accepted

Avoid unique violation in atomic transaction

In addition to what @Craig provided (and correcting some of it): Effective Postgres 9.4, UNIQUE, PRIMARY KEY and EXCLUDE constraints are checked immediately after each row when defined NOT ...
user avatar
23 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 ( @...
user avatar
  • 67.1k
22 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, ...
user avatar
  • 68.1k
21 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 ...
user avatar
19 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 ...
user avatar
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 ...
user avatar
17 votes
Accepted

Transactions within a Transaction

What you would need is a so called "autonomous transaction" (a feature provided by oracle). At this point this is not possible in PostgreSQL yet. However, you can use SAVEPOINTs: BEGIN; INSERT ... ...
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 ...
user avatar
  • 69.2k
16 votes
Accepted

Why "SET LOCAL statement_timeout" does not work as expected with PostgreSQL functions?

The way statement_timeout works, the time starts counting when the server receives a new command from the client. Queries launches inside server-side functions are not commands from a client, they ...
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 ...
user avatar
  • 14.3k
15 votes

Transactions within a Transaction

You could try it yourself: WARNING: there is already a transaction in progress It starts no new (sub)transaction as nested transactions are not implemented in PostgreSQL. (You may do some magic ...
user avatar
  • 28.8k
15 votes
Accepted

Query data before a commit transaction

Yes, it's possible if you change the transaction isolation level for the session (that's what you call "window" in SSMS) that queries modified data. Often this is not such a great idea, as you might ...
user avatar
  • 2,633
15 votes
Accepted

ROLLBACK doesn't work after INSERT INTO newly created destination table

The reason is that that some statements, like CREATE TABLE cause an implicit commit. You can read about them in the documentation: Statements That Cause an Implicit Commit. So the original sequence ...
user avatar
15 votes
Accepted

Are explicit transactions needed in this while loop?

Individual statements -- DML, DDL, etc -- are transactions in themselves. So yes, after each iteration of the loop (technically: after each statement), whatever that UPDATE statement changed has been ...
user avatar
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 ...
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 ...
user avatar
  • 9,232
14 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 ...
user avatar
  • 51.8k
14 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 ...
user avatar
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 ...
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 ...
user avatar
  • 69.2k
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 ...
user avatar
  • 14.3k
13 votes
Accepted

Synchronization using triggers

1. Does the trigger follow the relational database's ACID principle? Is there any chance an insert might be committed but the trigger fail? This question is partly answered in a related question you ...
user avatar
  • 69.2k

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