As I mentioned in my previous blog about Bulk Operations , both UPDATE and DELETE statements are challenging to handle against single entities contained across multiple tables (not counting associations), which might be the case with:
- inheritence using <joined-subclass/>
- inheritence using <union-subclass/>
- entity mapping using the <join/> construct
For illustration purposes, lets use the following inheritance hierarchy:
Animal / \ / \ Mammal Reptile / \ / \ Human Dog
all of which is mapped using the joined-subclass strategy.
Deletes
There are three related challenges with deletes.
- deletes against a multi-table entity need to
recursively cascade
to:
- all sub-class(es) row(s) matched by primary key (PK) value
- its super-class row
- all these orchestrated deletes need to occur in an order to avoid constraint violations
- which rows need to get deleted?
Consider the following code:
session.createQuery( "delete Mammal m where m.age > 150" ).executeUpdate();
Obviously we need to delete from the MAMMAL table. Additionally, every row in the MAMMAL table has a corresponding row in the ANIMAL table; so for any row deleted from the MAMMAL table, we need to delete that corresponding ANIMAL table row. This fulfills cascading to the super-class. If the Animal entity itself had a super-class, we'd need to delete that row also, etc.
Next, rows in the MAMMAL table might have corresponding rows in either the HUMAN table or the DOG table; so, again, for each row deleted from the MAMMAL table, we need to make sure that any corresponding row gets deleted from the HUMAN or DOG table. This fulfills cascading to the sub-class. If either the Human or Dog entities had further sub-classes, we'd need to delete any of those rows also, etc.
The other challenge I mentioned is proper ordering of the deletes to avoid
violating any constraints. The typical foreign key (FK) set up in our example
structure is to have the FKs pointing up
the hierarchy. Thus, the MAMMAL
table has a FK from its PK to the PK of the ANIMAL table, etc. So we need
to be certain that we order the deletes:
( HUMAN | DOG ) -> MAMMAL -> ANIMAL
Here, it does not really matter whether we delete from the HUMAN table first, or from the DOG table first.
So exactly which rows need to get deleted (a lot of this discussion applies to update statements as well)? Most databases do not support joined deletes, so we definitely need to perform the deletes seperately against the individual tables involved. The naive approach is to simply use a subquery returning the restricted PK values with the user-defined restriction as the restriction for the delete statement. That actually works in the example given before. But consider another example:
session.createQuery( "delete Human h where h.firstName = 'Steve'" ).executeUpdate();
I said before that we need to order the deletes so as to avoid violating defined FK constraints. Here, that means that we need to delete from the HUMAN table first; so we'd issue some SQL like:
delete from HUMAN where ID IN (select ID from HUMAN where f_name = 'Steve')
So far so good; perhaps not the most efficient way, but it works. Next we need to delete the corresponding row from the MAMMAL table; so we'd issue some more SQL:
delete from MAMMAL where ID IN (select ID from HUMAN where f_name = 'Steve')
Oops! This won't work because we previously deleted any such rows from the HUMAN table.
So how do we get around this? Definitely we need to pre-select and store the PK values matching the given where-clause restriction. One approach is to select the PK values through JDBC and store them within the JVM memory space; then later the PK values are bound into the individual delete statements. Something like:
PreparedStatement ps = connection.prepareStatement(
"select ID from HUMAN where f_name = 'Steve'"
);
ResultSet rs = ps.executeQuery();
HashSet ids = extractIds( rs );
int idCount = ids.size();
rs.close();
ps.close();
....
// issue the delete from HUMAN
String sql =
ps = connection.prepareStatement(
"delete from HUMAN where ID IN (" +
generateCommaSeperatedParameterHolders( idCount ) +
")"
);
bindParameters( ps, ids );
ps.executeUpdate();
...
The other approach, the one taken by Hibernate, is to utilize temporary tables; where the matching PK values are stored on the database server itself. This is far more performant in quite a number of ways, which is the main reason this approach was chosen. Now we have something like:
// where HT_HUMAN is the temporary table (varies by DB)
PreparedStatement ps = connection.prepareStatement(
"insert into HT_HUMAN (ID) select ID from HUMAN where f_name = 'Steve'"
);
int idCount = ps.executeUpdate();
ps.close();
....
// issue the delete from HUMAN
ps = connection.prepareStatement(
"delete from HUMAN where ID IN (select ID from HT_HUMAN)"
);
ps.executeUpdate();
In the first step, we avoid the overhead of potential network communication associated with returning the results; we also avoid some JDBC overhead; we also avoid the memory overhead of needing to store the id values. In the second step, we again minimized the amount of data traveling between us and the database server; the driver and server can also recognize this as a repeatable
prepared statement and avoid execution plan creation overhead.
Updates
There are really only two challenges with multi-table update statements:
-
partitioning
the assignments from the set-clause
- which rows need to get updated? This one was already discussed above...
Consider the following code:
session.createQuery( "update Mammal m set m.firstName = 'Steve', m.age = 20" )
.executeUpdate();
We saw from before that the age property is actually defined on the Animal super-class and thus is mapped to the ANIMAL.AGE column; whereas the firstName property is defined on the Mammal class and thus mapped to the MAMMAL.F_NAME column. So here, we know that we need to perform updates against both the ANIMAL and MAMMAL tables (no other tables are touched, even though the Mammal might further be a Human or a Dog). Partitioning
the assignments really just means identifying which tables are affected by the individual assignments and then building approppriate update statements. A minor challenge here was accounting for this fact when actually binding user-supplied parameters. Though, for the most part,
partitioning the assignments and parameters was fairly academic exercise.
Imagine you have a class hierarchy like this:
Item
^
File
^
Image
New properties introduced in each class in the hierarchy live in a table:
Lets say named like this [_Class]
But a view called [Class] joins up those new properties with those of the subclass. So you end up with something that looks like this:
Item: via [Item] view
^
File: via [File] => [Item] join [_File] table
^
Image: via [Image] => [File] join [_Image]
Doing it like this means that you can in MS SQL create an INSTEAD OF DELETE trigger on the view like this:
CREATE TRIGGER [dbo].[Delete.Image] ON [dbo].[Image] INSTEAD OF DELETE
AS
IF @@ROWCOUNT = 0
RETURN
DELETE FROM [dbo].[_Image]
WHERE ID IN (SELECT del.ID FROM deleted del)
DELETE FROM [dbo].[File]
WHERE ID IN (SELECT del.ID FROM deleted del)
This automatically recurses down the hierarchy without breaking any constraints. I am sure an approach like this must be possible with other databases.
Notice it achieves this by deleting from the most specific table first and then the inner VIEW which does the same thing in its INSTEAD OF DELETE trigger.
Just a thought.
Actually, I think the temporary table approach is pretty nice (and very effective).
(a) delete by primary key
(b) delete by condition from that you can derive set of primary keys to delete without data in delete table
Examples:
(a) delete from Human where id=10
(b) delete from Human as human where exists(select * from Order as order where order.status='archived' and human=order.customer)
Would it be hard to have these deletes without temporary tables - only id in where condition!
Just one question... What about if my SGBD does not allow creation of temporary tables? I'm using Oracle with a user that has only permissions em certains tables and I want to perform the following update:
update ItemInternet item set item.possuiImagem = true where item.pk in (:pks)
and it's resulting in the following error:
Hibernate:
/* insert-
select
for br.com.dimed.jpa.beans.ItemInternet ids */ insert
into
HT_ECM_ITENS_DA_INTERNET
select
itemintern0_.CODIGO_DO_ITEM as CODIGO_DO_ITEM
from
ECM_ITENS_DA_INTERNET itemintern0_
inner join
ITENS itemintern0_1_
on itemintern0_.CODIGO_DO_ITEM=itemintern0_1_.CODIGO_DO_ITEM
where
CODIGO_DO_ITEM in (
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
)
77484 [http-8080-2] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 918, SQLState: 42000
77484 [http-8080-2] ERROR org.hibernate.util.JDBCExceptionReporter - ORA-00918: column ambiguously defined
Hibernate:
delete
from
HT_ECM_ITENS_DA_INTERNET
77484 [http-8080-2] WARN org.hibernate.hql.ast.exec.MultiTableUpdateExecutor - unable to cleanup temporary id table after use [java.sql.SQLException: ORA-00942: table or view does not exist
]
29/07/2009 - 17:19:49:752 [DEBUG] TransactionInterceptor - Completing transaction for [br.com.krieser.dao.interfaces.ItemInternetDAO.changePossuiImagem] after exception: org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update
29/07/2009 - 17:19:49:752 [DEBUG] RuleBasedTransactionAttribute - Applying rules to determine whether transaction should rollback on org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update
29/07/2009 - 17:19:49:752 [DEBUG] RuleBasedTransactionAttribute - Winning rollback rule is: null
29/07/2009 - 17:19:49:768 [DEBUG] RuleBasedTransactionAttribute - No relevant rollback rule found: applying superclass default
29/07/2009 - 17:19:49:768 [DEBUG] JpaTransactionManager - Participating transaction failed - marking existing transaction as rollback-only
29/07/2009 - 17:19:49:768 [DEBUG] JpaTransactionManager - Setting JPA transaction on EntityManager [org.hibernate.ejb.EntityManagerImpl@5db8ff] rollback-only
29/07/2009 - 17:19:49:784 [DEBUG] TransactionInterceptor - Completing transaction for [br.com.krieser.services.interfaces.ImageUploadService.uploadZipFile] after exception: org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update
29/07/2009 - 17:19:49:784 [DEBUG] RuleBasedTransactionAttribute - Applying rules to determine whether transaction should rollback on org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update
29/07/2009 - 17:19:49:784 [DEBUG] RuleBasedTransactionAttribute - Winning rollback rule is: null
29/07/2009 - 17:19:49:784 [DEBUG] RuleBasedTransactionAttribute - No relevant rollback rule found: applying superclass default
29/07/2009 - 17:19:49:784 [DEBUG] JpaTransactionManager - Triggering beforeCompletion synchronization
29/07/2009 - 17:19:49:784 [DEBUG] JpaTransactionManager - Initiating transaction rollback
29/07/2009 - 17:19:49:799 [DEBUG] JpaTransactionManager - Rolling back JPA transaction on EntityManager [org.hibernate.ejb.EntityManagerImpl@5db8ff]
29/07/2009 - 17:19:49:815 [DEBUG] JpaTransactionManager - Triggering afterCompletion synchronization
29/07/2009 - 17:19:49:815 [DEBUG] TransactionSynchronizationManager - Clearing transaction synchronization
29/07/2009 - 17:19:49:815 [DEBUG] TransactionSynchronizationManager - Removed value [org.springframework.orm.jpa.EntityManagerHolder@1ff88c3] for key [org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean@1023565] from thread [http-8080-2]
29/07/2009 - 17:19:49:815 [DEBUG] JpaTransactionManager - Closing JPA EntityManager [org.hibernate.ejb.EntityManagerImpl@5db8ff] after transaction
29/07/2009 - 17:19:49:831 [ERROR] DefaultFaultHandler - Fault occurred!
org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update
Caused by:
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:60)
at br.com.dimed.jpa.dao.ItemInternetDAOImpl.changePossuiImagem(ItemInternetDAOImpl.java:69)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:296)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:177)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:144)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:166)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:166)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy30.changePossuiImagem(Unknown Source)
at br.com.krieser.services.implementations.ImageUploadServiceImpl.processImageDiretory(ImageUploadServiceImpl.java:131)
at br.com.krieser.services.implementations.ImageUploadServiceImpl.uploadZipFile(ImageUploadServiceImpl.java:71)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:296)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:177)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:144)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:166)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy31.uploadZipFile(Unknown Source)
at br.com.krieser.webservices.implementations.ImageUploadWebServiceImpl.sendFile(ImageUploadWebServiceImpl.java:20)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:296)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
at $Proxy41.sendFile(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.codehaus.xfire.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:59)
at org.codehaus.xfire.service.binding.ServiceInvocationHandler.sendMessage(ServiceInvocationHandler.java:320)
at org.codehaus.xfire.service.binding.ServiceInvocationHandler$1.run(ServiceInvocationHandler.java:86)
at org.codehaus.xfire.service.binding.ServiceInvocationHandler.execute(ServiceInvocationHandler.java:134)
at org.codehaus.xfire.service.binding.ServiceInvocationHandler.invoke(ServiceInvocationHandler.java:109)
at org.codehaus.xfire.handler.HandlerPipeline.invoke(HandlerPipeline.java:131)
at org.codehaus.xfire.transport.DefaultEndpoint.onReceive(DefaultEndpoint.java:64)
at org.codehaus.xfire.transport.AbstractChannel.receive(AbstractChannel.java:38)
at org.codehaus.xfire.transport.http.XFireServletController.invoke(XFireServletController.java:304)
at org.codehaus.xfire.transport.http.XFireServletController.doService(XFireServletController.java:129)
at org.codehaus.xfire.spring.remoting.XFireServletControllerAdapter.handleRequest(XFireServletControllerAdapter.java:67)
at org.codehaus.xfire.spring.remoting.XFireExporter.handleRequest(XFireExporter.java:48)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:857)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:792)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:475)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:440)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:609)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:695)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
at java.lang.Thread.run(Thread.java:595)
Caused by: org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.hql.ast.exec.MultiTableUpdateExecutor.execute(MultiTableUpdateExecutor.java:150)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:419)
at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:283)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1168)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:117)
at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:51)
... 70 more
Caused by: java.sql.SQLException: ORA-00918: column ambiguously defined
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:953)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3468)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1062)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
at org.hibernate.hql.ast.exec.MultiTableUpdateExecutor.execute(MultiTableUpdateExecutor.java:141)
... 75 more
Is there any way to disable the creation of temporary tables?
Thanks in advance