Relationship between Isolation Level and Locks

Question

I am trying to understand the relationship between Isolation Levels (http://msdn.microsoft.com/en-GB/library/ms173763.aspx) and locks (http://technet.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx). I read a similar question on here and the answerer said

"Locks are implementation details. If you have a transaction that contains a SELECT:

SELECT * FROM SomeTable

You only have to worry about what data you will see from SomeTable"

However, if we do not have to worry about them then why do we have: Table Hints (http://msdn.microsoft.com/en-us/library/ms187373.aspx). Do we have to think about locks?

Answer 1

Well, locks are partly an implementation detail. They are used (in SQL Server) to give you the guarantees that the isolation levels give you according to the standard. For example, SERIALIZABLE gives you as-if single-threaded access to the database. This is done by S-locking all data you have read in order to freeze it.

Locks are not purely a detail because the very details of a locking scheme leak out. They are detectable. Also, you can control locks using hints to create behavior that is not specified in the standard. Usually, one would do that to obtain stronger guarantees than before.