Link to home
Start Free TrialLog in
Avatar of ari_roy
ari_roy

asked on

record locking

i would like to lock records through java code to prevent a dead lock situation
i am using db2 udb as my backend
Avatar of Ravindra76
Ravindra76



It is very simple

 synchronized ( this ) {

    // Write Your database accessing code to the database

}

Best of luck

 Hi ari_roy,

 If any item in the record is changed on the
 screen, trap that event,

 Handle that event by issuing row level locking
 SQL statement for that record in the database,

 If that record is already locked by other user,
 handle the exception by popping up a message
 for the same.

 You do not have to write any code to 'unlock'
 since commiting or rollbacking the changes
 should automatically release the locks.
 
 I am not familiar with lock statements in DB2,
 but this is one way of preventing dead lock
 situation.

 - Vish
if u want to prevent dead lock then u have to use Synchronized method/block as suggested by ravindra76
Avatar of ari_roy

ASKER


hi Vish,

your comment is good but i need a proper code to the same.
the way you have suggested is similar to my logic but how to go about it??

ari_roy
Use
Connection.setTransactionIsolation(TRANSACTION_SERIALIZABLE)

The code would look like this:

int defaultIsolation = conn.getTransactionIsolation();

conn.setTransactionIsolation(TRANSACTION_SERIALIZABLE);

Statement stmt = conn.createStatement();

stmt.executeUpdate(<sql>);
/** you can have more than one sql statement executed */

conn.setTransactionIsolation(defaultIsolation);

Hope this helps.

Jerson
Avatar of ari_roy

ASKER

hi jerch
i am using jdk 1.1.6 and not jdk2ee
so your code didnot work
i also cannot change the version
ari_roy
Isn't that method available in Java 1.1.  I think it's your DBMS' driver that does not support transaction locking.  So the best you can do is to synchronized your database access just what they have told you by using the synchronized keyword.  If you're using Access, I'm sure it doesn't support such functionality. :)

Jerson
I think in the java programming way,

my previous comment

synchronized ( this ) { }

is the best solution

 Hi Ravindra,

 I thought 'synchronized' is for  preventing
 more than one thread from executing
 inside a method at once. Assuming a multi
 -user environment, I  think there is always
 a possiblity of executing the same method
 from different instances of the applet (at
 different clients) resulting in bad data,even
 with usage of 'synchronized'.

 Can you or anybody clarify this for me??


 Hi ari_roy,
 
 I already mentioned that since I am not
 familiar with db2, I will not be able to give
 concrete solution.

 In Oracle(with which I am little coversant
 with), after trapping an change in the item
 on Java Applet, I will issue a SQL statement
 to the database something like this

 SELECT <columns> FROM <table name> FOR UPDATE NOWAIT.

 'FOR UPDATE' locks the row and 'NO WAIT'
 does not wait for getting the lock.

 Hope this helps as a hint, in Oracle there
 is locking statement,  'LOCK TABLE..,
 where you can lock the table completely
 or at row level by specifying the 'lock mode'.

 - Vish
Hi Vishone,

 iN servlets,each service() call or doGet() or doPost() is a thread.

Here all threads have same code().

IF they are different type of threads, synchronize() won't work.

But for this case, synchronized will work.

because all are threads of same group

 Hi ari_roy,

 Are you using servlets?

 - Vish

Hi ALL

Sorry.

Ignore My Previous comments.

I was in other mood
Avatar of ari_roy

ASKER

hi Vish!

I am using servlets.
I tried out your code but it actually did not help
I am sending you the code without 'for update...  '

The code:-

try
{
//synchronized ( this )
//{
    int defaultIsolation = //con.getTransactionIsolation();
//  con.setTransactionIsolation(TRANSACTION_SERIALIZABLE);
    con.setAutoCommit(false);

//update1

    String q4 = "update buyer_broker set current_credit = current_credit - " + b5 + " where buyer_id = " + buyer_id;                                                                                                              Statement s4 = con.createStatement();
    s4.executeUpdate(q4);

//update2

    String q5 = "update invoice set current_bid = " + b1 + " , no_of_bid = no_of_bid + 1 , highest_bidder = \'" + buyer_name + "\' where invoice_no = \'" + invoice_no + "\' ";
    Statement s5 = con.createStatement();
    s5.executeUpdate(q5);

//insert

    String histq2 = "insert into bid_history values(" + history_id + ", " + buyer_id + " , " + i + " , " + b1 + ")";
    Statement hists2 = con.createStatement():
    hists2.executeUpdate(histq2);
    if (highest_bidder != null)
    {
      String q2 = "select * from buyermaster where buyer_name = \'" + highest_bidder + "\' ";
      Statement s2 = con.createStatement();
      ResultSet r2 = s2.executeQuery(q2);
      r2.next();
      buyer_id1 = r2.getInt("buyer_id");      

//update3
                                                                                                                                                                                                                                                                                                String q3 = "update buyer_broker set current_credit = current_credit + " + b5 + " where buyer_id = " + buyer_id1;
      Statement s3 = con.createStatement();                                                                        s3.executeUpdate(q3);


    } // if (highest_bidder != null)
    con.commit();
//  con.setTransactionIsolation(defaultIsolation);
    con.setAutoCommit(true);
//} // synchronized ( this )

in this code the problem places are the updates and insert statement

i am really in a mess and am not getting any answers to the problem

the environment is multi user, the version of jdk i am using is 1.1.6 & cannot be changed

ari_roy
ASKER CERTIFIED SOLUTION
Avatar of Ravindra76
Ravindra76

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ari_roy

ASKER

Hi ravindra76!!

Thanks a lot.
Your code really worked
ari_roy