Hi,

I'm having a problem with Hibernate where it basically locks the table that has been updated within a transaction, such that you can't query from the table anymore (even from a query tool).

I tried setting the isolation level to as low as possible ("read uncommitted"):

hibernate.connection.isolation=1

However, it's still locking the table. Basically my code is doing batch processing. It starts a transaction, then continually waits for objects to enter a queue and saves them as they come in. As soon as 100 objects have been saved, it commits the batch transaction.

Transaction trans = session.beginTransaction();

// continually looks for new objects to save

session.save(obj);

// when 100 objects have been saved

trans.commit();

So basically, between the beginTransaction and commit, the table associated with the object is locked - I can't even query from it. Even though the object has already been saved and the Thread is just sitting there waiting for new objects to come in.

Any ideas? Is there an additional transaction configuration that I'm missing?

BTW, I'm using MSQLSERVER.

Any ideas anyone?

Ok, I have found something new today.

The object that I'm trying to save has an identity column (again, this is SQLServer). When I change it to using an assigned id instead, I no longer have this problem.

From looking through the SQLServer forums, it doesn't look like this is a limitation on SQLServer. i.e. You should be able to update tables with identity columns within transactions.

Could this be a Hibernate problem? Anyone ran across this problem before? Or have worked with upating tables with identity columns from within a Hibernate transaction?

Any help would be greatly appreciated! Thank you!

what is your hibernate.jdbc.batch_size set to?
the docs recommend between 5 and 30.

I wouldn't be so quick to blame hibernate. I would try to reproduce the problem with a java-jdbc and batch processing w/ same batch size as this setting to see what happens.

Do you issue any in between flushes?

Batch size is set to 20

I tried issuing a session.flush() after each save() and still have this problem.

I'll try the JDBC route and see if I still have this problem.

Thanks for the suggestions!

sure no problem - hope it helps.

I've the same problem. Infact, I don't need transaction. I'm just inserting one row(not multiple!!). So, I'm merrily going without transaction.

But I'm facing the same problem. Hibernate automatically, puts a begin tran, and doesn't put commit tran after inserting. So, this open transaction is dangling*

* I found all this info from SQL Profiling.

I introduced transaction after that. Just in case, it may solve. But it didn't either. The open transaction, which Hibernate is putting, is just dangling. This makes the data not committed to DB at times. Any ideas?

I have noticed this same problem. It started when we upgraded from hibernate 3.1 RC2 to hibernate 3.1.2. Same problem in 3.1.3 also.
We are using postgres 8.1.

you guys should post some or all of the following info:

1) your hibernate.properties isolation_level
2) your locking strategy (with code)
3) your code that you are noticing the problem with and if possible, as a reproducible test case.
4) your caching strategy (again mappings and code).

Hello Hibernate Team,

This is what I'm using to save the object.
public void create(Object obj) throws PersistenceException {
Session sess = null;
try {
sess = sessionFactory.openSession();
sess.save(obj);
sess.flush();
} catch (HibernateException e) {
log.debug(e, e);
throw new PersistenceException(e);
} finally {
close(sess);
}
}

But I as told you before, it used to hang before. So, we were doing SQL Trace. Unfortunately for couple of Tables, it puts BEGIN TRAN before it.

Like This,

BEGIN TRAN
INSERT INTO

It doesn't happen for all tables, only a selected few(although the above code is common). Then I tried putting a transaction..The SQL Trace was like this,

BEGIN TRAN
BEGIN TRAN
INSERT INTO
END TRAN

Stil, this dangling BEGIN TRAN is there. I need to find out a way to turn it off. My Database is SQl Server 2000 SP3 w/Cumulative Patch MS03-031, and we are using Hibernate 2.

I'm attaching my hibernate.properties for your ref
######################
### Query Language ###
######################

## define query language constants / function names

hibernate.query.substitutions true 1, false 0, yes 'Y', no 'N'

## package imports

hibernate.query.imports net.sf.hibernate.test, net.sf.hibernate.eg
#hibernate.connection.password=s3



#################
### Platforms ###
#################

## JNDI Datasource

#hibernate.connection.datasource jdbc/SAFT
#hibernate.connection.username u
#hibernate.connection.password p

hibernate.connection.datasource jdbc/pooled/CIT
#hibernate.connection.username u
#hibernate.connection.password p

## MS SQL Server

hibernate.dialect net.sf.hibernate.dialect.SQLServerDialect
hibernate.connection.driver_class com.inet.tds.TdsDriver
#hibernate.connection.url jdbc:inetdae7:PEP_SQL?database=CIT

#hibernate.connection.username CITuser
#hibernate.connection.password CITuser

#################################
### Hibernate Connection Pool ###
#################################

hibernate.connection.pool_size 20
#hibernate.statement_cache.size 20



###########################
### C3P0 Connection Pool###
###########################

#hibernate.c3p0.max_size 2
#hibernate.c3p0.min_size 2
#hibernate.c3p0.timeout 5000
#hibernate.c3p0.max_statements 100
#hibernate.c3p0.validate false



###################################
### Apache DBCP Connection Pool ###
###################################

# connection pool

#hibernate.dbcp.maxActive 100
#hibernate.dbcp.whenExhaustedAction 1
#hibernate.dbcp.maxWait 120000
#hibernate.dbcp.maxIdle 10

## prepared statement cache

#hibernate.dbcp.ps.maxActive 100
#hibernate.dbcp.ps.whenExhaustedAction 1
#hibernate.dbcp.ps.maxWait 120000
#hibernate.dbcp.ps.maxIdle 100

# optional query to validate pooled connections:

#hibernate.dbcp.validationQuery select 1 from dual



#################################
### Plugin ConnectionProvider ###
#################################

## use a custom ConnectionProvider (if not set, Hibernate will choose a built-in ConnectionProvider using hueristics)

#hibernate.connection.provider_class net.sf.hibernate.connection.DriverManagerConnectionProvider
hibernate.connection.provider_class net.sf.hibernate.connection.DatasourceConnectionProvider
#hibernate.connection.provider_class net.sf.hibernate.connection.C3P0ConnectionProvider
#hibernate.connection.provider_class net.sf.hibernate.connection.DBCPConnectionProvider



#######################
### Transaction API ###
#######################

## the Transaction API abstracts application code from the underlying JTA or JDBC transactions

hibernate.transaction.factory_class net.sf.hibernate.transaction.JTATransactionFactory
#hibernate.transaction.factory_class net.sf.hibernate.transaction.JDBCTransactionFactory


## to use JTATransactionFactory, Hibernate must be able to locate the UserTransaction in JNDI
## default is java:comp/UserTransaction

#jta.UserTransaction jta/usertransaction
#jta.UserTransaction javax.transaction.UserTransaction
#jta.UserTransaction UserTransaction


## to use JTATransactionFactory with JCS caching, Hibernate must be able to obtain the JTA TransactionManager

#hibernate.transaction.manager_lookup_class net.sf.hibernate.transaction.JBossTransactionManagerLookup
#hibernate.transaction.manager_lookup_class net.sf.hibernate.transaction.WeblogicTransactionManagerLookup
#hibernate.transaction.manager_lookup_class net.sf.hibernate.transaction.WebSphereTransactionManagerLookup
hibernate.transaction.manager_lookup_class net.sf.hibernate.transaction.OrionTransactionManagerLookup
#hibernate.transaction.manager_lookup_class net.sf.hibernate.transaction.ResinTransactionManagerLookup



##############################
### Miscellaneous Settings ###
##############################

## print all generated SQL to the console

hibernate.show_sql FALSE

## specify a JDBC isolation level

hibernate.connection.isolation 2

## set the JDBC fetch size

#hibernate.jdbc.fetch_size 25

## set the maximum JDBC 2 batch size (a nonzero value enables batching)

hibernate.jdbc.batch_size 0

## enable use of JDBC 2 scrollable ResultSets (specifying a Dialect will cause Hibernate to use a sensible default)

#hibernate.jdbc.use_scrollable_resultset true

## use streams when writing binary types to / from JDBC

hibernate.jdbc.use_streams_for_binary true

## specify a default schema for unqualified tablenames

#hibernate.default_schema test

## use a custom stylesheet for XML generation (if not specified, hibernate-default.xslt will be used)

#hibernate.xml.output_stylesheet C:/Hibernate/net/sf/hibernate/hibernate-default.xslt


## enable outerjoin fetching (specifying a Dialect will cause Hibernate to use sensible default)

hibernate.use_outer_join false

##########################
### Second-level Cache ###
##########################

## optimize chache for minimal "puts" instead of minimal "gets" (good for clustered cache)

#hibernate.cache.use_minimal_puts true


## enable the query cache

hibernate.cache.use_query_cache true


## choose a cache implementation

hibernate.cache.provider_class net.sf.ehcache.hibernate.Provider
#hibernate.cache.provider_class net.sf.hibernate.cache.EmptyCacheProvider
#hibernate.cache.provider_class net.sf.hibernate.cache.HashtableCacheProvider
#hibernate.cache.provider_class net.sf.hibernate.cache.TreeCacheProvider
#hibernate.cache.provider_class net.sf.hibernate.cache.OSCacheProvider
#hibernate.cache.provider_class net.sf.hibernate.cache.JCSCacheProvider
#hibernate.cache.provider_class net.sf.hibernate.cache.SwarmCacheProvider
############
### JNDI ###
############

## specify a JNDI name for the SessionFactory

#hibernate.session_factory_name hibernate/sessionFactory

## Hibernate uses JNDI to bind a name to a SessionFactory and to look up the JTA UserTransaction;
## if hibernate.jndi.* are not specified, Hibernate will use the default InitialContext() which
## is the best approach in an application server

#file system
#hibernate.jndi.class com.sun.jndi.fscontext.RefFSContextFactory
#hibernate.jndi.url file:/

#WebSphere
#hibernate.jndi.class com.ibm.websphere.naming.WsnInitialContextFactory
#hibernate.jndi.url iiop://localhost:900/

hibernate.mappingDir=.

I would appreciate any help in this.

Thanks,
Maddy.