CODEWITHSUNDEEP
sql-server
blizpasta
blizpasta

Reputation: 2604

SQL Server: Is there a need to verify a data modification?

After performing an insert/update/delete, is it necessary to query the database to check if the action was performed correctly?

Edit:

I accepted an answer and would like to use it to convince management.

However, the management insists that there is a possibility that an insert/update/delete request could be corrupted in transmission (but wouldn't the network checksum fail?), and that I'm supposed to check if each transaction was performed correctly. Perhaps they're hinging on the fact that the checksum of a damaged packet can collide with the original packet's checksum. I think they're stretching it too far, and in most likelihood wouldn't do it for my own projects. Nonetheless, I am just a junior programmer and have no say.

Upvotes: 2

Views: 87

Answers (3)

PiRX
PiRX

Reputation: 3565

You shouldn't. You can use SQL (or your programming platforms) built in error handling mechanism to see if there was errors so you can notify user that something bad happened, but otherwise all DB transactions follow ACID (as mentioned by Paul) which means that if something in batch fails, all batch is rolled back.

Upvotes: 0

Will A
Will A

Reputation: 24998

This isn't necessary - if the query completes successfully then the modification has been performed - if the query fails for whatever reason then the entire action will be rolled back for the query that failed if multiple queries are executed in a batch.

Depending on the isolation level that is being used, it's wholly possible that your modification is either superceded by modifications made by another query running 'at the same time' - whether this is important is down to what you're expecting to happen in this circumstance.

Upvotes: 0

Paul Sasik
Paul Sasik

Reputation: 81489

Shouldn't be. Commercial database inserts/updates/deletes (and all db transactions) follow the ACID principle.

Wiki Quote:

In computer science, ACID (atomicity, consistency, isolation, durability) is a set of properties that guarantee database transactions are processed reliably.

If you have the feeling that you need to double check the success of your transactions then the problem most likely lies elsewhere in your architecture.

Upvotes: 2

Related Questions