Hot answers tagged

14

The reason why this works in PostgreSQL is that the system catalogs are regular tables. So creating a new function, for example, just requires inserting a row into the pg_proc table, changing the default value of a column just requires making an update to some row in pg_attrdef, and so on. Since tables are transactional anyway, you'd almost have to go out ...


13

For SQL Server, you could argue that a commit operation is nothing more than writing LOP_COMMIT_XACT to the log file and releasing locks, which is of course going to be faster than the ROLLBACK of every action your transaction performed since BEGIN TRAN. If you are considering every action of a transaction, not just the commit, I'd still argue your ...


11

For Oracle, rollback can take many times longer than the time it took to make the changes that are rolling back. This often does not matter because No locks are held while the transaction is rolling back It is handled by a low priority background process For SQL Server I'm not sure if the situation is the same but someone else will say if it isn't... As ...


10

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


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! */ ...


8

Rollback isn't just "oh, never mind" - in a lot of cases it really does have to undo what it had already done. There is no rule that the rollback operation will always be slower or always be faster than the original operation. If you are waiting I suggest it is safest to just keep waiting.


8

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


7

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

Most don't? Bummer. I principally use SQL Server and it does. I know Oracle doesn't but I thought Oracle might be an aberration. In SQL Server, I'm quite certain you can run multiple DDL statements in a single transaction although I also think there's a couple of restrictions (which I have all forgotten). You can do a create or an alter or a drop of ...


7

Not all transactions will have their commit activity perform much better than their rollback. One such case is the delete operation in SQL. When a transaction deletes rows, these rows are marked as ghost records. Once a commit is issued and a ghost record cleanup task starts, then only are these records 'deleted'. If a rollback was issued instead, it just ...


6

Yes, this is possible. Most DDL statements can be rolled back in SQL Server (There are a few exceptions such as CREATE 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

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


5

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


5

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


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

Oracle has shared query parsing, so a SELECT * FROM table_a done by one session is (normally) the same as that of another session. That would break if one session thought there was ten columns in the table and another thought there were eleven.


5

In SQL Server we can rollback DDL statements, it's not using auto commit at the end of the statement. In other DBMS I don't know, but I remember that in Oracle one can't do the same. I believe it's specific to each DBMS, not sure what would the SQL standard say about this, but I'm sure no producer implements 100% the standard. There's a similar question on ...


4

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


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

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

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


4

If you talk about continuous integration then I assume it's a development environment. In that case I would say the person doing structural changes has to test them to ensure not to break things for others, much the same way someone updating a common library: Test in your own sandbox before commiting such changes. In a production deployment process you ...


3

For what it is worth, SLEEPING connections are usually representing a Client that has left a connection active, but is not doing anything. Therefore, the connection is sleeping. Usually pretty harmless. But a SLEEPING connection that is holding an OPEN transaction can (but not necessarily) lead to problems with other transactions or with the ...


3

It would be difficult to do that using Native tools or you will have to look for third party tools. Natively: Default trace Can be used to track Object Altered, Object Created and Object Deleted along with other stuff. Refer to the link below. Refer to : The default trace in SQL Server - the power of performance and security auditing Note this is ...


3

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


3

It looks like the order of the statements is causing the problem. In my old post row locking within ACID transaction innodb, I named 12 statements that break a transaction intermittently. In your particular case, it was the CREATE TABLE statement. Once you ran CREATE TABLE inside a START TRANSACTION ... COMMIT/ROLLBACK block, there was no framework to ...



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