0

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 ?

Balaji Kandasamy
  • 4,446
  • 10
  • 40
  • 58
Nitin Kabra
  • 3,146
  • 10
  • 43
  • 62

2 Answers2

3

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
Joe Enos
  • 39,478
  • 11
  • 80
  • 136
  • what will happen if the table is currently lock and another user is trying to insert a record? – John Woo Mar 31 '13 at 13:43
  • They should simply have to wait for your transaction to complete before it will allow them to access the table - I wouldn't expect any problems as long as your transaction stays fast. But definitely test it out, maybe throw a `SLEEP` in the transaction to force it to take a long time, then while it's sleeping, try to hit it a few times and make sure they gracefully wait for the transaction to finish rather than error. – Joe Enos Mar 31 '13 at 13:46
  • will the data be available for readonly purposes while its locked ? – Nitin Kabra Mar 31 '13 at 14:58
  • Not sure - locking is a complex issue, and not my strong point - try [this](http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html) from the docs to see if something in here works for you. But even if it blocks from reading, the amount of time will be so small that it probably wouldn't matter anyway - if you delay a process 15ms, they won't even know it. – Joe Enos Mar 31 '13 at 15:05
  • @JoeEnos : can you please provide some code ? i tried this http://stackoverflow.com/questions/15749752/lock-table-after-begintransaction-mysql-transaction-in-c-net but still not working. transaction does not lock the table. – Nitin Kabra Apr 01 '13 at 20:14
  • Haven't done this myself, so I don't have a working example. Take a look at the link inside my previous comment for syntax for locking a table. I'd probably suggest putting the entire thing in a stored procedure, rather than executing the commands one at a time in your application. – Joe Enos Apr 01 '13 at 20:20
1

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.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • can you please provide some code ? i tried this http://stackoverflow.com/questions/15749752/lock-table-after-begintransaction-mysql-transaction-in-c-net but still not working. transaction does not lock the table. – Nitin Kabra Apr 01 '13 at 20:16
  • @NitinKabra The linked post doesn't contain any locking code. You'll need to do: [`LOCK TABLES your_table WRITE`](http://dev.mysql.com/doc/refman/5.6/en/lock-tables.html). NOTE: You'll need to lock the whole table, so just `SELECT ... FOR UPDATE` is not enough. – Branko Dimitrijevic Apr 02 '13 at 02:18