title: Database query hangs when running a select query against a table that a spring batch process is working on
body: <p>I have a file storage application which has two services (job service and file service) as spring boot applications. When user uploads a file, depending on the file type it gets processed via spring batch jobs and its thumbnail and preview is generated using FFMpeg and/or ImageMagick. These data is then written to a database (currently developing on Microsoft SQL Server 2017 Developer locally).</p>
  
  <p>Smaller files were working as expected but I wanted tested the application via sending a bunch of larger audio and video files and started refreshing the files page to see if the files are processed. However after a few refreshes the UI was stuck as the "loading" state until the spring batch job ended and after is displayed the results as expected.</p>
  
  <p>I ran the same test and checked the network tab and noticed that the call that was made to retrieve the list of files was at "pending" state. It stayed at that stage until the rest of the batch process was complete and returned as successful (200).</p>
  
  <p>After some debugging on the web service side I found that the file service application gets stuck at the following line:</p>
  
  <pre><code>List&lt;Asset&gt; allAssets = jdbcTemplate.query("SELECT asset_id, asset_extension, asset_import_date, asset_imported_by_username, asset_name, asset_path, asset_preview_path, asset_thumbnail_path, asset_type FROM assets", new AssetRowMapper()); 
  </code></pre>
  
  <p>So in my scenario, when I refresh the files page I am trying to access the assets table as the spring batch jobs also work on the same table to insert and update each file related data. I thought maybe the insert and update queries takes precedence somehow and lock the table from my select queries. So I created a view from the assets table and changed my query to:</p>
  
  <pre><code>List&lt;Asset&gt; allAssets = jdbcTemplate.query("SELECT asset_id, asset_extension, asset_import_date, asset_imported_by_username, asset_name, asset_path, asset_preview_path, asset_thumbnail_path, asset_type FROM assets_vw", new AssetRowMapper()); 
  </code></pre>
  
  <p>Unfortunately this didn't work and I got the same result.</p>
  
  <p>To see if this problem is originating from the application side (my code) or the database side I ran the same test but I ran the same query manually through the SQL Management Studio on my database. Interestingly, after a few successful queries (as I have seen on the UI also), I got the same result the query was stuck at "executing" stage until the rest of the spring batch jobs completed.</p>
  
  <p>So it seems that the problem is on the database side.</p>
  
  <p>I checked the maximum number of connections on my database (as suggested in various posts) and it was already the default value 0 (unlimited).</p>
  
  <p>At this point I am not sure what's wrong with my database (or my code). Any help would be appreciated.</p>