Menu
According to msdn : the commit syntax is :
However when I omit the tran/transaction words - it does compile and run with NO errors.
How can it be working ?
Does it do something else instead ?
538
As long as you don't COMMIT or ROLLBACK a transaction, it's still "running" and potentially holding locks. If your client (application or user) closes the connection to the database before committing, any still running transactions will be rolled back and terminated.
A rollback command can only be executed if the user has not performed the COMMIT command on the current transaction or statement.
Sql server unlike oracle does not need commits unless you are using transactions. Immediatly after your update statement the table will be commited, don't use the commit command in this scenario.
Marks the end of a successful implicit or explicit transaction. If @@TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications since the start of the transaction a permanent part of the database, frees the transaction's resources, and decrements @@TRANCOUNT to 0.
The COMMIT in question is not actually COMMIT you think. See COMMIT WORK
COMMIT [ WORK ] [ ; ]
Remarks
This statement functions identically to COMMIT TRANSACTION, except COMMIT TRANSACTION accepts a user-defined transaction name. This COMMIT syntax, with or without specifying the optional keyword WORK, is compatible with SQL-92.
So COMMIT by itself is COMMIT WORK which is identical to COMMIT TRANSACTION.
Ditto for ROLLBACK [ WORK ]
After comment,
BEGIN TRANSACTION gbn
SELECT 1
COMMIT gbn -- fail
GO
BEGIN TRANSACTION gbn
SELECT 2
COMMIT TRAN gbn -- works
GO
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
