CODEWITHSUNDEEP
databasepostgresqllocking
CircularCube
CircularCube

Reputation: 31

Is there a way to manually lock/unlock a table row in PostgreSQL that does not rely on transactions?

I am new to database locking, so please bear with me.

I have my SELECT statements in one file, and my UPDATE statements in another. Both make a connection at the start of the file, then disconnect at the end. What I am trying to do is lock the table row in the select statement, and if the user cancels or updates, release that lock.

I have tried advisory locks, but they unlock when I disconnect. All other locks that I tried do the same.

Is there a way that I can achieve what I want in my current DB structure, or do I need to rewrite the whole thing or brute force it with things like row_locked named boolean columns in my DB?

Thanks in advance for the help :)

Upvotes: 1

Views: 474

Answers (1)

user330315
user330315

Reputation:

If you need to persist that information across transactions and sessions, then yes, you need a column that stores that information.

I wouldn't use a boolean though, but a nullable timestamp column so that you can see when the row was locked (null means "not locked", not null means "locked"). This is very useful to cleanup "abandoned locks".

Upvotes: 4

Related Questions