Clojurians Log v2
sql
hi, morning i am trying to do lookup by primary key (uuid) with clojure.jdbc (database - postgresql) but it fails cause uuid is being treated as string
(jdbc/get-by-id db <table> #uuid "....")
(java.util.UUID/fromString uuid-string)
So I'm in a dumb, non-ideal position where I need to hack together the concept of 2 phase commits without actually being able to enable prepare transaction, commit prepared , and rollback prepared.
Basically it would go something like this:
(jdbc/with-transaction [conn (get-conn!)]
(let [data (get-postgres-data! conn)
update (whatever! data)]
(sql/update! conn update [<where>...])))get-postgres-data! can be locked in some way until after or sql/update! is executed. Am I barking up the wrong tree?
If you Google for Postgres “skip locked” you will find exactly what you want
@lancetarn @orestis thanks so much, just to close the loop on this:
(do
(future
(time
(do
(with-open [conn (pg-autoconn/auto-conn! "my_db")]
(jdbc/with-transaction [conn conn]
(let [rows (jdbc/execute! conn ["select * from client_notification_queue for update skip locked limit 3"])]
(Thread/sleep 3000)
(println "Expecting 3: " (count rows))))))))
(time
(do
(Thread/sleep 1000)
(with-open [conn (pg-autoconn/auto-conn! "my_db")]
(jdbc/with-transaction [conn conn]
(let [rows (jdbc/execute! conn ["select * from client_notification_queue for update skip locked"])]
(println "Expecting 1: " (count rows))))))))
;;=>
Expecting 1: 1
"Elapsed time: 1015.007809 msecs"
Expecting 3: 3
"Elapsed time: 3025.2133 msecs"(excuse the hideous code, just trying to get the concept worked out)
It is already uuid
Oops, sorry, misunderstood.
Possibly misunderstanding, but would SELECT FOR UPDATE be a possibility? You can accidentally wind up locking more than you mean, but perhaps is what you are looking for?
(do
(future
(time
(do
(with-open [conn (get-connection!)]
(jdbc/with-transaction [conn conn]
(jdbc/execute! conn ["lock table table_name in share mode"])
(Thread/sleep (* 10 1000))))
(println "lock released!"))))
(time
(do
(with-open [conn (get-connection!)]
(jdbc/with-transaction [conn conn]
(sql/insert! conn :table_name {:identifier "ha123"})))
(println "inserted!"))))select for update is probably what I'm looking for, I need to find the right combination of locks!Although in this case I'm making a relatively low throughput queue and data integrity is more important than throughput
Yeah locking entire tables in my experience can make all kinds of things sad very fast, but as always “it depends.”
any suggestions on better locking practices?
Here's the scenario:
I want multiple lockers workers to be able to pull jobs from a queue-table. Each row represents a task. The only thing they are required to do is pickup a row where status is NULL , update status = 'pending', do some work, and then set status = 'done' or delete the row. I don't want two workers to pickup the same row/task.
I really only need one worker but I need to guard against multiple folks running the app simultaneously, a job running twice is not acceptable in this scenario
I do think SELECT FOR UPDATE is what you want, provided you can make the status = 'pending' update and commit immediately… but there are some much deeper SQL gurus in here. If you have a larger number of workers and having them pick jobs serially is bad, I have seen a technique against MySQL where you get a list of “potentially eligible” rows using the WHERE status is NULL criterion in a non-locking way, then “pick” one somehow (random, time-sorted, whatever) and SELECT FOR UPDATE on the primary key to lock only the job you are about to adopt, with retries in the case of lock expiration to just try to grab another. I’m sure this too has contention issues with some number of thundering workers if you are trying to order the jobs, but hey. I also don’t know enough about the locking semantics of Postgres to give high-performance advice, but it is a fun subject 🙂