Provide an example of a case with SELECT FOR UPDATE where it can lead to a deadlock.

A deadlock with SELECT FOR UPDATE can occur when two or more transactions try to acquire locks on the same rows but in different orders.

Example:

1. Transaction A executes SELECT * FROM accounts WHERE id = 1 FOR UPDATE; — locks the row with id=1.
2. Transaction B executes SELECT * FROM accounts WHERE id = 2 FOR UPDATE; — locks the row with id=2.
3. Transaction A tries to execute SELECT * FROM accounts WHERE id = 2 FOR UPDATE; — waits for the lock on id=2 to be released.
4. Transaction B tries to execute SELECT * FROM accounts WHERE id = 1 FOR UPDATE; — waits for the lock on id=1 to be released.

As a result, both transactions are waiting for each other, leading to a deadlock.

To avoid such situations, you should:

• Always lock rows in the same order.
• Minimize the time locks are held.
• Use timeouts or deadlock error handling.

Example code in Go using transactions and SELECT FOR UPDATE:

trx1, _ := db.Begin()
// Transaction 1 locks account 1
row1 := trx1.QueryRow("SELECT balance FROM accounts WHERE id = 1 FOR UPDATE")

// In another goroutine or process:
trx2, _ := db.Begin()
// Transaction 2 locks account 2
row2 := trx2.QueryRow("SELECT balance FROM accounts WHERE id = 2 FOR UPDATE")

// If trx1 now tries to lock account 2, and trx2 — account 1, a deadlock will occur

Thus, deadlock occurs due to cross-locking when using SELECT FOR UPDATE.