Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

> you should do a locking read on that something else

How to do this tho? Do you mean like this?

BEGIN TRANSACTION

// all concerned rows in the select statement

SELECT * FROM A, B... FOR SHARE;

// update relevant rows

UPDATE A SET x = true ... FOR UPDATE;

COMMIT

END



It kinda depends. I might do it like this if its super simple:

UPDATE A, B SET A.x = B.x where A.b_id = B.id and A.id = 1;

If it's more complex, it might have to be more like this:

SELECT * FROM A ... FOR UPDATE;

SELECT * FROM B... FOR SHARE;

UPDATE A...

If B is only ever updated after locking A, you can safely do this:

SELECT * FROM A ... FOR UPDATE;

SELECT * FROM B...;

UPDATE A...

Generally speaking, I wouldn't recommend locking "for share" then updating it later. This can result in deadlocks because you're upgrading it to an exclusive lock.




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: