Need advice on MYSQL table lock requirement

i am developing a central store stock management system with mysql 5.6 as database and java using jdbc.

i have following tables:

item_id    | description  | .....| current_balance
041           | Item 2         |.......| 210
092           | Item 3         |.......| 333

store_voucher_entry (assume an issue voucher)
......... | voucher_number | item_id | overall_qty | item_balance
______|________________     |_________|____________|______________
  .....   | S9876                     | 041        | 8                  | 202
  .....   | S9876                     | 092        | 33                | 300

when processing a receipt or issue voucher as follows i do:

1) select the current balance from a products table of all product entries in the issue/receipt voucher being processed
 (eg, voucher contains item 2 and item 3).
     A typical voucher could contain several different products of different quantities issued/received.
2) insert the voucher header information
3) then insert information about the entries in the voucher in the store_voucher_entry table
     For each entry i have to maintain a spot balance after the entry is processed. (it a reporting requirement)
       So i have to do a + or - with the entry qty and the balance selected from (1) above.
4) update current balance of each item in voucher in the products table
I need to make sure that while voucher is processed,
no other process or user can select current balance of any item selected from (1) in products table, until the processing is finished.

So here is what i have done?

	//start a transaction
	statement.execute("SET autocommit=0");

	//lock relevant tables
	statement.execute("LOCK TABLES store_voucher WRITE, products WRITE,store_voucher_entry WRITE");
		other processing go here , updates , inserts, selects from locked tables

	//commit any transactions

	statement.execute("UNLOCK TABLES");
	//rollback if any issues
	if (statement != null) {
		try {statement.execute("ROLLBACK");} catch (Exception e) {}
	//in case something went wrong in try, release table locks
	if (statement != null) {
		try {statement.execute("UNLOCK TABLES");} catch (Exception ee) {}
        //... close resources


Open in new window

 Is the way i handled it okay?, or is there a better way to do this?
Obinna Henry NwaforAsked:
Who is Participating?
Gary PattersonVP Technology / Senior Consultant Commented:
If this is an interactive process, a good practice is:

top of loop
Read balance with no lock
Display to user, wait for user input

Transaction begins
Re-read balance with lock
Compare balance to verify it hasn't changed.
If changed, release lock and go to top of loop to try again.
Otherwise, update balance
Transaction ends
Bad practice to lock and then wait for user input, since a user can leave and cause lots of problems for other processes.
You can run atomic transactions without table lock.
Tablelock is necessary if you truncate it and reload.
Gary PattersonVP Technology / Senior Consultant Commented:
Locking the entire tables just to update one row looks like dramatic overkill to me as a DB2 guy. That kind of behavior will cause all sorts of contention in a high-volume environment.  

As I understand it, you don't have much of a choice when using MyISAM, but when using InnoDB, you can do row-level locking, which is far superior for OLTP applications.

So, the question is - are you using MyISAM or InnoDB?  If you have any choice in the matter, sounds to me like you want to be using InnoDB.
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

transaction alone should lock little enough for short enough time for 2 to not clash.
Obinna Henry NwaforAuthor Commented:
thank you, gheist and Gary Patterson for your response.
@Gary Patterson , i am using InnoDB?

@gheist, while i can run atomic transactions without table lock, this would not prevent another user to select current balance of an item being processed by another user, this is not good cos both will do calculations based on same balance.

@Gary Patterson, i agree with you, but will row level locking prevent this situation?

- consider this scenario
1) user1 is receiving item2
2) user2 is issuing item2 also
3) typically this happens concurrently

when user1 selects the current balance of item2, user2 should only be allowed to select balance of item2 until user1 processing is done and item2 balance updated, so that user2 can calculate with the new balance.

thats why i decided to do lock tables.

will row level locking achieve this? i think if user1 did say a SELECT... FOR UPDATE of required rows, it would still allow user2 to select same balance, and this is not good.

how can i prevent this, so that a user always works with the most recent balance of an item, irrespective of how many concurrent users are processing vouchers with same item.
Obinna Henry NwaforAuthor Commented:
i just tried something with row level locks and i think it worked: used two different mysql sessions

set autocommit=0;
SELECT current_balance FROM products WHERE item_id in (...) FOR UPDATE;
UPDATE products set current_balance=.... WHERE ...

it seemed to work as the second session waited at the SELECT...FOR UPDATE until the first finished and it read the updated balances.

Whats your opinion about this?
while transaction is in progress it should not be considered.
See SQL ACID. It is counted on COMMIT, no need to keep all users out of previous (still correct before commit) result while you add new records.
Obinna Henry NwaforAuthor Commented:
the current_balance  fetched from the select for update, is used for subsequent calculations for a new current_balance and this new value is stored in both the products table and the voucher_entrys record to maintain a trail of balance changes and what entries caused such change.

hence if all users read current_balance of same item, then the eventual balance could be wrong;

scenario (where users are allowed to read same balance concurrently, user1 reads first):
1) user1 reads 5 as item1 balance for receipt of 3 units of item1
2) user2 reads 5 as item1 balance for issuance of 2 units of item1

then depending on which comitted last, the overall balance in products table of item1 would either be 8 or 3, which is wrong.

desired outcome: (assume concurrent user scenario, user1 reads first)
1) user1 reads 5 and continues receipt processing of 3 units
2) user2 should be allowed to read only after user1 completes and updates current_balance to 8
2) user2 now reads 8 and continues issuance processing  of 2 units and updates balance to 6

assuming am getting it wrong, how can i do this right?
If you make that dependency into single SQL transaction all the locking happens correctly and automagically
Obinna Henry NwaforAuthor Commented:
thanks for your reply, i think i get what you are saying, but cant quite translate it into code.
could you give me a little sql code bits, doesn't have to be a full blown code.
transaction is committed all values locked, posted unlocked at same moment. (i.e anybody accessing value just gets all values before XOR all values after, and never halfway done)
i dont have your sql bits, you can make deadlock in same transaction, but even then it is rolled back....
Obinna Henry NwaforAuthor Commented:
if 'anybody accessing value just gets all values before XOR all values after, and never halfway done', does that not mean that if all concerned values are locked by another user/process, this anybody has to wait?
Obinna Henry NwaforAuthor Commented:
@Gary Patterson, thanks for your comment.
i understand the flow.
what type of lock should i use?
row lock obviously...
Gary PattersonVP Technology / Senior Consultant Commented:
I'm a DB2 guy, not a MySQL guy, but automatic row locks are generally part of any DBs tranactional support.  Generally all you need to do is start a transaction, retrieve the record for update, and it gets locked automatically.  Maybe gheist can tell you more about the MySQL particulars.
Only particularity is that myisam locks full table as a file for some operations. While it is expected to be ACID-clean (there are counter-cases) - it is fairly inefficient.
MariaDB has good documentation about shortcomings of theirs and mysql storage engines:
Gary PattersonVP Technology / Senior Consultant Commented:
@gheist:  Above, I asked which engine was being used, and poster responded "InnoDB", if I read the response right.  Perhaps Obinna Henry Nwafor can confirm?  

It is a pretty important distinction, as I understand it.
I think it was "how do I tell I am using InnoDB ?"
Obinna Henry NwaforAuthor Commented:
thanks guys, have really learnt a lot from this thread.
@Gary Patterson, your are right db engine is InnoDB. and your right to say
"Generally all you need to do is start a transaction, retrieve the record for update, and it gets locked automatically",

But i think the automatic row lock offered within a transaction only locks the rows for updates and not for reads/selects.
thus another process say process2 could also read same value even though the row is locked within another transaction by process1 and being updated, making the values read by process2 stale.

which was the reason why i thought process1 could 'SELECT..<only required rows> FOR UPDATE' within a transaction which  prevents a read until process1 is done, so when it finishes process2 could then read latest value.

SELECT ... FOR UPDATE should help you. With INNODB that locks the rows affected, with MyISAM full tables.
Obinna Henry NwaforAuthor Commented:
thank you @Gheist and @Gary Patterson for your time. really appreciated.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.