ava.sql.SQLException: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Lock table is full. (7870)

[adi reddy]

Hi ,

I dont know why I am getting this. Because some times its working fine.

The following is my object and trying to save:

class A ...{

...

Set<B> b;
...

@OneToMany(mappedBy = "A", cascade = {CascadeType.ALL})
@LazyCollection(LazyCollectionOption.FALSE)
public Set<B> getB() {
return this.b;
}
public void setB(Set<B> b) {
this.b = b;
}

Set<C> c;
...

@OneToMany(mappedBy = "A", cascade = {CascadeType.ALL})
@LazyCollection(LazyCollectionOption.FALSE)
public Set<C> getB() {
return this.c;
}
public void setB(Set<C> c) {
this.c = c;
}

}

class B...{
......
..
...

}

class c..{

.........
...

}

classes A, B, C are mapped with TABLE_A, TABLE_B, TABLE_C tables in database.

I am trying to save A as follows:

saveA(A a){
..
getHibernateTemplate().saveOrUpdate(a);
getHibernateTemplate().flush();
..
}

some times i am getting the following exception:
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not insert: [A]; uncategorized SQLException for SQL [insert into TABLE_B (.........) values (........)]; SQL state [HY000]; error code [-210016]; [DataDirect][OpenEdge JDBC Driver][OpenEdge] Lock table is full. (7870); nested exception is java.sql.SQLException: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Lock table is full. (7870)

Why i am getting this? any help...

[Steve Luke]

Each time you need to update an Object in the database you need to obtain a lock on the Row containing the data. This lock is held in a table named the 'Lock table' and this table has a limited number of rows, and so has a limited number of objects that can be updated in a single transaction. It appears like you are attempting to update too many objects in a single transaction (maybe because of the Set<B> and Set<C> are large collections.)

I think there are a couple of solutions:
1) You could examine your database configurations and see if there is an option to increase the number of rows in the lock table. For example, the Progress DB has -L. You would want to set that value to something larger than the total number of Objects you expect in a single transaction. If you don't know the number you expect then you could use a debugger to figure it out.

2) Reduce the size of your transaction. Examine the data which is being committed on a single transaction and see if you can either reduce the total number of Objects or break the transaction into multiple transactions (safely).

[adi reddy]

Thanks for help...

I am thinking Set<B> (if it contains huge data) needs to save separately as follows:

saveA(A a){  
..  
Set<B> bs= a.getB();
a.setB(null);
getHibernateTemplate().saveOrUpdate(a);  
getHibernateTemplate().flush(); 
if(bs != null && bs.size()>0) {
saveBlist(bs);
} 
..  
}  

public void saveBlist(Set<B> bs) throws Exception{
 for(Iterator itr = bs.iterator();itr.hasNext();){
  B b = itr.next();
  getHibernateTemplate().saveOrUpdate(b);
  if(count%100 == 0){
   getHibernateTemplate().flush(); 
   getHibernateTemplate().clear(); 
}
}
}

i have tried with above code but no use. After 7 thousand count or iterations its giving same SQL exception.

I have tried one more scenario:

saveA(A a){  
..  
Set<B> bs= a.getB();
a.setB(null);
getHibernateTemplate().saveOrUpdate(a);  
getHibernateTemplate().flush(); 
if(bs != null && bs.size()>0) {
saveBlist(bs);
} 
..  
}  

public void saveBlist(Set<B> bs) throws Exception{
 Session session = getHibernateTemplate().getSessionFactory().openSession();
for(Iterator itr = bs.iterator();itr.hasNext();){
  B b = itr.next();
  session .saveOrUpdate(b);
  if(count%100 == 0){
   session .flush(); 
   session .clear(); 
}

}
session.close(); 
}

This is working fine, i am able save data. good.

Here is my question:
1)What is difference between first and second scenario?
2) In second scenario if saveBlist() method throws an SQL exception, will the A save in database or all operations will rollback?

Please help me in this ???