commit transaction

[bazzottigabriele]

in a Rest API I called:

<JpaRepository>.deleteAll();
<JpaRepository>.flush();
<JpaRepository>.saveAndFlush(<Entity>)

But that gives me: database is locked

That's because the transactions aren't committed to the database, until the end of the Rest API the deleteAll isn't executed on the database and the resource isn't released.

[gwenn]

Are you sure that the JPA transaction is correctly declared ?
Which JDBC driver is used ?
Could you try to activate the SQLite log and trace and see what happen ?
Could you switch temporary to another backend (like postgresql/h2/...) to make sure that it is related to SQLite and not your code ?
Could you share a minimalist/standalone/compilable sample to reproduce the problem ?

[bazzottigabriele]

The declaration of JPA is:

<jpa:repositories base-package="com.accenture.moparva.datalayer.repos"
		entity-manager-factory-ref="emf" transaction-manager-ref="transactionManager" />

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName">
			<value>${jdbc.driverClassName}</value>
		</property>
		<property name="url">
			<value>${jdbc.databaseurl}</value>
		</property>
		<property name="username">
			<value>${jdbc.username}</value>
		</property>
		<property name="password">
			<value>${jdbc.password}</value>
		</property>
	</bean>

	<bean id="emf"
		class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="packagesToScan" value="com.accenture.moparva.datalayer.models" />
		<property name="jpaVendorAdapter">
			<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
		</property>
		<property name="jpaProperties">
			<props>

				<prop key="hibernate.dialect">${jdbc.dialect}</prop>
				<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
				<prop key="hibernate.format_sql">true</prop>
				<prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
				<prop key="hibernate.connection.release_mode">auto</prop>
				<prop key="hibernate.transaction.auto_close_session">true</prop>
				<prop key="maxActive">1</prop>
				<prop key="org.hibernate.flushMode">COMMIT</prop>
				<prop key="spring.jpa.hibernate.ddl-auto">update</prop>
			</props>
		</property>

	</bean>

	<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
		<property name="entityManagerFactory" ref="emf" />
	</bean>
	<tx:annotation-driven />

	<bean id="persistenceExceptionTranslationPostProcessor"
		class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />

	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<constructor-arg>
			<ref bean="dataSource" />
		</constructor-arg>
	</bean>

I'm using hibernate5.

The problem is that until the end of the Rest API the deleteAll is not executed, only at the end of the function there are a commit and a Sqlite log.
With h2 the code works.

[gwenn]

So I still don't known/have the SQLite driver used, the SQLite traces (not the hibernate traces because drivers execute their owns queries), and no sample.
Could you try to tweak these flags:

<prop key="hibernate.connection.release_mode">auto</prop>
<prop key="hibernate.transaction.auto_close_session">true</prop>
<prop key="org.hibernate.flushMode">COMMIT</prop>

?

[bazzottigabriele]

The driver is:

<dependency>
				<groupId>org.xerial</groupId>
				<artifactId>sqlite-jdbc</artifactId>
				<version>3.8.11.2</version>
			</dependency>

The sample is:

@Entity
@Table(name = "AdminConfig")
public class AdminConfig {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Integer id;
	private String key;
	private String value;
	private Date createdAt;

	private Date updatedAt;


	

	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public Date getCreatedAt() {
		return createdAt;
	}
	public void setCreatedAt(Date createdAt) {
		this.createdAt = createdAt;
	}
	public Date getUpdatedAt() {
		return updatedAt;
	}
	public void setUpdatedAt(Date updatedAt) {
		this.updatedAt = updatedAt;
	}
	public String getKey() {
		return key;
	}
	public void setKey(String key) {
		this.key = key;
	}
	public String getValue() {
		return value;
	}
	public void setValue(String value) {
		this.value = value;
	}
}
public interface AdminConfigREPO extends JpaRepository<AdminConfig, Integer> {

	public List<AdminConfig> findByKey(String key);
	
}

And on the controller
@Controller
@RequestMapping("settings/")

public class ConfigApiController {

	@Autowired
	private AdminConfigREPO mAdminConfigREPO;

@RequestMapping(value = "/config", method = { RequestMethod.POST })
	public @ResponseBody List<AdminConfig> postOtfMessage(@RequestBody AdminConfig adminConfigs, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		
		
		mAdminConfigREPO.deleteAll();
		mAdminConfigREPO.saveAndFlush(adminConfigs);
		
	
			return adminConfigs;
		
	}
	}

I have added
true
COMMIT

trying to force the commit

[bazzottigabriele]

with:

<prop key="hibernate.dialect">${jdbc.dialect}</prop>
				<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
				<prop key="hibernate.format_sql">true</prop>
				<prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
				<prop key="hibernate.connection.release_mode">after_transaction</prop>
				<prop key="maxActive">1</prop>
				<prop key="spring.jpa.hibernate.ddl-auto">update</prop>

and
<prop key="hibernate.connection.release_mode">after_statement</prop>

also deleting only
<prop key="org.hibernate.flushMode">COMMIT</prop>
I get the same error database locked

What else could I tweak them?

[gwenn]

I can't reproduce the error:

@Service("clientService")
public class ClientServiceImpl implements ClientService {
  @PersistenceContext
  private EntityManager entityManager;
// ...
  @Transactional
  public void replaceAll(Client client) {
    entityManager.createQuery("DELETE FROM Client").executeUpdate();
    entityManager.persist(client);
    entityManager.flush();
  }
}

@Transactional
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:/META-INF/spring/applicationContext*.xml")
public class ClientServiceTest {
  @Autowired
  private ClientService clientService;

  @Test
  public void testReplaceAll() {
    final Client c = new Client("client1", "code1");
    clientService.replaceAll(c);
  }
}

BEGIN (0 ns)
2017-12-01 23:02:11,530 [main] INFO  org.springframework.test.context.transaction.TransactionalTestExecutionListener - Began transaction (1): transaction manager [org.springframework.orm.jpa.JpaTransactionManager@4cfa83f9]; rollback [true]
Hibernate: delete from client
delete from client (1000000 ns)
Hibernate: insert into client (accounting_code, name, version) values (?, ?, ?)
insert into client (accounting_code, name, version) values (?, ?, ?) (0 ns)
select last_insert_rowid() (2000000 ns)
ROLLBACK; (0 ns)
BEGIN (0 ns)
COMMIT (0 ns)
2017-12-01 23:02:11,877 [main] INFO  org.springframework.test.context.transaction.TransactionalTestExecutionListener - Rolled back transaction after test execution for test context [TestContext@61a2aeb7 testClass = ClientServiceTest, testInstance = com.ossia.cra.service.ClientServiceTest@64b70f41, testMethod = testReplaceAll@ClientServiceTest, testException = [null], mergedContextConfiguration = [MergedContextConfiguration@5f8d9767 testClass = ClientServiceTest, locations = '{classpath:/META-INF/spring/applicationContext*.xml}', classes = '{}', contextInitializerClasses = '[]', activeProfiles = '{}', contextLoader = 'org.springframework.test.context.support.DelegatingSmartContextLoader', parent = [null]]]

No database locked.
The transaction is rollbacked by spring in unit tests.

[0Alpha-Bravo0]

Hi there!
after some years I have the same problem:

CannotAcquireLockException: error performing isolated work; SQL [n/a]; nested exception is org.hibernate.exception.LockAcquisitionException: error performing isolated work

Caused by: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)

the exception jump up when Hibernate tries to take the next Id on @GeneratedValue id property
here logs:

Hibernate: select next_val as id_val from hibernate_sequence
Hibernate: update hibernate_sequence set next_val= ? where next_val=?
2022-02-25 17:55:36.715  WARN 23624 --- [nio-8080-exec-1] o.h.e.j.s.SqlExceptionHelper             : SQL Error: 5, SQLState: null

following my jpa/hibernate configurations:

spring.datasource.driver-class-name=org.sqlite.JDBC
spring.jpa.properties.hibernate.dialect=org.sqlite.hibernate.dialect.SQLiteDialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

as suggested by @gwenn i tried to add

spring.jpa.properties.hibernate.connection.release_mode =auto
spring.jpa.properties.hibernate.transaction.auto_close_session=true
spring.jpa.properties.org.hibernate.flushMode=COMMIT

that doesn't resolve my problem but i have had a different error

Could not open JPA EntityManager for transaction; nested exception is java.lang.IllegalStateException: Session/EntityManager

may be an error with @GeneratedValue annotation in Entity class??

Strange thing: I have a Unit test (inmemory db) that runs the same code and it works without problems.

[gwenn]

Why do you use GenerationType.AUTO instead of GenerationType.IDENTITY ?
If there is no reason, what happens when you switch to GenerationType.IDENTITY ?
And maybe we should fix driver / dialect such as Hibernate uses a RETURNING clause instead of last_insert_rowid.

[gwenn]

See 43fdae9 (but useful only with GenerationType.IDENTITY).