I feel like an idiot not knowing that the default isolation level for SQL Server (all of its versions as far as I know, 2000 and 2005 for certain) is "Read Committed".

Here is a summary of the MSDN information on this isolation level.

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

This means that if within a transaction first you select some rows and then you try to update the selected rows, during the update the rows are not guaranteed to have the same values as they did during the select.

Doh! I really should have known this as it is quite a dangerous isolation level. I always thought the default one was "Repeatable Read" ...