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 ...
- 65.6k
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 ...
- 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 ...
- 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 ...
- 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 ...
- 151k
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 ...
- 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 ...
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 ...
- 65.6k
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 ...
- 151k
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
(
@...
- 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, ...
- 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 ...
- 92.7k
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 ...
- 1,824
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 ...
- 40.1k
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 ...
...
- 1,122
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 ...
- 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 ...
- 27.1k
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 ...
- 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 ...
- 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 ...
- 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 ...
- 92.7k
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 ...
- 65.6k
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 ...
- 22.8k
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 ...
- 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 ...
- 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 ...
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 ...
- 22.8k
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 ...
- 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 ...
- 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 ...
- 69.2k
Only top scored, non community-wiki answers of a minimum length are eligible