postgres deadlock without explicit locking
Suppose session 1 does:

begin transaction;
update a set col = val where pk = 1;
update a set col = val where pk = 2;
commit;


and at the exact same time session 2 does the opposite:

begin transaction;
update a set col = val where pk = 2;
update a set col = val where pk = 1;
commit;


Then session 1 will be waiting for session 2 to commit or rollback the update on the row where pk = 2 while at the same time session 2 will be waiting for session 1 to commit or rollback the update on the row where pk = 1. Deadlock.