PostgreSQL update from select inline query deadlock Edit

Murugan Andezuthu Dharmaratnam | 03 February 2022 | 206

when I call an update from select with join and where condition I was getting a deadlock. The solution is shown below.

Solution

Solution was to add a for update in the inner query . If FOR UPDATE or FOR SHARE is specified, the SELECT statement locks the selected rows against concurrent FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, or SELECT FOR UPDATE of these rows will be blocked until the current transaction ends. Also, if an UPDATE, DELETE, or SELECT FOR UPDATE from another transaction has already locked a selected row or rows, SELECT FOR UPDATE will wait for the other transaction to complete, and will then lock and return the updated row (or no row, if the row was deleted). Within a SERIALIZABLE transaction, however, an error will be thrown if a row to be locked has changed since the transaction started.

update tableA set tableA.columnA = x.ColumnB
	from
	(
		select with joins to other tables where condition for update
	) x
	where 
	x.column = TableA.column and condition