readyset
asked on
Deadlock resolution
Here is our scenario - we have a ui screen where schedules are created for students - these schedules are stored in - studentsschedules table. Very intensive update/insert/delete oriented table all the time.
We also provide a background service which syncs schedules to student calendars , this service pings our databaseserver every 15 minutes for any new schedules depending on the calendar client. The code for this is a simple update to the table - studentschedules and then select from studentschedules with joins to other tables.
We are facing some deadlock errors on our production server, to resolve this , on the background service , I am planning to move the update and read into two different transactions, one with the update and one with the read. I am going to put isolation level for the read transaction as a "read uncommitted". I am going to test my results then and re-tweak depending on my results.
I am really not concerned about the dirty reads , because the service keeps pinging our servers at some point the data will be correctly updated. In our case the deletes might flow through a little late which is fine with us.
Any thoughts or opinions on this approach?
We also provide a background service which syncs schedules to student calendars , this service pings our databaseserver every 15 minutes for any new schedules depending on the calendar client. The code for this is a simple update to the table - studentschedules and then select from studentschedules with joins to other tables.
We are facing some deadlock errors on our production server, to resolve this , on the background service , I am planning to move the update and read into two different transactions, one with the update and one with the read. I am going to put isolation level for the read transaction as a "read uncommitted". I am going to test my results then and re-tweak depending on my results.
I am really not concerned about the dirty reads , because the service keeps pinging our servers at some point the data will be correctly updated. In our case the deletes might flow through a little late which is fine with us.
Any thoughts or opinions on this approach?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.