Tag Info

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


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.


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


7

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


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


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


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

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


5

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


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

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

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


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


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

Yes the rollback transaction would rollback all changes made inside the stored procedure except if the stored procedure was itself to issue an unbalanced COMMIT which would raise an error Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. but still lead to the transaction being committed. Both cases shown ...


3

Well I think that having a test system that supports the workload, data and edge cases you have in production should mitigate the need to roll back. If you can properly test the deployment in a similar system, you should have very few scenarios where you have to roll back. That said, you can also do many things to make your code changes backward compatible. ...


3

Things are a bit simpler imho. Your catch block is not invoked because your code never executes. SQL Server receives a block of text: blah blah blah blah blah blah It then tries to make sense of it so it parses it, which transforms it into a syntactic tree, but it still not yet runnable. To make it runnable it needs to compile it and at this stage it ...


3

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


3

Don't use DDL triggers. Use Event Notifications instead. Event Notifications convey the very same data as DDL triggers and occur on the very same events, but they are asynchronous and loosely coupled. You get notified only after the original DDL committed. If the DDL rolls back, you don't get any notification. It seems to be exactly what you need.


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


2

See if this helps. I would take a backup right away because if vacuum cleans out the deleted row it's gone and you won't have any chance to recover it. Recover Deleted Row


2

From the MySQL Documentation •--base64-output[=value] This option determines when events should be displayed encoded as base-64 strings using BINLOG statements. The option has these permissible values (not case sensitive): ■ AUTO ("automatic") or UNSPEC ("unspecified") displays BINLOG statements automatically when necessary (that is, for ...


2

No, this type of delete generates roughly the same quantity of undo regardless of whether it is an IOT or heap. In contrast, inserting into the IOT generates far more undo. heap: drop table foo; create table foo( id integer not null, val char(100) default 'A' not null); -- insert into foo(id) select level from dual connect by ...



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