CODEWITHSUNDEEP
mysqldatabasedatabase-design
Nitin Kabra
Nitin Kabra

Reputation: 3226

How to fix this Foreign key issue MySQL?

I am developing an accounting application where a key is given to each transaction. for eg: in a sale transaction there are two entries, one for sale account, and other for the customer.

id    | trid | date           | Account            | Amount

112 | 33 |01-04-2013 | Sales A\c          | 300.00
113 | 33 |01-04-2013 | Customer A\c   | 300.00
114 | 34 |01-04-2013 | Sales A\c          |110.00
115 | 34 |01-04-2013 | Customer 1 A\c | 110.00
116 | 35 |01-04-2013 | Sales A\c           | 250.00
117 | 35 |01-04-2013 | Customer 2 A\c | 250.00

here, TRID is MAX(TRID) + 1. This concept works fine on single user environment, but in a multiple user environment where application is used concurrently by more than one user, one or more user's can get same TRID.

what could be the solution for that ?

Upvotes: 0

Views: 84

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

First of all, if TRID alone needs to be unique, then make it a key (alone, not in combination with some other field). This way, the DBMS will not let any duplicates enter the database, no matter what mistakes you make in the client code.

In the multi-user environment, you can use an auto-increment to safely generate unique values, provided you don't care for generated values being contiguous.

OTOH, if you can't afford "holes", then:

  • either lock the whole table before SELECT MAX(TRID) + 1,
  • or do the same SELECT without locking but be prepared to re-try if there is a key violation.

I'd recommend against the locking if you can help it, since it can have serious negative impact on scalability.

Upvotes: 1

Joe Enos
Joe Enos

Reputation: 40393

You need to use a transaction and lock the table during use to create an atomic operation - this will ensure that no two processes will use the same value.

You'll need to put the proc together, but basically it's:

-- start transaction
-- lock table
-- select max(trid) + 1 and store in a variable
-- do your inserts
-- unlock table
-- end transaction

Upvotes: 3

Related Questions