Solved

SELECT FOR UPDATE postgres

Posted on 2013-05-31
9
851 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

708 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

16 Experts available now in Live!

Get 1:1 Help Now