Skip to main content

Error in using lock table

No replies
cinlung
Offline
Joined: 2010-06-23
Points: 0

Dear all Java experts,

I need your help in Java expertise. I made an application in Java for entering stock into database. It is a desktop app to update stockcard that can be runned at many computers, but updates 1 table only (stock card). I uses mysql database.

I want to make all those computer to queue themselves when entering the data by using mysql LOCK TABLES StockCard WRITE; command, then UNLOCK TABLES; after each process finished.

This is pretty much what I wanted to do:
1. Get stock ID and location from user
2. Lock Stock Card table to prevent other PC from inputting with no queue
3. Get the last stock qty from Stock Card table. --> This is where the error occurred.
4. Update the last qty and use it as the new beginning qty and add the qty of change.
5. Insert the newly processed data back to the stock card.
6. Unlock table for other to use.

It seemed that multi-PreparedStatement executes different JDBC sessions even if the Connection object is the same. I know the process should work with the latest mysql driver. Maybe my code need some fixing. I tested with Aquadata by opening 2 connections, one locks the table, the other perform a select, and it worked. The select cannot run until I unlock the table using the previous connection.

Please help and thank you
Rendra

Here is what my codes look like

//Lock Database
sdb.lockTable(DBInfo.TABLE_STOCKCARD); --> This will call the LOCK TABLES StockCard WRITE; and run it using same connection object, but different statement object from the one used in the insert process below (PreparedStatement locker).

for(int i=0; i Here I am trying to get the last qty available in the stock card and make it as the beginning qty using PreparedStatement object different from the "locker". --> Error happened here: java.sql.SQLException: Table 'INV_StockCard' was not locked with LOCK TABLES
csh[i].getItemID(), csh[i].getCashierStorage());

if(null==temp || temp.length<1)
{
throw new Exception(
"[CTP841] Error: Item " + csh[i].getItemID() +
" qty is not available in location " + csh[i].getCashierStorage() +
"!");
}

temp[0].setAction(StockCardBean.STOCK_ACT_OUT);
temp[0].setCurrentQty(temp[0].getNewQuantity());
temp[0].setQtyOfChange(csh[i].getItemQty()*-1);
temp[0].setNewQuantity(temp[0].getCurrentQty()+temp[0].getQtyOfChange());
temp[0].setEntryDate(new Date());
temp[0].setEntryUser(b.getOperatorID());
temp[0].setReference(b.getTransactionID());
temp[0].setNote("Cashier transaction.");

//Insert into database stock card
if(sdb.insert(temp[0])<=0) --> This is the procedure to enter the compiled data to the stock card table.
{
throw new Exception(
"[CTP883] Error: Error entering item " + csh[i].getItemID() +
" into stock card!");
}

sdb.unlockTables(); --> This will call the "UNLOCK TABLES;" using the reused statement from the lock process (locker).