Is it possible to know wich program is locking a record?

[Cringer]

I've looked into this before. Seems it has resource impact, particularly on storage.

 

[Cringer]

Does anyone know if it has an impact on the performance of the db at all?

 

[Osborne]

Does anyone know if it has an impact on the performance of the db at all?

Would also like to know whether it does, and the only mention of a performance hit I found is in this article - http://knowledgebase.progress.com/articles/Article/P150383:

When active, the client processes sends additional information to the database as described above. This will incur a minor penalty for performance on the client but only when Statement Caching is enabled on the database. The performance penalty is due to an additional operation when performing an interaction with the database regardless of whether the client is a shared memory connection or remote connection. For remote connections the client must send additional packets of information to the database.

 

[TomBascom]

I use this feature on some very large systems. The "performance hit" is negligible. Storage is trivial.

There some bugs and issues that make "turn it on for everyone and leave it on" untenable right now. But using it when you've got an issue to debug is perfectly ok.

 

[Cringer]

Thanks Tom. I've added a viewer for it on our db monitoring tool.

 

[Rob Fitzpatrick]

Thanks Tom. I've added a viewer for it on our db monitoring tool.

There's also one in ProTop. (And in promon.)

Performance hit should (in theory) be less for self-service clients as you aren't pulling the stack data across a wire; the client process is writing it to shared memory. Gus has some info on performance considerations in his presentation.

If you just want to sample what a client is doing but don't want the hit of constant updates, option 3 (one-time stack) is useful. It's sort of like issuing a single kill -SIGUSR1 except that you don't have to go find a protrace file. Actually that's a simplification. Client statement caching only writes to _Connect when the client access the DB, whereas with kill -SIGUSR1 the client will give you a protrace even when non-CRUD code is running (provided it isn't hung or deadlocked).

 

[Cringer]

Thanks Rob.