question

sand143eep avatar image
sand143eep asked

ACID properties

Hello, i am unable to understand the Atomicity in Acid Properties, when i am executing a transaction consisting of different statements in which one of the update contains column which is not present. the transation ran success for all the other except the column which is not present.and all the other statements updated the table. here i got confusion as **Database modifications must follow an all or nothing rule** but here the remaining statement in the same transaction are success. can any one please help me to understand the atomicity with an example please.
sql-server-2008sql-server-2005sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
If you run your statements within a transaction, you also need to use some sort of error handlign, preferrably with TRY/CATCH, and in the CATCH-block you need to run a ROLLBACK in order to maintain atomicity of the transaction. Otherwise, SQL Server will run each statement. When a statement fails, it will move over to the next statement, and finally handle your COMMIT-statement, which will commit all the successful changes within your transaction. Conceptually it will something like this BEGIN TRY BEGIN TRAN; --statement 1 --statement 2 --statement 3 COMMIT; END TRY BEGIN CATCH ROLLBACK; --handle errors (throw-statements, logging of errors etc) END CATCH
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.