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

2
votes

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.

3
sql-server
Sure, if there's a rollback after the INSERT/UPDATE/DELETE/TRAN – OMG Ponies
Wow! Honestly, being concerned about network transmission's potential impact on db transactions amounts to analysis paralysis. Is it theoretically possible? Yes, but no one should ever worry about that. Look at GUIDs as an example. There is potential for collision, an astronimically tiny one, and so one worries about it. I actually suggest posting a follow-up question on this ASAP (with a link back to this question). You ought to get some good insights. You might also want to bounty it to make sure it gets its due attention. – Paul Sasik

3 Answers

2
votes

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.

0
votes

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.

0
votes

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.