Locks and dirty writes for SQL dbs Lets say I have two transactions, t1 and t2, that write to the same two rows, r1 and r2. I understand a kind of “dirty write” would be if t1 overwrites t2 on r1 last while t2 overwrites t1 on r2.

Is it possible for such a situation to arise? Does it depend on the isolation level used? What would prevent such a problem?
Isolation levels are for reading data. Not sure what you mean by "dirty write". Any RDBMS wont allow dirty writes - since they follow ACID properties.

A write operation will always take X (Exclusive locks) till the transaction is completed. Multiple concurrent transactions will use MVCC - sql server uses locking and row versioning since 2005.

Read up - Paul White's series on Isolation levels.