DB2 Insert and Deadlocks

db2deadlockinsert

My application has 4 different threads doing inserts into the same DB2 table simultaneously.

Could this cause a deadlock / lockwait situtation?

Theoretically I don't see a problem here; as one insert statement is not going to lock a row required by another insert statement because all are independent insert statements.

Also, I commit after each insert so transaction log wouldn't fill up.

I'm looking for a high performance model while avoiding any potential lock situtation. Please provide your thoughts.

Best Answer

I don't see a problem either.

If your application logic allows, you might consider committing every N rows.

For improved performance, you might consider whether you can do blocked inserts of multiple rows at a time.

(I use DB2 for i. If you are on LUW or z/OS, your mileage may vary.)

Related Solutions

Sql-server – Shared and IX locks causing deadlock (Sql server)

You'll want to change your code to something like this.

DECLARE @AssetIssueId INT
INSERT INTO [Issue] ([IssueId], [AssetId]) VALUES (?, ?)
SET @AssetIssueID = scope_identity()
INSERT INTO [IssuePropertyValues] 
@AssetIssueID, ?, ?

As to the why...

If Java is starting a transaction for you automatically, and the insert of 10k+ rows is taking a few moments, then this thread is taking an IX on the Issue table. Another thread is then taking an IX on the issue table (which is blocked). This thread is then attempting to take an S on the issue table (for the INSERT SELECT FROM statement) which is blocked waiting for the other thread, and we now have a deadlock.

Sql-server – How to understand this deadlock

I took a quick look at the deadlock in SentryOne Plan Explorer. Two things stand out to me:

(1) the transaction count is 2, not 1. Regardless of whether this is how "Java technology works," I am strongly suspicious that there is more going on in that wrapping transaction than you think, or at least SQL Server thinks that there might be more going on. It's not picking a victim because it doesn't like Java. Also note that wrapping transactions in SQL Server makes little sense, because there is no such thing as autonomous transactions.

(2) each INSERT statement is accompanied by an additional statement simply labeled unknown - I have no idea what this is doing, but I suspect it must be related.

Sorry this isn't a direct answer but hope it helps shed some light on where to look (how JDBC is structuring your transaction and whether you should have two transactions in the first place).

Full size graphic here

enter image description here

Best Answer

Related Solutions

Sql-server – Shared and IX locks causing deadlock (Sql server)

Sql-server – How to understand this deadlock

Related Question