Database Locking


EJB programming & troubleshooting: Database Locking

  1. Database Locking (4 messages)

    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;


    Threaded Messages (4)

  2. Database Locking[ Go to top ]

    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.

  3. Database Locking[ Go to top ]

    Why don't you use the SEQUENCES in Oracle for doing this. That will take care locking and multiple users.

  4. Database Locking[ Go to top ]

    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 ...

  5. Database Locking[ Go to top ]


    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.