Handling table locks and transactions in PostgreSQL vs MySQL: Achieving equivalent behavior
Hey guys, in MySQL, I'm used to handling table locks and transactions like this:
lock table employees write, msg write; select cleanupDB(:LIFETIME, :NOW); unlock tables;
When i mark query as a transaction, i simply add "begin" string infront of query, and then execute with "commit":
if (query.transaction) {
query = "begin;";
}
.....
sql.execute("commit")
This approach provides atomicity without explicitly starting a transaction. However, in PostgreSQL, I'm trying to achieve similar behavior with:
LOCK TABLE employees IN ACCESS EXCLUSIVE MODE; LOCK TABLE msg IN ACCESS EXCLUSIVE MODE; CALL cleanupDB(:LIFETIME, :NOW);
I understand that in PostgreSQL, LOCK TABLE automatically starts a transaction if one isn't already in progress. How can I achieve the same level of atomicity in PostgreSQL without explicitly using BEGIN and COMMIT(without starting a transaction)? Is there a way to separate the concept of table locking from transaction management in PostgreSQL, similar to how it works in MySQL?
If anyone know the answer, i would really appreciate your help. Thanks.