I have a integer field id in oracle database which has to be incremented by 1 everytime a new record is inserted.
i am using BMP bean to do this.
in ejbcreate method of the BMP bean i call getid() method which gets the max(id)+1 of that particular table.
now when i have multiple users trying to create a record at the same time, i get a error that unique contraint violated coz that id which it tries to insert is already inserted by someother user.
i am not ablo to select the most current max(id) from the table so that i can add 1 to it and then insert.
can someone tell me how to lock the select query given below:
select max(id)+1 from abc;
I assume your Next_id field is stored in a seperate table to the one you're updating?
You need to do something similar to :-
UPDATE abc SET next_key = next_key + 1 WHERE abc_key = 'xx'
SELECT next_key FROM abc WHERE abc_key = 'xx'
The first line locks the row, so nothing else can get the next key until the transaction is complete.
Why don't you use the SEQUENCES in Oracle for doing this. That will take care locking and multiple users.
I would not suggest you to have a EJB for this type of functionality . EJBs support concurrent users and hence same value can be read from the database .
Sequences may differ across databases .
what we have is a utility which is synchronized which does a read and then updates the database with the next value .
So when the next user requests for an Id the latest value is read .
You can cache to avoid multiple read/write to the database ...
Isn't it better to leave the counting to the database ?
In a large J2EE application you probably only use one database, implement the best db solution ie sequence, serial or use stored procedures (if YOUR database don't support it - are you using the right database ? ). You always do the counting/incementing when you apply data to the database and you let the database do it by itself within a transaction. It will work everytime.
Counting numbers are probably better than timestamps - you just want something unique why add the extra info about the time ?
Now there are two scenarios :
a) you need this unique value to manage transactions so data don't get overwritten by the wrong user
b) you just want to count things.
b) is easy - you have already done it with db special features !
a) is more complex.
In the bean you can re-check the value of your property that matches the db-column for the unique value before allowing the update. (Embedded JDBC in the bean ? - still don't think this is to much to ask, just a "select col1 from tab1 where .....")
Well, not to experienced in the J2EE arena but very familiar with large apps with relational databases and lots of users so I hope my ideas will fit J2EE as well.