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
ari_royAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ravindra76Commented:


It is very simple

 synchronized ( this ) {

    // Write Your database accessing code to the database

}

Best of luck
0
vishoneCommented:

 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
0
rjackmanCommented:
if u want to prevent dead lock then u have to use Synchronized method/block as suggested by ravindra76
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

ari_royAuthor Commented:

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
0
jerchCommented:
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
0
ari_royAuthor Commented:
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
0
jerchCommented:
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
0
Ravindra76Commented:
I think in the java programming way,

my previous comment

synchronized ( this ) { }

is the best solution
0
vishoneCommented:

 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
0
Ravindra76Commented:
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
0
vishoneCommented:

 Hi ari_roy,

 Are you using servlets?

 - Vish
0
Ravindra76Commented:

Hi ALL

Sorry.

Ignore My Previous comments.

I was in other mood
0
ari_royAuthor Commented:
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
0
Ravindra76Commented:

just add this code to thread free


public class YourServlet extends HttpServlet implements SingleThreadModel

Now it will be thread free.

Note: JSDK 2.0 version of servlets
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ari_royAuthor Commented:
Hi ravindra76!!

Thanks a lot.
Your code really worked
ari_roy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.