Obinna Henry Nwafor
asked on
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:
products
__________________________
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
HERE IS MY CONCERN:
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?
QUESTION
Is the way i handled it okay?, or is there a better way to do this?
i have following tables:
products
__________________________
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
HERE IS MY CONCERN:
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?
try{
//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("COMMIT");
//UNLOCK ALL TABLES
statement.execute("UNLOCK TABLES");
}catch(...){
//rollback if any issues
if (statement != null) {
try {statement.execute("ROLLBACK");} catch (Exception e) {}
}
}finally{
//in case something went wrong in try, release table locks
if (statement != null) {
try {statement.execute("UNLOCK TABLES");} catch (Exception ee) {}
}
//... close resources
}QUESTION
Is the way i handled it okay?, or is there a better way to do this?
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.
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.
transaction alone should lock little enough for short enough time for 2 to not clash.
ASKER
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.
@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.
ASKER
okay,
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 ...
...
COMMIT or ROLLBACK
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?
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 ...
...
COMMIT or ROLLBACK
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.
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.
ASKER
@gheist,
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?
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
ASKER
@gheist
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.
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....
i dont have your sql bits, you can make deadlock in same transaction, but even then it is rolled back....
ASKER
@gheist,
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Gary Patterson, thanks for your comment.
i understand the flow.
what type of lock should i use?
i understand the flow.
what type of lock should i use?
row lock obviously...
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:
https://mariadb.com/kb/en/mariadb/aria-storage-formats/
MariaDB has good documentation about shortcomings of theirs and mysql storage engines:
https://mariadb.com/kb/en/mariadb/aria-storage-formats/
@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.
It is a pretty important distinction, as I understand it.
I think it was "how do I tell I am using InnoDB ?"
ASKER
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.
@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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you @Gheist and @Gary Patterson for your time. really appreciated.
Tablelock is necessary if you truncate it and reload.