17

The actual query wasn't executed in an explicit transaction though, can that explain the existence of the target table? Yes, exactly so. If you do a simple select into outside of an explicit transaction, there are two transactions in autocommit mode: the first creates the table and the second fills it up. You can prove it to yourself this way: In a ...


16

My Recovery model is set to simple In short, there is nothing you can do but learn from this lesson that important databases should use the full recovery model (along with proper restore plans). Can i use the logfile for this Since it's in the simple recovery model, most likely not. Is 'ROLLBACK' another option ? Only if the transaction is currently ...


15

You're correct, the SELECT...INTO command is not atomic. This wasn't documented at the time of the original post, but is now called out specifically on the SELECT - INTO Clause (Transact-SQL) page on MS Docs (yay open source!): The SELECT...INTO statement operates in two parts - the new table is created, and then rows are inserted. This means that if the ...


13

Yes, this is possible. Most DDL statements can be rolled back in SQL Server (There are a few exceptions such as CREATE DATABASE)


12

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


11

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 where it may look like this rule has been broken. But it hasn't. Before we look at these cases though, different user sessions do not share a connection. Each ...


10

BEGIN TRANSACTION BEGIN TRY ALTER TABLE1... ALTER TABLE2... -- Additional data/structural changes COMMIT END TRY BEGIN CATCH ROLLBACK; THROW; -- Only if you want reraise an exception (to determine the reason of the exception) END CATCH


10

SQL Server does not de-escalate the lock. I investigated using a "Numbers" table with 100,000 rows. Empirically, updating 5,000 rows produced a corresponding number of RID locks in sys.dm_tran_locks. Updating a further 10,000 rows caused escalation to a single table lock. This was consistently reproducible. To minimise the objects involved the table was a ...


9

The answer, at least on 11.2, is "It depends": This create is rolled back: create trigger trig_foo after create on schema begin raise_application_error(-20001, 'Dont do it!'); end; / -- create table foo as select level as id from dual connect by level<=10000; /* SQL Error: ORA-00604: error occurred at recursive SQL level 1 ORA-20001: Dont do it! */ -- ...


9

Yes this can be done but you need a third party acting as a transaction coordinator. The standard protocol for this is called Two Phase Commit (2PC). This is usually done with a transaction manager acting as the coordinator. This can also be generalized further to more than two databases. In fact it doesn't even have to be databases as the approach is ...


9

For many people, the MySQL Achilles' heel is implicit commit. According to Page 418 Paragraph 3 of the Book the following commands can and will break a transaction ALTER TABLE BEGIN CREATE INDEX DROP DATABASE DROP INDEX DROP TABLE RENAME TABLE TRUNCATE TABLE LOCK TABLES UNLOCK TABLES SET AUTOCOMMIT = 1 START TRANSACTION SUGGESTION When it comes to MySQL,...


9

As per docs online: Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection. https://docs.microsoft.com/en-us/sql/t-sql/functions/trancount-transact-sql This is a connection level variable. If there were transactions open for connections other than the one your colleague ran his query on, there could have been ...


8

There is nothing you can really do because a rollback is being done via the UNDO tablespace inside ibdata1, which should have grown immensely. If you kill the mysqld process and restart mysql, it will just pickup where it left off as part of the crash recovery cycle. DISCLAIMER : Not Responsible for Data Loss What you could do may result in data loss for ...


7

When a MongoDB instance gets into a Rollback state, and the rollback data is greater than 300MB of data, you have to manually intervene. It will stay in a rollback state until you take action to save/remove/move that data, the (now secondary) should then be resynced to bring it back in line with the primary. This does not have to be a full resync, but that ...


7

To add to what @RLF provided in his answer; the following will also result in transaction rollback. ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE; will rollback any open transactions. Disconnection by the client, or server, or any bit of infrastructure in between. If the underlying disk subsystem goes away, all open connections to the database ...


6

A savepoint is a point within the current transaction. A DDL statement like CREATE TABLE, TRUNCATE, ALTER TABLE or DROP TABLE will issue an implicit commit before and after the statement runs that will end the current transaction. Once the current transaction ends, you can no longer rollback to a savepoint defined within that transaction. Potentially, you ...


6

@@TRANCOUNT reports a count of BEGIN TRANSACTION statements, not active transactions. From a different perspective, it is reporting the depth of a nested transaction. @@TRANCOUNT (Transact-SQL) Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection. [source] Misunderstandings of nested transactions and ...


6

Many alterings in one transaction with rollback and commit - it is not a dream. It is possible. Here is a scaffold for your script (following MS guidelines with improvements): BEGIN TRANSACTION BEGIN TRY -- place your script in this TRY block -- your DDL instructions: ALTER TABLE1... ALTER TABLE2... -- data modifications: EXEC(' ...


6

@MaxVernon already gave all the info you need. Just some addition from my side. The bigger the transaction the harder the (pre)calculation for the SQL Server. That's why your remaining time in seconds may increase while the process runs. After running a query the SQL Server can't grant more memory to the process. For big calculations your tempdb will most ...


6

I want to understand if there is a possibility that transaction being run via one application can affect the ones from an independent other application. Directly, no. Each transaction is by definition is a "single unit of work" and is local to itself. That is, my transaction cannot become part of your transaction. I can't explicitly tell yours to roll ...


5

The delete statement without the where clause delete all rows in the table without change of table structure. If the delete statement is within a transaction, then it can be rollback before the transaction is committed. If the delete transaction has been committed, the deleted transaction can't be rollback. Unless use of third party tool or restore the log ...


5

Not all are. PostgreSQL takes no more time to roll back than to commit as the two operations are effectively identical in terms of disk I/O. I don't actually think this is a question of being optimized for commit so much as it is a question of what other queries one is optimizing for. The basic question is how you address on-disk layout and how this ...


5

In theory you could do something as like wrap the script execution in a transaction. You can execute the .sql batch files from an application library like dbutilssqlcmd or SMO's ServerConnection.ExecuteNonQuery which handles the GO batch separator or sqlcmd extensions in the script. However in practice this is nearly impossible to do. Wrapping a script in a ...


5

CREATE TRIGGER [DDL_PreventLoginDrop]ON ALL SERVER FOR DROP_LOGIN AS DECLARE @LoginName NVARCHAR(128),@eventData XML SET @eventData = EVENTDATA() SELECT @LoginName=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME') IF @LoginName IN ('Login name that you want to protect') BEGIN PRINT 'Gotha, you nasty application!' ...


5

The answer really depends largely on your isolation level, what type of locks are held by the transaction being rolled back, and what your non-rollback session is trying to do. Step 1) What is locked? Before the ROLLBACK was issued, that transaction had done work and acquired locks. You can see details on those locks by looking in sys.dm_tran_locks: ...


5

kill 61 with statusonly. SQL tells me that,Status report cannot be obtained. Rollback operation for Process ID 61 is not in progress It seems Kill With statusonly reports ,only when the session is being killed manually From Paul Randal :(Emphasis mine) KILL WITH STATUSONLY generates a report only if the session ID or UOW is currently being rolled back ...


5

If you kill it, you'll have to roll your progress so far back, which could take a very long time. Rollbacks are single threaded. You could be waiting much more than 14 hours for that to happen. You can't resume a suspended task. Suspended is one part of a task life cycle in SQL Server. It means the task is running, but waiting on a resource. In your case, ...


4

The error is happening because the error being thrown part of a recompile error due to deferred name resolution. Looking at SQL BOL those aren't trapped when they happen at the same level as the try...catch. However, if it's happening at a different level, either as dynamic SQL or a SP call, then it will get caught and rolled back. Using Profiler you can ...


4

The reason you're seeing this result is that SQL Server is not actually catching your ALTER TABLE error. You'll notice that when you run this, you see the red error message rather than a printed line--you can verify this by changing print @@error to something like print 'HELLO!'; in that case, you will NOT see 'HELLO!' printed; you will see the error ...


4

SSIS Transaction Level The default transaction level for all tasks and containers is Supported which means they will enlist in an transaction if available but they will not create one.. A Required settings will start a transaction if one does not exist and enlist in an existing transaction. NotSupported indicates that the Executable/Container will ignore ...


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