DI API Deadlocks in SQL 2005 database server

192 Views

Hello All,

I have an Issue, which is coming on large databases ( > 50GB) and number of users > 20.

I have read the following notes

1269591, 1489753, 1318311, 1231444 , 1344641 , 1316554

also contributed in the following thread:

Workaround:

Each SAP B1 Client is using an addon, which do a WM (Warehouse management) based on WHSE Journal. Until upgraded to 2007 version, this was working fine, no issue came up only system was slow.

Process with pseudo code like this

'incoming cases - when an item is entering into the warehouse
Add Logistics documents (action success = true)
 - start transaction
  - check Warehouse journal 
  - record changes done by this document (based on oinm) into (UDT)
 - finish transaction
'outgoing cases - where an item is carried out from the warehouse - create a self pick list example: sales order:
start transaction
 - loop on every rows of the sales order
  - read ordered quantity
  - look for place where it is stored, 
 - if found make  a reservation in (UDT)
  - add position to self created pick list  (UDT)
finish  transaction
'release from warehouse
start transaction
  - look for pick list position (UDT)
  - reduce stock value (UDT)
  - update pick list  with packed quantity (UTD)
  - create delivery note
finish  transaction

Depending from activity the users receive various error messages like Could not commit transaction / Deadlock ... / -2038 Internal error xxxx messages, when trying to access to UDT-s or any marketing documents, and the system is frozen. I go to activity monitor, and check and tell them which client is causing dead locks, they log off ,and can continue work or if the process runs to timeout, it sends the error message to SAP B1 Client.

Also we have an addon which reads (only reads) data from database from OITM / OITW / and one of these UDT above. (queries against stock value from UDT). here we receive 2 type of error messages

- Deadlock

- Timeout

I know these messages are coming from: other computers /Users are issueing documents in transaction.

Any ideas to resolve this issue / has anybody happened this issue ?

Every ideas, comments are welcome.

The system is b1 2007 SP 01 PL 10. Average document position between 50-100.

Solution Architect team? Any workaround?

Regards

János