MySQL Forums
Forum List  »  Stored Procedures

Re: Stored Procedure inserting duplicate Rows
Posted by: Peter Brawley
Date: July 14, 2022 11:41AM

1 Without DDL for all referenced tables, it's not possible to evaluate this sproc more than superficially (see $7 below)

2 To debug the sproc, you'll need to add in code stubs that track the values that are being duplicated

3 You'll also need to similarly debug the called sproc for which code is not posted

4 The logic looks incomplete, eg (i) error trapping and reporting is inadequate (ii) several contingencies can be imagined causing a SELECT or UPDATE to fail, but the code covers no such possibilities

5 Absolutely this logic needs to be encapsulated in a transactional block with rollback and retries to handle deadlocks

6 Also all entity and foreign key constraints need to be tracked and errors related to them need to be reported out eg using SIGNAL

7 Atomicity is one of Codd's 12 basic rules of relational databases, if you're unfamiliar with it, please read a primer ag https://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf



Edited 3 time(s). Last edit at 07/15/2022 09:23PM by Peter Brawley.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Stored Procedure inserting duplicate Rows
52
July 14, 2022 11:41AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.