Entity Bean Locking a table in the database

Discussions

EJB design: Entity Bean Locking a table in the database

  1. Entity Bean Locking a table in the database (7 messages)

    Hi, we are using Entity beans to query a table in our database. We have used VisualAge for Java 3.5 to generate the code and in the EJSJDBCPersister class in the EJB we create, we have a findByPrimaryKeyForUpdateSqlString() method that gets called. Is there anyway we can force
    the EJS Server and Websphere not to use this or stop a lock on the table from happening. There is another method called findByPrimaryKeySqlString() which does the same thing other than the update.

    Basically, I guess when we bring the server down or something happens and the table used by the entity bean gets locked.

    We do notcall this method but it does get used but the EJS Server and by WebSphere.

    Anyone have any ideas on why this is happenening and how it can be fixed?
     
  2. i think ........

    A lot of rows are being locked for updates
    So the database is prompting the row level locks to a table level lock.

    Thats the easy part
    The tougher question is why are so many things getting updated and why is the transaction not being commited.

    or check

    Do you have a index on the table on that PK.
  3. Yes, we have a index for all of the keys...Here's the SQL:

    final static String findByPrimaryKeyForUpdateSqlString = "SELECT T1.end_date, T1.contract_type, T1.contract_seq, T1.status, T1.contract_num, T1.advertiser_num, T1.agency_num, T1.level_num, T1.start_date, T1.adtype_cd, T1.rate_card_num, T1.pub_cd, T1.edition_cd FROM rep_contract T1 WHERE ((T1.advertiser_num = ?) AND (T1.contract_num = ?)) AND (T1.pub_cd = ?) FOR UPDATE";

    We have in index for the advertiser_num, contract_num and pub_cd.

    Why, What does having an index mean? I hope you reply. Thank you sooo much!

    Hitesh
  4. The index means the search is not a table scan but is an index scan.

    Now can you grep the code and find out who are the callers of this UpdateSQL. There is a slight Chance that it is in a ejbStore() method.
  5. The only things that use that SQL is is the EJSJDBCPersisterContractBean's load method. I guess it's used to load the EJBs by WebSphere's EJB Server and WebSphere. How can we prevent this locking of the table and any ideas on why how it would happen?
  6. Can you change the generated code not to use the for update sql but use the normal sql. See if that still causes the problem.
    The beans are loaded and locked hoping that they will updated. Since you are not updating them the row is locked. If the number of rows exceed the lock escalation level the row level lock is getting promoted to a table level lock.

    There is some material on isolation level in the descriptor. Chances are you are using TRANSACTION_SERIALIZABLE. Can you change this to something less restrivtive and see what happens.
  7. I have the following isolation level:
    TRANSACTION_READ_COMMITTED.

    The Transacation attribute is set to TX_REQUIRED.

    Would setting the isolation level to TRANSACTION_SERIALIZABLE prevent dirty reads and not lock the table?


  8. You cannot modify the isolation level of a entity beans with container-managed
    persistence. These beans use the default isolation level of the DBMS, which is
    usually READ_COMMITTED.
    For entity beans with bean-managed persistence and for all session beans, you can
    set the isolation level programmatically with the API provided by the underlying
    DBMS.
    U can set the Isolation level to Serializable as it does prevent dirty reads.