Earlier this week I had an interview at Andela, they offered us a challenge to build an API using postgreSQL and Flask. In my 3 years working as a software developer I had never interacted with PostgreSQL.
Fast forward I started experiencing weird bugs in my API. Postgres would freeze from time to time and this was because postgres would lock my db requests. I still don't understand this, why? What is the purpose of pg_locks?
Not really, I was using Pyscopg2 to handle queries between Flask and PostgreSQL. I though ideally instead of locking requests and freezing it should throw an error
Psycopg2 directly? With no other libraries? It might be that you're not closing connections after the end of the request, hence the lock.
So, they lock requests to the DB? I thought a good practice would to return an error instead of locking requests.
Oh, sorry my bad, I thought you wanted to understand the purpose of the view pg_locks, which is the name of a special view where PostgreSQL lists all the active locks (a locked table, or a row or others). Instead I think that you're actually asking what locks are for.
I thought a good practice would to return an error instead of locking requests.
It depends. If every concurrent operation would result in an error sent to the caller, those database would quickly become a single user and single process server.
Not very unlike locks in multithreading, database locks serve the purpose of protecting access to shared resources (tables, rows, data).
In a system where tens if not hundreds of connections operate on the same dataset, there has to be a system to avoid that two connections invalidate each other's operation (or in other cases causing a deadlock). Most RDBMS system promise you they are ACID, which means atomic, consistent, isolated and durable.
Locks are a way to do that. An operation comes to the DB, declares they need a resource, finishes its own modification, then releases such resource, so that the next operation can do the same. If they didn't lock their resource two operations might overwrite each other's data causing disasters.
Locks are not errors, they are handled automatically by the DB, unless you're doing something that abuses resources so you need to monitor the pg_locks table and understand what you're doing wrong.
Lock problems are not uncommon, but they are not that easy to trigger either.
Hi John, can you expand on what you're doing or how you're using PostgreSQL through flask?
pg_locks is a view of all the locks in the DB server.
Are you manually setting locks?