SQL by default uses locks for transaction isolation, while Oracle uses a form of row-versions.

In SQL, if you issue an update within a transaction, the locks taken by that update will be held until the transaction commits or rolls back. It's an exclusive lock (for data modification) and is not compatible with shared locks needed for reads. Hence the select has to wait until the transaction is committed or rolls back before it can read a locked row.

SQL 2005 and higher do support the isolation method that Oracle uses, in the Snapshot and Read_Committed_Snapshot isolation levels. They do have a large impact on TempDB, but they will allow select queries to read the old version of rows locked for modification.