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 ...
- 26.6k
34
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 ...
- 26.6k
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 ...
- 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 ...
- 34.7k
26
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 ...
- 98.5k
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, ...
- 78.9k
21
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,969
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 ...
- 57k
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 ...
- 13.9k
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 ...
- 47.3k
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 ...
- 88.1k
16
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 ...
- 180k
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 ...
- 54.8k
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 ...
- 180k
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 ...
- 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 ...
- 25k
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 ...
- 30.3k
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 ...
- 180k
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 ...
- 10.1k
14
votes
Accepted
How to check if the current connection is in a transaction?
A simple way is to compare now() to statement_timestamp().
now() gives the current date and time (start of current transaction).
statement_timestamp() gives the current date and time (start of ...
- 1,224
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 ...
- 25k
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 ...
- 88.1k
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 ...
- 16.2k
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 ...
- 1,075
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 ...
- 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 ...
- 70k
13
votes
Does SQL Server allow (make visible) DDL inside a transaction to the transaction prior to commit?
Is this what you're looking for?
BEGIN TRANSACTION;
ALTER TABLE foo ADD b varchar;
EXEC sp_executesql N'UPDATE foo SET b = CAST( a AS varchar )';
ALTER TABLE foo DROP COLUMN a;
COMMIT;
- 32.6k
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 ...
- 31.9k
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 ...
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 ...
- 32.6k
Only top scored, non community-wiki answers of a minimum length are eligible