Questions tagged [queue]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
0 answers
32 views

Service Broker's SEND statement causes security context switch

We have triggers in multiple tables that will send messages to a Service Broker service upon INSERT, UPDATE and DELETE operations. These operations may be nested as parts of bigger, transactional ...
user avatar
  • 111
1 vote
1 answer
60 views

Optimize UPDATE query for single "next" row

We had information from new relic that one our query has a bad performance (almost 16 seconds for execution). We use PostgreSQL 11.8 on Debian Linux. Table: postgres=# SELECT table_name, column_name, ...
user avatar
1 vote
2 answers
77 views

Correct way to continuously receive from a queue

I have to receive messages from a queue continuously. My current code looks like this: DECLARE @status INT=1 WHILE @status IS NOT NULL BEGIN WAITFOR (RECEIVE @status=status, ...
user avatar
2 votes
0 answers
66 views

Exactly-once FIFO queue in Synapse

Creating a queue table in SQL Server is a much-studied problem. However, I would like to implement one in Azure Synapse where many of the building blocks do not exists. Specifically no table hints (...
user avatar
0 votes
0 answers
142 views

Advanced Queues fail with "Table stats versioning encountered error(942)"

I've been trying to get DBMS_CHANGE_NOTIFICATION to work on an Oracle XE 11.2 DB in docker. I feel like I've followed the documentation to the letter, but I still can't get it to work. Here is what I'...
user avatar
0 votes
1 answer
36 views

Queueing MySQL record inserts to avoid over-subscription of a related resource ... table locking?

Given a simplified hypothetical of seats in a lifeboat, if I have the following setup with a lifeboats table and a seats table where each record is one occupied seat in the given lifeboat: CREATE ...
user avatar
  • 3
2 votes
1 answer
171 views

Duplicate key errors in Postgres queue-like table when rows should be locked by SELECT FOR UPDATE SKIP LOCKED

I have a table called queue with items that need to be processed: CREATE TABLE public.queue ( id serial NOT NULL CONSTRAINT queue_pkey PRIMARY KEY ); Another table process ...
user avatar
0 votes
1 answer
140 views

Are recent versions of Oracle able to implement a queue with SKIP LOCKED?

My question: what is the very latest status on whether or not recent versions of Oracle actually make it possible to implement a queue in a straightforward way with SKIP LOCKED and limiting to a ...
user avatar
1 vote
3 answers
69 views

Queue Implementation with Custom Consistent Absolute Ordering

I need to create a schema for a queue. Elements in this queue will be processed in an order based on a column. However, this is not always the e.g. date_added column. Users should be able to freely re-...
user avatar
0 votes
2 answers
90 views

A way to merge multiple queues

Consider the following table: CREATE TABLE `multiqueue` ( `ID` BIGINT(20) NOT NULL AUTO_INCREMENT, `CustomerID` BIGINT(20) NOT NULL, `Volume` INT(11) NOT NULL, `Content` MEDIUMTEXT NOT ...
user avatar
6 votes
1 answer
269 views

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 ...
user avatar
0 votes
0 answers
215 views

How to create and process queue of data (FIFO) from MS SQL Trigger [duplicate]

I would like to create a table and fill it with data and have it act as a queue that works in a first-in-first-out fashion. New items can be added at any time while processing or not. My inserts ...
user avatar
  • 301
4 votes
3 answers
503 views

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 ...
user avatar
4 votes
1 answer
757 views

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 ...
user avatar
  • 43
0 votes
0 answers
83 views

Automating Alerting on Blocking with Queues and Event Notifications [duplicate]

I need to automate email notifications on blocking on some SQL 2016 servers. I have had a look at running a job through the SQL agent but seems hack n slash. I found an alternative method online that ...
user avatar
  • 129
0 votes
2 answers
52 views

Best Database specifications for my IOT solution

i'm working on a IOT environment that basically is a sensor that can measure more then 300 times a second. We receive this measurements remotely using MQTT protocol. I want to set up a painel where i ...
user avatar
2 votes
0 answers
58 views

How should I configure a table to optimize for frequent inserts and deletes? [duplicate]

I know about making the clustered index a steadily increment field, like a datetime field. So that will give me fast inserts. However, the deletes will be based on a consumer of the "queue". So the ...
user avatar
  • 133
1 vote
1 answer
84 views

Is this table-backed-queue implementation safe?

We've a system where kinds of orders should be processed in batches, by advancing them from Status 0 to Status 1. There's a max number of a particular kind of order we can process in any one go, and ...
user avatar
0 votes
1 answer
30 views

Row inserted to DB in main app is not available in the different process. Can MySQL defer some queries?

I have such problem. My main app process some request and insert the new row into DB, then send an Event to the queue. I have the different process that listens on that queue and when he get details ...
user avatar
0 votes
1 answer
1k views

How to persist messages in Postgresql's LISTEN/NOTIFY? [closed]

If I send notifications before starting to listen i.e. notify test, 'test message'; listen test; I loose that notifications. How can I persist them for later fetching? Actually, I need this ...
user avatar
  • 103
2 votes
1 answer
336 views

How do I determine what is activating my SQL service broker proc?

I have several queues. One of which has suddenly started acting oddly. It activates repeatedly while nothing is in it's queue. I even manually removed old conversations and this thing is still ...
user avatar
21 votes
2 answers
24k views

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 ...
user avatar
  • 311
1 vote
1 answer
882 views

Database table as a queue, order by another table's column

I have two tables. One table serves as the queue from which multiple users simultaneously dequeue items one at a time, by flagging the item's row. The other table is joined and used for ordering. It ...
user avatar
22 votes
1 answer
37k views

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.
user avatar
7 votes
1 answer
3k views

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 ...
user avatar
1 vote
0 answers
538 views

oracle AQ for scheduled jobs

We have a process that currently uses the Oracle DBMS_SCHEDULER to execute. The process runs every time the backend receives data from an external device. The Oracle scheduler is set up such that a ...
user avatar
13 votes
3 answers
19k views

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 ...
user avatar
  • 255
3 votes
1 answer
840 views

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) ......
user avatar
  • 1,341
119 votes
1 answer
120k views

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 ...
user avatar
2 votes
1 answer
1k views

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 ...
user avatar
  • 285
0 votes
0 answers
141 views

Efficient way to fetch/update X records in a loop

What I need is a simple process that handles requests. I have setup a simple table as follows: CREATE TABLE Requests ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, request_param VARCHAR(250) ...
user avatar
  • 731
7 votes
1 answer
2k views

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 ...
user avatar
  • 17.7k
2 votes
1 answer
405 views

Schema for multiple identical queue tables

I am migrating a message processing application that was built on MSMQ to SQL Server 2012 (preferably Standard Edition). Messages are received and distributed via TCP/IP endpoints and the typical ...
user avatar
  • 505
2 votes
0 answers
87 views

Optimizations for new SQL Server 2000 database that will act as email queue AND API database

Due to limitations put on me I've got to put my outgoing Email Queue in the same database as my customer information. What are some optimizations I can make, specifically to the Email portion that ...
user avatar
18 votes
2 answers
21k views

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? ...
user avatar
  • 4,105