63
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 ROLLBACK in the CATCH block will roll back all of the work.
BUT here are some things to note about transactions (at least in SQL Server):
There is only ever ...
63
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 transaction, which is implicitly committed (or rolled back) after the statement completes (or fails).
Transaction simulation suggested by the person who ...
40
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 writers from blocking other writers. There is no way around that.
In my experience, the additional load on the TEMPDB is negligible and the benefits of row ...
31
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 an excerpt from the ROLLBACK WORK doc page:
ROLLBACK [ WORK ] [ ; ]
This statement functions identically to ROLLBACK TRANSACTION except
that ROLLBACK ...
30
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 are given more or less immediately, or at least as soon as they are available. Batch processing would collect requests into, well, batches, and execute them on ...
26
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 a SQL Agent job, or some other means. But single DML statements, or code that doesn't make any modifications, doesn't need an explicit Transaction. So, what I ...
26
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 except Read uncommitted where "dirty reads" are possible (but that does not affect your question per se).
It's implemented with the MVCC model (Multiversion ...
answered Apr 22 '15 at 18:24
Erwin Brandstetter
138k17 gold badges328 silver badges474 bronze badges
25
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 DEFERRABLE. This is different from other kinds of NOT DEFERRABLE constraints (currently only REFERENCES (foreign key)) which are checked after each statement. We worked ...
23
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 this statement (although I think could possibly get a deadlock). But, somehow, it IS getting one.
It definitely IS possible. This is due to your isolation level ...
answered Aug 4 '17 at 18:51
Sean Gallardy - Retired User
21k2 gold badges34 silver badges65 bronze badges
21
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
(
@updateAuthor varchar(100)
, @assignedUser varchar(100)
, @ticketID bigint
)
AS
BEGIN
BEGIN TRANSACTION;
SAVE TRANSACTION MySavePoint;
SET @...
21
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, the other connection proceeds. In all cases, the column is soon decremented by 11.
A third user may see the value decremented by 0 or 4 or 7 or 11. The "very ...
18
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 not CHECK constraints.
So the unique partial index (and the implicit constraint it implements) will be checked at every statement (and in fact after every row ...
18
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 spid 61:
if @@TRANCOUNT > 0 rollback
go
begin transaction
select *
into ##t
from sys.objects
declare @bind_token varchar(255);
exec sp_getbindtoken @...
answered Jan 31 '19 at 20:39
David Browne - Microsoft
35.6k3 gold badges38 silver badges79 bronze badges
17
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 DATABASE dba210949;
USE dba210949;
CREATE TABLE user_chats
(
id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
created_at TIMESTAMP DEFAULT ...
17
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 exception is Deferred Name Resolution but that applies to tables, not columns:
Deferred name resolution can only be used when you reference nonexistent table ...
16
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 ...
SAVEPOINT a;
some error;
ROLLBACK TO SAVEPOINT a;
COMMIT;
It is not entirely an autonomous transaction - but, it allows you get "every transaction" right. You ...
15
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 don't reset that timer or push a new one onto a stack of timers.
This is why SET LOCAL statement_timeout = 100; has no effect.
And if a function does SET ...
15
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 get some unexpected results. Consider the side effects carefully. I have no idea if it's possible to change the transacion isolation level in the Excel Power ...
15
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 of statements:
START TRANSACTION
SHOW TABLES LIKE customers
CREATE TABLE `customers__20150119_14_08_20` LIKE `customers`
INSERT INTO `...
15
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 auto-committed.
Of course, there is always an exception, right? It is possible to enable Implicit Transactions via SET IMPLICIT_TRANSACTIONS, in which case the ...
15
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 status values. No big deal.
Most programs will do actual work within the transaction and this is another matter. The point of a transaction is so you can be sure ...
15
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 hardened to disk. Specifically, once it has been hardened to the transaction log file, control can be returned.
The data, at this point, may not be hardened into ...
15
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 that happen.
As you mention, you can make that happen through TRY...CATCH, which gives you the most control over if and how you can rollback.
It sounds like you ...
14
A connection from the pool will have the isolation level set by the last client to use that connection. Yes, it really is that scary.
The long and the short of it is that if you change the isolation level of a connection you must explicitly set it back to READ COMMITTED before closing. Better is to explicitly declare your required isolation level at the ...
14
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 in a pl/pgsql function, for example, that mimics that behaviour, though.)
With PostgreSQL 11, one could think the new real stored procedures and their ability ...
14
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 processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY...CATCH will handle the error.
...
14
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 current balance as ₽1100, adds ₽100, and writes ₽1200
Or the other way around (T2 then T1). The important point is that both increments of ₽100 are applied.
...
14
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 desire to undo everything. If everything is going as planned, then you should always COMMIT.
Why?
Issuing a ROLLBACK will result in SQL Server undoing any work that ...
13
when I explicitly lock a row or a table, am I employing the exact same constructs that are used by the database's transaction facilities under the covers to make the transaction work properly?
Yes. If that would not be true, then your own 'locking' would only be scoped to other similar 'locking' and not interact with the engine own locking. So you would ...
13
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 linked to. Trigger code is executed in the same transactional context as the DML statement that caused it to fire, preserving the Atomic part of the ACID ...
Only top voted, non community-wiki answers of a minimum length are eligible