0

java.sql.BatchUpdateException: Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I have two java application one reading data from sybase and writing to an sql server 2008 and another reading data from sql server 2008 table and writing it to other table. Now the two application works fine. But I have many people accessing data from mssql table where the second application updates data every 30 sec. So I get the above exception. I saw similar thread here in stackoverflow but Deadlock troubleshooting in Sql Server 2008 I have problem with the solution presented here with

row versioning

can I user rowversioning to avoid dead locks in my situation and How could I use it?

Edit

    String selectAllQuery = "Select new_site_id from GIS.MAP.ro";    
    String selectQuery = "Select siteId from GIS.MAP.status where AlarmCode in ('1','2','3') and localNodeAlias like 'FLM%'";

    String updateQuery = "update GIS.MAP.ro set active_site_status = ? where new_site_id = ?";  
    String updateAllQuery = "update GIS.MAP.ro set active_site_status = site_status where new_site_id = ?";

So, I select from GIS.MAP.status table and update update GIS.MAP.ro table. Actually GIS.MAP.status table is also update every 30 sec(the whole table is deleted and inserted but the two task

  1. inserting into GIS.MAP.status goes first and transaction is committed
  2. updating GIS.MAP.ro table goes second and transaction is committed
  3. Finally, The data from GIS.MAP.ro is accessed by many user by third party application. Actually the deadlock occours at this instance.
Community
  • 1
  • 1
kinkajou
  • 3,664
  • 25
  • 75
  • 128

1 Answers1

1

In most cases deadlocks indicate that there's something wrong with your implementation - there's a problem with query order, locking order or join order. I'd suggest collecting and analyzing your deadlocks (MSDN: Analyzing Deadlocks with SQL Server Profiler) so you may find exact places in your code where deadlocks occur and fix them.

Sergey Kudriavtsev
  • 10,328
  • 4
  • 43
  • 68
  • dead lock occours when geoserver accessed map data column which is being modified. I think geoserver only uses select query and nothing. – kinkajou Jan 12 '12 at 11:30
  • How simple is your select? If it uses subqueries, for example, it might be deadlocking if the other process is modifying the same tables – Sergey Kudriavtsev Jan 12 '12 at 11:46
  • yes table is being modified by another process and data is being used by people using another application. – kinkajou Jan 12 '12 at 11:50
  • Well, could you show the exact queries for modification and for select? Also CREATE TABLE statement might be useful. – Sergey Kudriavtsev Jan 12 '12 at 11:51
  • I added the query which I wrote but you the third party application should generate simple select query that is not update and insert query. – kinkajou Jan 13 '12 at 03:53
  • Have you ever encountered failing updates due to deadlock exceptions? Or are only 3rd party app users complaining? – Sergey Kudriavtsev Jan 13 '12 at 05:57
  • I guess you can't modify user select queries, right? Then I'd suggest to move your update code into a single explicit transaction. AFAIK, this will force all table locks to be taken at transaction start and will somewhat reduce chances of deadlocking. If you catch some deadlocks using Profiler and post them in your question then I might be able to tell smth more... – Sergey Kudriavtsev Jan 13 '12 at 20:53