0

I would like to run some sql command as a transaction:

If one sql statement because of any reason doesn't work, the other statements should not affect the database. To achieve this aim I have written this block of code:

DECLARE @TransactionName VARCHAR(20)= 'Demotran1';
BEGIN TRANSACTION @TransactionName
-- This statement works 
alter table [SalesLT].[ProductModel]
add test315 int  
-- This statement doesn't work
alter table [SalesLT].[ProductModel]
add testtimestamp123 timestamp

rollback TRANSACTION @TransactionName

After running the above statement I have this expectation that nothing is happening to Table ProductModel This code works for me BUT after running it, for some minutes my whole database is somehow not accessible. My first question is, why the database after running this command behaves like this? and Do you have a better idea to achieve my aim?

Kaja
  • 2,962
  • 18
  • 63
  • 99
  • 1
    Alter table can take some time. Be patient. – jarlh May 05 '22 at 19:08
  • @PeterSmithit sounds good. but still have the problem that rolling back takes a long time – Kaja May 05 '22 at 19:12
  • @jarlhyou mean alter in rolling back in generall takes a long time? – Kaja May 05 '22 at 19:13
  • 4
    Yes, rolling back _can_ take a long time. In fact it can take a lot longer than the initial action because rollback is single-threaded. But the initial alter table can take a long time too, depending on what you're adding and what version you're using, it may have to write to every single existing page (and even create new ones). Rollback has to undo _all of that_. Also, use `rowversion` instead of `timestamp`, because it's not always clear to others whether you actually meant to add a date/time column. – Aaron Bertrand May 05 '22 at 19:13
  • 4
    In addition, sometimes a process (or a rollback) can seem to be _slow_ but in reality it's not moving at all - it can be waiting on some resource or there could be a lock on the row or page it's currently trying to change. So always check in another window what is _actually_ happening, e.g. `SELECT command, last_wait_type, blocking_session_id FROM sys.dm_exec_requests WHERE session_id = ;` – Aaron Bertrand May 05 '22 at 19:21
  • @AaronBertrand +1 on locking, as adding a nullable column is a metadata-only operation. – David Browne - Microsoft May 05 '22 at 20:10
  • @DavidBrowne-Microsoft Usually, yes. [Counter-example](https://rusanu.com/2012/02/16/adding-a-nullable-column-can-update-the-entire-table/). – Aaron Bertrand May 05 '22 at 20:12
  • Interesting. But probably not the case with `AdventureWorksLT.SalesLT.ProductModel`. – David Browne - Microsoft May 05 '22 at 20:19
  • @DavidBrowne-Microsoft For sure. But the OP and future readers won't always be dealing with a sample database. :-) – Aaron Bertrand May 05 '22 at 20:47
  • I strongly suggest you use `SET XACT_ABORT ON;` regardless of whether you use `TRY` `CATCH`. That will ensure a rollback always happens if there is an error. Also you can add multiple columns at once: `alter table SalesLT.ProductModel add test315 int, testtimestamp123 timestamp`. Also be aware that `timestamp` is an alias for `rowversion`, it does not represent dates – Charlieface May 05 '22 at 23:41

0 Answers0