New answers tagged locking
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
