New answers tagged

9

Consider database-qualifying the sp_getapplock proc name. This way, the lock will be acquired in the specified database instead of the current session database context. EXEC @lock_result = tempdb..sp_getapplock @Resource = 'my_resource', LockMode = 'Exclusive', @LockTimeout = 30000;


6

You can place it in a stored procedure in one of the databases, then execute it with a cross-database call. USE db_one; GO CREATE PROC dbo.LockMy_resource AS DECLARE @lock_result int; EXEC @lock_result = sp_getapplock @Resource = 'my_resource', @LockMode = 'Exclusive', @LockTimeout = 30000;...


1

Assuming the INSERT targets the right columns, which we can't tell without table definition and target definition list. The statement boils down to this: WITH cte AS ( SELECT fa.id, {GROUP}, {worker}, 'Running' FROM fila_all fa WHERE NOT EXISTS ( SELECT FROM fila_consumers fc WHERE fc.msg_id = fa.id AND fc.group_name = {...


-2

By using the DBMS_LOCK, we cannot guarantee that only one session will pass through. I have observed that when multiple sessions request for a lock simultaneously, there is a greater chance that more than one session will get into the procedure. One full proof method to allow only one worker to get into is that - in the beginning, run a query on ...


0

Given that postgresql supports isolation levels, you can go for serializable and consider the typical "better forgiveness than permission" approach to avoid locks. Here example in RoR: begin Item.transaction(isolation: :serializable) do #Item.lock("FOR NO KEY UPDATE").... not needed Item.increment_counter(:...


Top 50 recent answers are included