61

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 ...


29

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 ...


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 ...


14

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 ...


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. ...


12

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;


8

For statements that are allowed to execute within an explicit transaction (i.e. BEGIN TRAN, or heaven forbid IMPLICIT_TRANSACTIONS is ON), I am only aware of the following two that will not be affected by a ROLLBACK (though technically both are somewhat "cheating" in a sense): DML statements against table variables (variable, even table variables, do not ...


6

Gap locks are necessary under the read committed (RC) isolation level to prevent potential integrity violations due to concurrent inserts -- this is what the documentation statement Gap locking is only used for foreign-key constraint checking and duplicate-key checking. implies. Suppose transaction T1, with the RC isolation, updates the value of a ...


5

Regarding visibility, SQL Server uses locking and/or row-versioning to implement isolation levels. Changes are always visible to the current session. Uncommitted changes are visible to other sessions only they are using the UNCOMMITTED ("daring they could get a dirty read"). Normally, other sessions will be blocked (due to locking) or will see the original ...


5

Bad idea code is just going to be more expensive to fix down the line. If there are blocking problems using explicit transaction (rollback/commit), point your DBA to the internet for some great ideas to address the issues. Here's a way to help alleviate the blocking: https://www.sqlservercentral.com/articles/using-indexes-to-reduce-blocking-in-concurrent-...


4

You can do a quick test to confirm this, I just ran a test out of curiousity, this is on sql server 2012. The current session will have visibility to a record that was previously inserted but not committed, however other sessions will not have visibility to that depending on their transaction isolation level. Example create table hs_test( id int ) begin ...


4

You can use Read Committed Snapshot Isolation (RCSI) on your database, which uses space in TempDB to keep track of committed versions of the data. Be aware that RCSI adds 14 bytes to every versioned row, so you might see some additional page splits in the database. It also requires that TempDB's space and I/O be monitored to make sure it's not slowing ...


4

A database is made up two files, a data file and a transaction log file. These are stored on disk. Each database has a log cache in RAM, when a transaction is committed it gets moved to the log cache waiting to be flushed to disk. Therefore temporarily when a transform has been committed and waiting to be flushed to disk, it is in memory, however ultimately ...


4

You can see for yourself using DMVs. In SSMS open two query tabs. Paste this into one: select * from sys.dm_tran_active_transactions select * from sys.dm_tran_locks Paste your statements into the other. Run my queries. This will show background activity on the server which can be ignored. Run just your BEGIN TRAN A; statement and re-run the DMV queries. ...


4

The question is invalid - either Transaction 4 committed before the system failure or it did not, but you've stated that it did commit, but then rolled back and your diagram suggests the transaction was still uncommitted at the point of system failure. If Tran 4 committed before the failure, then it was written to the transaction log and would be rolled ...


4

Below is a PowerShell example that uses the Microsoft.SqlServer.TransactSql.ScriptDom to parse procs and identify those with BEGIN TRAN statements. This version will download the assembly from NuGet if Microsoft.SqlServer.TransactSql.ScriptDom.dll doesn't already exist in the specified location. You could use a package manager instead for that task. param ( ...


4

The problem you're facing is down to how the TRY...CATCH construct handles errors - it consumes them, eats them, and spits nothing back out except for a 0 return code. This is evidenced by a very innocuous line in the docs on TRY...CATCH that says Errors trapped by a CATCH block are not returned to the calling application. If any part of the error ...


4

The fake transaction strategy is dangerous because it allows concurrency issues that transactions specifically prevent. Consider that in the second example any of the data might be changed between statements. The fake transaction deletes are not GUARANTEED to run or succeed. If the database server turns off during the fake transaction, some but not all of ...


4

This is not a programming issue, rather it is an interpersonal/miscommunication issue. Most likely your "DBA" is worried about locks, not transactions. The other answers already explain why you have to use transactions... I mean that's what RDBMS do, without properly used transactions there is no data integrity, so I'll focus on how to solve the real ...


4

My understanding is that this type of lock is during the process of identifying the range where the newly inserted key should be placed (I'm assuming that's what 'test the range' means). After this happens the lock is released, the new key gets inserted and an X lock is placed on it. RangeI-N lock is requested to test that insertion into key range ...


4

It depends on the transaction isolation level. In read committed it will release locks. In "Repeatable read" and "Serializable" it will hold locks. You can find more info in the following article: SET TRANSACTION ISOLATION LEVEL


3

The problem is that the HOLDLOCK is creating the "Shared" lock (the Mode: S locks) on that resource for the duration of the transaction. This does not prevent other processes (such as the same proc executed in another session) from placing their own "Shared" lock on the same resource. But then both sessions get to the UPDATE statement which is trying to ...


3

Your understanding is fundamentally correct. What you didn't realize is that there can be false positive results when with the SERIALIZABLE isolation level. The documentation says: While PostgreSQL's Serializable transaction isolation level only allows concurrent transactions to commit if it can prove there is a serial order of execution that would ...


3

Option A is the correct choice. It is possible for all statements in a transaction to work and then the actual COMMIT to fail, so you keep the COMMIT inside your TRY block so that any failure of the COMMIT will be caught and you can gracefully handle this error and rollback. See this answer on SE. In your example code, if we assume other users are running ...


3

documentation means: if you have permanent table TABLE_A and you create a temporary table with the same name TABLE_A, a permanent table will be not visible for your code. But you could request data from the permanent table by add schema to name - schema_name.table_name. Answering to your question - yes, it is safe.


3

You can use DROP TEMPORARY TABLE instead of TRUNCATE, then CREATE TEMPORARY TABLE(...) again, it will not commit the transaction but you gain the speed of TRUNCATE statement. "CREATE TABLE and DROP TABLE statements do not commit a transaction if the TEMPORARY keyword is used" Statements That Cause an Implicit Commit


3

Below are relevant excerpts from the SET TRANSACTION ISOLATION LEVEL documentation. Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed. All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the ...


3

That's the whole idea of MVCC - Multi-Version-Concurrency-Control. Whenever data is being modified while there are other active transactions reading it, or have read it previously (depending on the reader transaction's isolation level), a copy of the pre-modified data is set aside for these transactions so as not to block them. This copy is kept until all ...


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