The problem is to protect a database table field from being read and updated by two different threads at the sametime.
I am using a counter table to assign IDs to all the other tables. When a transaction occurs I want the thread to get the most updated value and update the counter field accordingly.
Someone suggested that I should use transactions in Java to do that. How can I use transactions in Java.
Thx
u actuallty don't need triggers
for generating sequetial id use following query
"select max(id) from tablename"
where id is name of id field of yr sql table
u will get the max value of id from the table .
collect in an appropriate variable ,increment the count and keep on going
Regards
Vipul
THREAD t1, t2;
t1 get a max of say 20
t1 increments it to 21at same time
t2 get a max of 20
it also increments ot 21
t1=21
t2=21
t1 inserts the value to table
at id is primary key
if t2 tries to save duplicate value of 21
it get an error
u can avoid this by
step 1. before inserting check if value of 21 exist
ie select * from table wher id=21;
if record is fetched increment to 22
and follow step 1
else
save
// it works fine for any no of threads try out
Don't rely on thread based solutions, its quite posible that there are multiple processes updating that database. You need to put the transaction support under the database control. Here's [url=http://msdn2.microsoft.com/en-us/library/ms378799.aspx]the MS documentation [/url] on using transactions in MS/SQL from JDBC. As far as getting the new id from an insert into a table, set the "identity specification" on the field to make it autonumber, then read either the @@IDENTITY or preferably SCOPE_IDENTITY value.
