Deadlock When Deleting (SQL Server)

Questions and answers regarding general SQL and backend databases

Deadlock When Deleting (SQL Server)

Postby bcusick » Fri May 03, 2019 4:53 pm

Hey Guys,

I have a number of headless clients (10) that work on a batch process to download data and then normalize that data into our schema.

I've recently been getting a bunch of these types of errors when the process deletes all the records in a small foundset (like 3-4 records):

Code: Select all
)com.servoy.j2db.dataprocessing.DataException: Transaction (Process ID 58) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim.


I'm not sure how to code around this. Anyone have any suggestions?

MUCH appreciated!

Bob
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Re: Deadlock When Deleting (SQL Server)

Postby ROCLASI » Sat May 04, 2019 12:00 pm

Hi Bob,

What database vendor is this? MSSQL Server?
Also is this table you delete records from used anywhere in your solution(s)? Or other external apps? Just to identity who might hold a lock (and why).
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Twitter: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5385
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Deadlock When Deleting (SQL Server)

Postby bcusick » Sat May 04, 2019 5:50 pm

Hey Robert,

The tables are used in different parts of the solution - but there are no users logged in when this batch process runs.

Basically - I spawn about 10 headless instances (one for each tenant) that goes to a government site - logs in as the tenant - and downloads information.

Then, that data is manipulated to fit my own schema. As part of that process - I have to delete some data (small sets - like 4 records) - but I have to delete a LOT of them.

This is SQL Server 2016.

Thanks for any advice!

Bob
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Re: Deadlock When Deleting (SQL Server)

Postby ROCLASI » Sun May 05, 2019 5:26 pm

Hi Bob,

Are you running anything inside database transactions ? If so, how long are they open?
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Twitter: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5385
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Deadlock When Deleting (SQL Server)

Postby bcusick » Sun May 05, 2019 6:27 pm

Hey Robert,

I don't use any transactions at all.

Bob
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 5 guests

cron