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
i am using db2 udb as my backend
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
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.setTransactionI solation(T RANSACTION _SERIALIZA BLE)
The code would look like this:
int defaultIsolation = conn.getTransactionIsolati on();
conn.setTransactionIsolati on(TRANSAC TION_SERIA LIZABLE);
Statement stmt = conn.createStatement();
stmt.executeUpdate(<sql>);
/** you can have more than one sql statement executed */
conn.setTransactionIsolati on(default Isolation) ;
Hope this helps.
Jerson
Connection.setTransactionI
The code would look like this:
int defaultIsolation = conn.getTransactionIsolati
conn.setTransactionIsolati
Statement stmt = conn.createStatement();
stmt.executeUpdate(<sql>);
/** you can have more than one sql statement executed */
conn.setTransactionIsolati
Hope this helps.
Jerson
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
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
Jerson
I think in the java programming way,
my previous comment
synchronized ( this ) { }
is the best solution
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
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
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.getTransactionIsolat ion();
// con.setTransactionIsolatio n(TRANSACT ION_SERIAL IZABLE);
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(histq 2);
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.setTransactionIsolatio n(defaultI solation);
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
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.getTransactionIsolat
// con.setTransactionIsolatio
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(histq
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.setTransactionIsolatio
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi ravindra76!!
Thanks a lot.
Your code really worked
ari_roy
Thanks a lot.
Your code really worked
ari_roy
It is very simple
synchronized ( this ) {
// Write Your database accessing code to the database
}
Best of luck