0

In my application, I have a piece of code where the following activities take place

1.Run script A for data generation which generates a file
2.Parse the file and read data from the file
3.Store data in 3 different tables and before storing mark existing rows of that date as stale.
4.Run script B
5.parse file generated by B
6.Store data in 2 different tables and before storing mark existing rows of that date as stale.
7.Record activity audit in a table and send a mail

Now these 7 steps are to be done in parallel for 10-12 different entities. Platform is JAVA, Spring, Ibatis, and am doing the entire process for each thread in a new Transaction with ISOLATION LEVEL as READ COMMITTED. Threads are managed by ThreadPoolExecutor with corepoolsize=maxPoolsize of 10. The size of the tables is around 30K and amount of data written or updated for each table is around 100 rows. Database being used is SQL server.

The problem is without the @Transactional notation things work out as expected, but when the 7 steps are done in a transaction for each thread, the process gets stuck and simply doesn’t proceed. On investigation of queries that are in suspended state, it turns out that 3-4 queries are trying to do step 3 and getting stuck in ‘updating existing entries of that date as stale’. This also leads to escalation of locks and after 5-10 minutes the entire database gets locked, preventing any other operation leading to sort of a deadlock.
Note that a actual deadlock does not take place, the operation simply does not proceed.
Solutions tried : Creating non clustered index on the table to help SQL server identify and lock specific rows.
Please suggest some solution or possible causes for the issue.

EDIT

After some investigation and analysis, I have figured out a solution. I have moved the steps 3,6,7 at the end of the thread after all the data has been generated. So these steps are now being done in a separate method and @Transaction notation is only applied to the method rather than doing the whole thread operation in a Transaction. Further this method has been made a 'synchronised' method , due to which even though there are 10 threads active, only one 1 thread will write to DB at any point of time.So this eliminates the worries of multiple threads trying to write to DB at the same time.
I have tested this approach and none of the issues seen earlier are present now, but just wanted to know whether annotating a method with both @Transactional and making it synchronized is fine, as in will it have undesirable effects. Further is it a good practice to write to DB in a synchronised method.

For those interested in what was actual cause of the earlier problem, I have mentioned a probable reason which may or may not be correct. One possible reason for the threads getting stuck was that the thread pool executor had a max size of 10 threads while the Apache DBCP pool had a max size of 8 DB connections. So when one first thread got into a transaction and remained idle for than 5 min (minEvicatableTime), it was evicted and its connection was given to another thread. Now the other threads who had got the connection couldnt write data as the tables were locked by the first thread, while first thread couldnt complete as it did not have a DB connection.

user1314361
  • 1
  • 2
  • 3
  • 1
    Does this has anything to do with [I need row-level locking](http://stackoverflow.com/questions/10648448/i-need-row-level-locking) or [Is it possible to force row level locking in SQL Server?](http://stackoverflow.com/questions/3114826/is-it-possible-to-force-row-level-locking-in-sql-server)? – c.s. Aug 19 '13 at 10:30
  • I don't exactly know whether the problem is due to lock escalation or due to the transaction not getting committed. Ideally with the number of rows that are getting updated which is much less than 5000 lock escalation shouldn't take place. – user1314361 Aug 19 '13 at 11:03
  • Perhaps you should post some code then to check how the above activities are implemented especially for 3, 6, 7. And perhaps add any transaction related configuration (if any) – c.s. Aug 19 '13 at 11:07
  • The steps 3,6,7 are batch updates/writes using Ibatis. I have edited the question with the solution I figured out. – user1314361 Aug 21 '13 at 07:33

1 Answers1

0

What kind of locking scheme is applied on the table?

We have a similar application where users can bulk upload excel sheet in a table. To prevent this kind of deadlock, we book the task for that user until he completes it. No other user can use the same task during this period.

Meet
  • 408
  • 2
  • 10
  • No we cannot follow this approach as the data generation part that happens in step 1 takes a lot of time, more than 1 hour for some entities, so we want to make the process parallel. – user1314361 Aug 19 '13 at 11:04