OLTP in SQL Server 2008

150 pts.
SQL Server 2008
SQL Server 2008 administration
While using OLTP in Micrsoft SQL 2008 enterprise edition, does the row level locking happens or table level locking happens?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Neither by default. By default SQL Server uses page level locking. Row and table level locking can be forced by using an index hint.

Discuss This Question: 4  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Akansha
    Does this forced locking affect performance. ?
    150 pointsBadges:
  • carlosdl
    It could. Locking at a smaller level could improve concurrency but adds additional overhead because more locks must be held if many rows are locked. Changing the default locking level is something that needs to be carefully analyzed, but that doesn't mean it will always have a noticeable negative impact on the system.
    85,865 pointsBadges:
  • Kccrosser
    A bigger factor in performance is the unnecessary use of locks. Many queries in most systems could use the "with (nolock)" hint to avoid unnecessarily locking or blocking on existing locks. You need to make sure you understand the implications, but if your queries are accessing relatively static data, or you don't care if you see the results of a concurrent transaction immediately, using "with (nolock)" can dramatically reduce lock contention and increase system performance. Also, ensure that all transactions are made as small as possible to minimize the time that locks are active. A classic performance killer is to start a transaction, then do a query against a table using a non-indexed column. Until the transaction completes, the entire table will usually be locked, along with any other tables being used in the transaction. Any queries within a transaction should only be on well-indexed columns, preferably only on primary keys or other unique indexes, to minimize lock escalations. And further, wherever possible, avoid any queries within a transaction.
    3,830 pointsBadges:
  • The Most-Watched IT Questions: May 4, 2010 - ITKE Community Blog
    [...] OLTP in SQL Server 2008, asked by Akansha and answered by MrDenny, CarlosDL and [...]
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: