Solved

SELECT FOR UPDATE postgres

Posted on 2013-05-31
9
858 Views
Last Modified: 2013-07-09
I am working on a mutli-threaded application that is using a table to keep track of what each thread is doing. Now this is something I have done using mysql quite a bit but it seems postgres is different.

Here is my select:
select
                m.membid AS xmembid,
                m.demoids AS xdemoids,
                m.areaids AS xareaids,
                ma.hzip AS xhzip,
                c.ddaccount AS xddaccount,
                z.latitude AS lat,
                z.longitude AS lon,
                mb.partid
              FROM
                members m
              LEFT JOIN creditcards c ON
                m.membid = c.membid
              LEFT JOIN membaddr ma ON
                m.membid=ma.membid
              LEFT JOIN zipcodes z ON
                ma.hzip = z.zipcode
              LEFT JOIN membpart mb ON
                mb.membid = m.membid
              LEFT JOIN targeted_offers_scores_1367949828 t ON
                m.membid = t.membid
              WHERE
                m.cobrid = 116
                AND m.membtype!='anon'
                AND m.active='t'
                AND t.processed = 'f'
              ORDER BY m.membid
              LIMIT 10

Open in new window


Based on the results of this query I want to update targeted_offers_scores_1367949828 and set processed to true. However I want to lock the rows in targeted_offers_scores_1367949828  so the other threads can grab different rows.
0
Comment
Question by:skione
9 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 39211878
I suggest you to wrap your code in  a explicit begin tran/commit tran so all code either succeds or not - "all-or-nothing operation".


http://www.postgresql.org/docs/8.3/static/tutorial-transactions.html
0
 

Author Comment

by:skione
ID: 39211882
I understand transactions, I have since found out that PSQL doesn't work with LIMIT like mysql does. So I need a different way but pointing me to the documentation I have already read doesn't really help.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39211985
According to this, it looks like the PostGreSQL LIMIT clause works just like MySQL.
http://www.postgresql.org/docs/8.1/static/queries-limit.html
0
 

Author Comment

by:skione
ID: 39211989
I says so but I think when combined with FOR UPDATE/SHARE it doesn't work. At least not in my experiments
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39211990
PostGreSQL row-level locking appears to be an inherent component of a transaction.
http://www.postgresql.org/docs/9.1/static/explicit-locking.html
0
 

Author Comment

by:skione
ID: 39215113
Perhaps I didn't explain my goals. I am trying to do something that I have done with mysql many times. I am trying to build a queue like system. I grab a SELECTion of rows, process them and then update them, marking them as complete. The select grabs a batch of rows (let
's say 1000 at a time), so I can multi-thread the process. I will eventually need to process 10 million rows in < 24 hours. I can process 2 rows per second, so I need many threads to achieve that goal. The way PSQL handles LIMITs is different than mysql. Normally I would just do a SELECT LIMIT 100 FOR UPDATE but I can't since PSQL fires the LIMIT from row 0 even with an offset when you combine it with a FOR UPDATE/SHARE. So how can I get this done?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39215156
Ahh, now we are maybe starting to peel the onion.  Please show us your test data set, including the SSCCE.  Perhaps we can use your test data to simulate the issues and help you find a way to the answer.
0
 
LVL 22

Accepted Solution

by:
earth man2 earned 500 total points
ID: 39224109
http://www.postgresql.org/docs/9.1/static/explicit-locking.html#ADVISORY-LOCKS

SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- danger!
SELECT pg_advisory_lock(q.id) FROM
(
  SELECT id FROM foo WHERE id > 12345 LIMIT 100
) q; -- ok
0
 

Author Comment

by:skione
ID: 39229894
So I actually have this working without the need to lock rows but I still want to know how to get this done.

Ray_Paseur:
So in mysql I would do something like this:

START TRANSACTION

SELECT m.id 
, c.id AS cid 
, m.sid AS sid 
, m.type AS type 
, m.title AS title 
, m.email AS email 
, m.phone AS phone 
, m.message AS message 
, s.server AS server 
, s.username AS username 
, s.password AS password 
, c.category_name AS category_name 
, m.mid AS mid 
FROM bm_message_queue m 
LEFT JOIN bm_categories c on m.cid=c.id 
LEFT JOIN bm_servers s on c.server=s.id 
WHERE m.stamp <='". $current_time ."' AND m.type = 'sms' 
AND (mid=0 OR mid is null) AND sent=0 limit 
100 FOR UPDATE;


//execute and loop through select:
UPDATE bm_message_queue SET sent =1 WHERE id = {current row ID}

COMMIT

Open in new window


So this pseudo code is pulled directly from a working example, there is some error checking and roll backs but this is the heart of my goal. I select 100 rows and lock them, I loop through them, presumably doing something (like sending a message), then mark it as sent. Nothing gets done until I commit the transaction but since the rows are locked no other thread can touch them
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This article discusses how to create an extensible mechanism for linked drop downs.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now