Updlock and deadlocks

[Former Member]

Hi,

I try to troubleshoot some deadlocks in a client deployment of SAP B1 and I see many of the deadlocks are related to ONNM table.

For example queries like

SELECT T0.*  FROM [dbo].[ONNM] T0 WITH (UPDLOCK)

Initially I suspected that such queries are executed when I open the document numbering, but tracing the commands for that I see the executed query does not contain the mentioned hint.

What could cause such "bad" queries to be run?

Thanks,

florin

[maik_delly]

Hi Florian,

we experience similar problems with current SBO 9.0. But for us it is not only ONNM ( which is involved most of the times ) but also other queries / tables.

We are working with support on a solution for random ( not reproduceable ) but reccuring DB locks in a 45+ user environment. Especially when DI Server is involved.

Maybe we can find some commonalities ...

Which PL are you working with ?

How many users are working ?

Do you have / use a DI Server ?

Any other "heavy" usage of DI API  (  DataTable / Recordset / BusinessObjects ) ?

Also have a look in SAP Note 2059740 - it is a similar issue ( didn't help us ).

regards,

Maik

[Former Member]

Hi Maik,

Thanks for sharing!

It is 9.0 PL 12, there are 40+ users and we are using the DI server for data access.

Yes, the ONNM is not the only table involved, just that for this one the queries look at least strange.

There are a lot of select queries with UPDLOCK hint and in my opinion there should be no such hint on those queries.

Normally we see deadlocks on items, onnm and orders (as these are the most used objects ).

Please let me know of any breakthroughs in your case.

Best regards,

florin

[maik_delly]

Hi Florin,

strangely enough this is kind of good news....

The environment is comparable ( many users, DI Server ) and the problem seems to be the same.

I would like to ask you to get in contact with SAP support and open a ticket with reference to our ticket ( ID 809880 ) - second level support is involved.

We already did a lot of debugging on this issue and found the following :

- Locks can be created by DIS crash ( DIS SQL process is "Awaiting command" -> no auto resolve )

- additional DIS licenses worsen the issue

- DI API transaction feature is problematic

- Less deadlocks when DIS preferences are changed ( Deadlockretries = 10, ConnectionTimeout = 3 , MaxConnections increased ) -> for us memory consumption of DIS was evidently decreased -> less crashes

regards,

Maik

[maik_delly]

Hi Florin,

do you have any developments regarding this issue ?

After some days of silence we experienced crashes again and support is no help so far (  hundreds mb of log files and sql traces were uploaded ).

Since it is just not possible to reproduce the system behaviour of a 45+ ( heterogeneous and heavy load ) user environment it would be nice to interchange with a fellow "victim".

Feel free to contact me via email ( SCN profile ) .

regards,

Maik

[pedro_magueija]

Hi Florin,

ONNM is the numbering table, I suspect this table is updated regularly when you add documents either via the client or the API.

However they shouldn't deadlock, are addons running? Could it be that a transaction is started from an addon?

---

Best regards,

Pedro Magueija

[Former Member]

Hi Pedro,

Yes, I'm aware of the purpose of the table. However what I do not understand is the reason why would anyone make a select on the whole table with update lock. This should be done per a single record....

Seems I will have to hunt down the addons, hopefully find the guilty one. As this is a production server, it will not be that easy.

Best regards,

florin