Regarding SQL Server Locking Mechanism

Question

I would like to ask couple of questions regarding SQL Server Locking mechanism

1. If i am not using the lock hint with SQL Statement, SQL Server uses PAGELOCK hint by default. am I right??? If yes then why? may be its due to the factor of managing too many locks this is the only thing i took as drawback but please let me know if there are others. and also tell me if we can change this default behavior if its reasonable to do.

2. I am writing a server side application, a Sync Server (not using sync framework) and I have written database queries in C# code file and using ODBC connection to execute them. Now question is what is the best way to change the default locking from Page to Row keeping drawbacks in mind (e.g. adding lock hint in queries this is what i am planning for).

3. What if a sql query(SELECT/DML) is being executed without the scope of transaction and statement contains lock hint then what kind of lock will be acquired (e.g. shared, update, exclusive)? AND while in transaction scope does Isolation Level of transaction has impact on lock type if ROWLOCK hint is being used.

4. Lastly, If some could give me sample so i could test and experience all above scenarios my self (e.g. dot net code or sql script)

Thanks Mubashar

Answer 1

1. No. It locks as it sees fit and escalates locks as needed

2. Let the DB engine manage it

3. See point 2

4. See point 2

I'd only use lock hints if you want specific and certain behaviours eg queues or non-blocking (dirty) reads.

More generally, why do you think the DB engine can't do what you want by default?