Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [queue]

41 questions
151
votes
1 answer

Postgres UPDATE ... LIMIT 1

I have a Postgres database which contains details on clusters of servers, such as server status ('active', 'standby' etc). Active servers at any time may need to fail over to a standby, and I don't care which standby is used in particular. I want a…
vastlysuperiorman
  • 1,685
  • 2
  • 11
  • 8
31
votes
2 answers

Automatic aging-out (deletion) of old records in Postgres

Does Postgres have any features to support aging-out old records? I want to use Postgres for logging, as a sort of queue, where records (log events) older than two weeks are automatically deleted.
Basil Bourque
  • 11,188
  • 20
  • 63
  • 96
25
votes
3 answers

Postgres Listen/Notify As Message Queue

Is there any way to use Postgres Listen/Notify feature to deliver a message to a channel and have only one listener consume this message? The purpose for this is that I have multiple 'worker' apps all listening to the same Postgres channel. But I…
moesef
  • 351
  • 1
  • 3
  • 4
20
votes
2 answers

FIFO queue table for multiple workers in SQL Server

I was attempting to answer the following stackoverflow question: What SQL Server 2005/2008 locking approach should I use to process individual table rows in multiple server application instances? After posting a somewhat naive answer, I figured…
ErikE
  • 4,355
  • 4
  • 29
  • 39
15
votes
3 answers

Best way to implement concurrent table based queue

I have a table in MySQL that represents a queue of links to be processed. The links are processed by an external app, one by one, and deleted in the end. This is a high volume queue and I have multiple instances of the processing app, spread across…
Miguel E
  • 275
  • 1
  • 2
  • 7
7
votes
1 answer

Recommendations on how to organize Queues in Service Broker

I have a service broker application that currently has 5 or 6 queues on two servers. The general workflow is: Server A User populates some tables User fires stored proc which puts a message into a header queue indicating there is work to be…
JNK
  • 18,064
  • 6
  • 63
  • 98
7
votes
4 answers

Is my queue table implementation race condition safe?

Hello people smarter than me! I've created a sort-of-a-queue table system, but it seems too simple to be safe from race conditions. Am I missing something or is the following race condition safe? The Schema I have a table, let's call it…
J.D.
  • 40,776
  • 12
  • 62
  • 141
7
votes
2 answers

Using a Table as a Queue without sp_getapplock/sp_releaseapplock

I have a list of commands I need to execute, all of which are contained within a table I've named myQueue. This table is a little unique in that some commands should be grouped together such that their execution is performed sequentially, rather…
John Eisbrener
  • 9,547
  • 6
  • 31
  • 65
7
votes
1 answer

No service broker active on database

I am trying to set up query notification however when I try to create a queue and service I get the error 'There is no Service Broker active in the database. Change to a database context that contains a Service Broker.' When I run the query select…
Johnathon64
  • 247
  • 3
  • 5
4
votes
3 answers

Why is our query suddenly returning rows it should not (using READPAST and UPDLOCK options)?

We have a job table that looks like this CREATE TABLE [dbo].[Clearing]( [Skey] [decimal](19, 0) IDENTITY(1,1) NOT NULL, [BsAcctId] [int] NULL, [Status] [varchar](20) NULL, CONSTRAINT [csPk_Clearing] PRIMARY KEY CLUSTERED ( [Skey]…
brian beuning
  • 151
  • 4
4
votes
1 answer

How can I limit SQL Server Agent's simultaneous jobs number?

I'm looking for a setting (other than schedules shifting) which could limit the number of jobs running simultaneously. I have 844 jobs in my SQL Agent (SQL Server 2017 Enterprise). Most of them needs few minutes to complete, there are about 190 jobs…
bigder
  • 43
  • 3
3
votes
1 answer

Work queue with complex select and long processing: Ensuring concurrency

I have a queue table from which worker processes claim records, one at a time. It is crucial that no two workers claim same item to process. Normally this kind of thing is done with select top (1) ... with (updlock, rowlock, readpast). This works as…
GSerg
  • 1,353
  • 1
  • 17
  • 29
3
votes
4 answers

Performance issues of a queue implemented on top of InnoDB

Brief problem statement First thing first: after posting this question originally and working more with our DBAs, I actually learned that our DB runs in a container, instead of being installed natively. From what I've read before, it's discouraged…
Dmitry Frank
  • 131
  • 3
3
votes
3 answers

Impact of index on a "status" field with one (guaranteed) change

Introduction I have a PostgreSQL table setup as a queue/event-source. I would very much like to keep the "order" of the events (even after the queue item has been processed) as a source for e2e testing. I starting to run into query performance…
WesAtWork
  • 143
  • 5
2
votes
1 answer

Retrieve next queue item

I have a simple table in SQL Server 2012 which implements a processing queue. As data has been inserted the query to retrieve the next item has gone from <100ms to a constant 5-6secs. I would be hugely grateful if someone could point me at the cause…
Chris L
  • 295
  • 1
  • 9
1
2 3