Could you kindly clarify the following circumstance for me?
In my project, I’m using Postgresql 12 as the primary database, with many background tasks accessing and writing to the database in parallel, as well as some user interactions (which of course produce updates and inserts to the database from the front of the application.
I receive exceptions like this one every now and then:
SQLSTATE[40P01]: Deadlock detected: 7 ERROR: deadlock detected
DETAIL: Process 18046 waits for ShareLock on transaction 212488; blocked by process 31036.
Process 31036 waits for ShareLock on transaction 212489; blocked by process 18046.
HINT: See server log for query details.
CONTEXT: while updating tuple (1637,16) in relation "my_table"
During my transactions, I do not manually lock any rows or tables within my program. However, I regularly have ‘big’ transactions that can edit a significant number of rows in a single operation. So here are the questions:
Do ordinary transactions generate table-wide or row-wide locks? (I presume yes unless the entire event is magical. After reading from here, I want to ask shouldn’t the RDBMS automatically fix such issues when two queries attempt to alter the same resource while wrapped in a transaction?
If the answer to the second question is “no,” how should I approach similar situations?