CODEWITHSUNDEEP
javasql-serverspringazurejdbctemplate
BetaDev
BetaDev

Reputation: 4684

JDBC Template Tables locking issue

I am using JDBC template to insert/update the DB table. I have following Configuration fro data source:

@Bean
  public DataSource dataSource() {
    HikariDataSource dataSource = new HikariDataSource();
    SQLServerDataSource ds = new SQLServerDataSource();
    ds.setUser("user name");
    ds.setPassword("Password");
    ds.setPortNumber(123);
    ds.setDatabaseName("DB name");
    ds.setServerName("Server");
    ds.setEncrypt(true);
    ds.setTrustServerCertificate(true);
    dataSource.setAutoCommit(true);
    dataSource.setMaximumPoolSize(size);
    dataSource.setMinimumIdle(idle);
    dataSource.setDataSource(ds);

    return dataSource;
  }

My problem is table lock. I am accessing, inserting and updating to a table my_table by exposing an API endpoint. So when I hit the endpoint first time, endpoint inserts three rows in the table which is intended. After first hit the subsequent other hit to the Endpoint taking 2/3 minutes to insert data. During that period I can not even access my my_table using MS SQL client application too.

I though it might be because of the open commits that's why I made:

dataSource.setAutoCommit(true);

But not luck. After first hit table gets locked for a while and all other subsequent hit or direct access to that DB table taking too long.

The Java Application works perfectly on localhost but after deploying to the Azure (Application service), I am getting table lock problem. I am not sure its table lock issue or something causing that table busy. I am using MS SQL Server which is also on the cloud.

NOTE: At my DAOs level, I am simply using jdbcTemplate.update() to add or edit data and on my service level method I am using @Transactional.

Upvotes: 0

Views: 1513

Answers (1)

Jack Jia
Jack Jia

Reputation: 5549

I created a new project and tested it and it worked fine. You may get it at: DataSource

Users table:

enter image description here

I used jdbcTemplate.update() to add and update User, and also enabled @Transactional on JdbcUserRepository. However, table was not blocked when I tried to add user:

enter image description here

From the screenshot, you can see that I made a lot of POST requests to add users, and each request got a response immediately.

So, I think the root cause of the problem may still be in the logic part of your code. Could you please share more code for further investigation.

Upvotes: 1

Related Questions