Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.
Solved
preventing update of customer balance twice
Posted on 2012-03-18
I have a web application using coldfusion and mysql. There is a form where
customer's payment is entered and submitted. Upon submission customer's
balance is updated. Sometimes customer's balance is updated twice.
How do I prevent this from happening?
Example:
Balance: 1000
payment: 100
After update: balance = 800
Thanks.
customer's payment is entered and submitted. Upon submission customer's
balance is updated. Sometimes customer's balance is updated twice.
How do I prevent this from happening?
Example:
Balance: 1000
payment: 100
After update: balance = 800
Thanks.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3-
3
6 Comments
Without seeing your code, it's hard to make specific suggestions. But one solution is to generate a unique identifier, something like a transaction or order id. When the user clicks submit, the application should check if payment has already been made for this id. If so, then it has found a duplicate payment.
This is my code for updating the balance.
<cfquery name="updBal" datasource="#dsn#">
update cust
balance = balance - #form.payment#
</cfquery>
<cfquery name="addToTransact datasource="#dsn#">
insert into transact
(name, payment, pay_date)
values
('#form.name#', '#form.payment#', #Now()#)
</cfquery>
Transact table and cust table have primary id (transactid and custid)
Sometimes this piece of code is executed twice. When that happens transactid
is incremented twice for the two records inserted.
How would a primary key come into play?
Thanks.
<cfquery name="updBal" datasource="#dsn#">
update cust
balance = balance - #form.payment#
</cfquery>
<cfquery name="addToTransact datasource="#dsn#">
insert into transact
(name, payment, pay_date)
values
('#form.name#', '#form.payment#', #Now()#)
</cfquery>
Transact table and cust table have primary id (transactid and custid)
Sometimes this piece of code is executed twice. When that happens transactid
is incremented twice for the two records inserted.
How would a primary key come into play?
Thanks.
transactid is the primary key for transact, and yet the insert statement above does not include that column, so I assume that has integer auto_increment in your definition. I think what you need to do is to already insert a row to the transact table before processing payment (so payment column is currently null), note the generated transactid, then make it part of the form, maybe as a hidden field.
Then when the form is submitted, a check is made if the row with that transactid already has a value for payment. If so, then it is considered a duplicate and nothing else happens. If not, then the usual balance update should proceed.
Then when the form is submitted, a check is made if the row with that transactid already has a value for payment. If so, then it is considered a duplicate and nothing else happens. If not, then the usual balance update should proceed.
Featured Post
The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
- Server Hardware
- IT Administration
- Disaster Recovery
- Storage Software
- Databases
- Storage Hardware, MySQL Server, Hardware, Percona, *Backups
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB.
To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month5 days, 16 hours left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.