Check select results in seconds

[newbie14]

Dear All,
I have data coming into my db at quite a high frequency. Once I recieve the data I have many different db operation (select, update)checking and one final insert. Before inserting I will check it exist before or not? The problem at times the similar data comes apart just few seconds. So I am worried the checking will miss because another insert have not been completed yet? Any suggestion?

[smantscheff]

That is what transactions are for. Bracket your check and insert procedure in a transaction which you can roll back in case of duplicate values.

[newbie14]

Dear Smantscheff,
My worry is before inserting into the db I will do other processing where I also will send email once I see certain data. So if I miss the check and double emails will be send. I have put everything in transaction but the email I could not put in transaction right?

[smantscheff]

1) Start the transaction.
2) Do your processing and send a flag if a notification must be sent.
3a) Commit the transaction OR
3b) Unset the notification flag and roll back the transaction.
4) Check if the notification flag is set and sent the email accordingly.

[newbie14]

Dear Smantscheff,
Do you think to be safer decide about sending the email towards the end after the last insert is it? Will that be safer ?

[smantscheff]

Either you send a notification email or you don't. If you do depends on the logic of your program. This has nothing to do with "safety" - if it has, your algorithm is flawed.

[newbie14]

Dear Smatcheff,
So where best to call the email function based on your experience. I do not want where suddenly in between I call my email function but then whole transaction failed and got rollback. So this is my worry?

[smantscheff]

Any function can result in an error. Your code has to deal with that possibility.
Without knowing your algorithm or seeing some pseudo-code of it I cannot help you any further.

[newbie14]

Dear Smantscheff,
My algo is as below

1. Read string
2. Break the string
3. Check(Does a select statement) if is link to any associate or not
4. If link to associate check in geo fence (Does a select statement) and if found in a geo fence does a Insert Statement
5. IF the broken string have some event alert message then do another Insert
6. If there is geo fence and or even alert then send email
7. Whethere there is any association or not do a final insert here.