Metadata in Lock conflict after SQL Statement [CORE5267] #5546
Comments
|
Commented by: @dyemanov You need to alter procedures using a WAIT transaction mode. With a NO WAIT transaction, the error you mention is expected. |
|
Commented by: Sean Leyne (seanleyne) Dmitry, WAIT really wouldn't help, no? It would simply mean that the statement would not return and wait "forever", which is really not a good solution. IMO, the current functionality is correct. You can't update the SP. |
|
Commented by: @dyemanov Sean, it does not look obvious, but believe me it helps. Cached procedures immediately release their locks once requested, the old copy remains cached by existing queries while the new version will be used by newly prepared queries. There's no blockage involved, so there's no need to wait "forever" - ALTER PROCEDURE succeeds immediately regardless of the lock timeout value. The only exception is zero timeout (NO WAIT mode) - request is just not sent to the cached procedure in this case, so the it remains "locked" until all queries using it are released. |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Submitted by: Benjamin Bacik (benjamincomed)
Duplicates CORE3766
After normal SQL-Statement the Procedure is locked.
I cannot Edit the DDL of this Procedures. Database-Monitoring showing everything ok and clean.
Event after Hours the procedures cannot be edited.
The other Connection has to be closed, so that the ddl can be edited.
Message:
lock conflickt in no wait transaction
unsuccessfull metadata update
object PROCEDURE "" is in use
--> it does'nt even show the procedurename
-->its impossible to edit any procedure on a system until all users who called one the procedures to be edited are disconnected.
The text was updated successfully, but these errors were encountered: