Hot answers tagged

160

Without concurrent write access Materialize a selection in a CTE (Common Table Expressions) and join to it in the FROM clause of the UPDATE. WITH cte AS ( SELECT server_ip -- pk column or any (set of) unique column(s) FROM server_info WHERE status = 'standby' LIMIT 1 -- arbitrary pick (cheapest) ) UPDATE ...


28

According to PostgreSQL documentation about NOTIFY: The NOTIFY command sends a notification event together with an optional "payload" string to each client application that has previously executed LISTEN channel for the specified channel name in the current database. Notifications are visible to all users. (emphasis mine) This means you cannot do what ...


18

There is no feature built in to delete rows automatically on a time-based regime (that I would know of). You could run a daily (you decide) cron-job to schedule simple DELETE commands or use pgAgent for the purpose. Or you could use partitioning with weekly partitions. That makes deleting very cheap: just keep the latest two weeks and drop older partitions. ...


9

First: MySQL is one of the worst possible pieces of software to implement this, specially if it is very dynamic. The reason is that engines like MEMORY and MyISAM have only full-table locks while more suitable engines like InnoDB have a higher write penalty (to provide ACID properties) and are optimized for accessing records that are spatially and temporally ...


8

This is an open question with no clear choice. YMMV so you have to test. Here is my opinion: Having one queue to handle everything is a good choice if you want to be able to control the number of activated tasks, as there is no global max_queue_readers. Other than that, I don't see many advantages. One could argue that one single activated proc is easier to ...


7

Taking just the core part that identifies the id of the row to return, the following query encapsulates the logic needed: SELECT TOP (1) MQ.id FROM dbo.messagequeue AS MQ WHERE -- Current row MQ.processed = 0 AND MQ.failed = 0 AND ( EXISTS ( -- Previous row in strict sequence SELECT * ...


5

Well I think it's quite simple. You just get the message that Service Broker isn't enabled. But you query the system databases, not your current Database. Just as an idea, might it be, that you run the Script for setting up the notification in your master database while you forget to change the database context to your db01 where the Service Broker is ...


5

As I explained in this article, MySQL 8 introduced support for both SKIP LOCKED and NO WAIT. SKIP LOCKED is useful for implementing job queues (a.k.a batch queues) so that you can skip over locks that are already locked by other concurrent transactions. NO WAIT is useful for avoiding waiting until a concurrent transaction releases the locks that we are ...


5

You've got two different indexes that can satisfy that query. So two queries, running two different plans could each lock a key on a different index. Try forcing the index in the query.


5

Getting the same row from different indexes As David mentions in his answer, you can get the same row from multiple sessions if you happen to access that row via different indexes. The UPDLOCK hint only applies to the specific access method. Having a nonclustered index row U locked does not prevent another query acquiring a U lock on a different index (...


5

This is best done outside of SQL Agent. Honestly, at 800+ jobs running every hour you should have moved to a different method long before now. My preference for this would be to create a single master SSIS package that reads in a list of jobs that need to be run and then starts up the first X in the list, then sleeps until the number of running jobs is ...


4

You can't replay them. If no one is listening, they're gone. Start up your own communication daemon to catch them. Then you can do whatever you want.


4

I did something like this a while back with good success, I used RabbitMQ and this plugin https://github.com/gmr/pgsql-listen-exchange Basically RabbitMQ Connects to PostgreSQL and listens for the notify event, then you can use RabbitMQ to aggregate that message to as many queues as needed and have an app consuming each queue


3

it's not clear what problem you think there is The problem is that you rely on non-deterministic behaviour (which, by being non-deterministic, is bound to change at any time). SQL is a declarative language. It does not define how you want to achieve something; it only defines what you want to achieve. Let's see what you want to achieve by rephrasing your ...


2

You have two question, let's start with the easiest. Math: rows / hours is: 300 Hz * 3600 s / hour = 1.08 Million rows Which database can handle ~1 Million rows / hour? Just about all of them. You just need to use the correct technique in order to achieve those numbers. Single row insert then commit can potential kill performance. This blog describes ...


1

When a process commits, the locks it held are immediately released to all. But the rows it inserted are not visible to other snapshots which started before it committed. So the just-unlocked rows are eligible for someone else to lock, when that someone else is not able to see the inserted rows that would cause them not to select the just-unlocked row. ...


1

So, here's my answer that came out of a very productive discussion with @RickJames. Thanks a lot! So, create the queue table like this. Please note that it supports multiple queues at once, and some other features. Also, the position can actually be added optionally as another table, which could be better if you're into modularity (I know I am). CREATE TABLE ...


1

(I'll compete with myself. Here's an Answer for a "large" number of items.) Updating 1K rows is not very fast, due to saving rows in case of rollback. Incrementing/decrementing the sequence numbers (column seq) for a range of rows, then changing one the sequence in the one row that was moved. That's 3 UPDATEs -- one slow, one fast. The UI would ...


1

For recent versions, you can use window functions: SELECT ID, CustomerID, Volume, Content, runtot FROM ( SELECT ID , CustomerID , Content , sum(Volume) over ( partition by CustomerID order by ID ) as runtot FROM multiqueue ) AS q1 WHERE runtot < 2000; EDIT: If you want at ...


1

Check out the locking that occurs in your stored procedure - the following is a simple example, you can adapt it to test your query and others. In case it needs to be clear, go to a quiet server for this, like a development enivironment or better yet, a local install. The last thing you want to be doing is holding transactions open for giggles in a ...


1

Check the queue monitor state in sys.dm_broker_queue_monitors for troubleshooting: SELECT QUOTENAME(OBJECT_SCHEMA_NAME(queue_id, database_id)) + N'.' + QUOTENAME(OBJECT_NAME(queue_id, database_id)),* FROM sys.dm_broker_queue_monitors; Rusanu has a good blog post (Understanding Queue Monitors) that describes the internals of the monitor state machine that ...


1

It seems that preventing the locking of candidate rows is impossible with an ordering on a column not part of the clustered index. I came up with a workaround, that seems to solve the problem on the minimal example i provided. I'll do some tests to make sure if it solves the actual production problem. The idea is to split dequeue operation into two steps. ...


1

I have done something similar with offline DBCC checks (two servers doing backup restores and then a DBCC checkdb). One server gathers all the 31 server's backups yesterday and puts them into a queue and then that server and another pull from that queue. While not a lot of servers, the method should remain the same: Have the application server run an update ...


1

You mentioned you could not use 'locking by deletion' because the row must stay in the table. The code example below does use it, but only after creating a global queue first and populating it. It then uses the 'locking by deletion' for the ##Queue table rather than the table you're working with. USE master GO -- 1. Create a Stored Procedure that ...


Only top voted, non community-wiki answers of a minimum length are eligible