|
Recent
Articles |
Installing Fusebox
A quick editorial note. I mentioned last week that I would be playing with Fusebox and trying to learn the basics. In case anyone comes in via Google I wanted to be clear that these are my experiences as I learn. So please...
MySQL Administration Via ColdFusion
A user asked me if it was possible to backup and restore a MySQL database from ColdFusion. There are multiple ways of doing this, but the basic answer is that you can do this very easily. MySQL ships with a set of...
MySQL Optimization Tips
Every programmer loves to optimize, even when we know we shouldn't. To satisfy your cravings MySQL has several keywords that can be placed in your SQL statement...
Removing Nulls From A DataTable
The individual cells in a DataTable can have a null value in the form of System.DbNull.Value. If the DataTable is created by querying a database through ADO.NET, you can write the SQL statement in a way eliminates...
Compact An Access Database From .NET
Over time, a database becomes fragmented when it is used. This makes it grow in size and perform badly. Not the two qualities you want for your mission-critical...
Oracle Responds To Information Security Critics
Oracle the Database Company is taking time out on its corporate blog to address a number of critics on the security of Oracle Databases. Like any other application, the security of the application is only as good as...
The What, Why, And How Of Database Cleansing
Firstly the "What" - Cleaning a database is done to: Remove duplicate records. Ensure your data is consistently formatted. Correct data that is...
Oracle & Red Hat's Symbiotic Relationship
There is a symbiotic relationship between Oracle & Red Hat now. Yes there was one before with Oracle's database business on Linux, but it's even more intertwined now. This relationship is similar to what...
|
|
|
|
02.21.07
Record Locking In SQL Server
By
Kalpesh Bakotiya
Pessimistic and Optimistic Concurrency
In a multi-user environment, there are two models for updating data in a database: optimistic concurrency and pessimistic concurrency.
Pessimistic concurrency involves locking the data at the database when you read it. You essentially lock the database record and don't allow anyone to touch it until you are done modifying and saving it back to the database. Here you have 100% assurance that nobody will modify the record and while you check it have it checked out, out. Another person will have to wait until you have made your changes.
By default, SQL Server controls lock escalation, but you can control it yourself by using lock optimizer hints. Here are some lock escalation hints you may want to consider:
ROWLOCK - This hint tells SQL Server to use row-level locking instead of page locks for INSERTS. By default, SQL Server may perform as a page-level lock instead of a less intrusive row-level lock when inserting data. By using this hint, you can tell SQL Server to always start out using row-level locking. But, this hint does not prevent lock escalation if the number of locks exceeds SQL Server's lock threshold.
SERIALIZABLE (equivalent to HOLDLOCK) applies only to the table specified and only for the duration of the transaction, and it will hold a shared lock for this duration instead of releasing it as soon as the required table, data page, row or data is no longer required.
TABLOCK specifies that a table lock to be used instead of a page or row level lock. This lock will be remained held until the end of the statement.
TABLOCKX specifies that an exclusive lock will be applied held on the table until the end of the statement or transaction, and will prevent others from reading or updating the table.
UPDLOCK specifies that update locks will be used instead of shared locks, and will hold the locks until the end of the statement or transaction.
XLOCK specifies that an exclusive lock be used and kept activated held until the end of the end of the transaction on all data being processed by the statement. The granularity of XLOCK will be adjusted if it is used with the PAGLOCK or TABLOCK hints.
SQL Server 7.0 and SQL Server 2000 Lock Escalation Options
You can override how SQL Server performs locking on a table by using the SP_INDEXOPTION command. Below is an example of code you can run to guide tell SQL Server to use page locking, not row locks, for a specific table:
SP_INDEXOPTION 'INDEX_NAME', 'ALLOWPAGELOCKS', TRUE
When FALSE, page locks are not used. Access to the specified indexes is obtained using row- and table-level locks only.
Continue reading this article.
About the Author:
By Kalpesh Bakotiya
Kalpesh Bakotiya is working as a Programmer at Semaphore Infotech Pvt. Ltd, India. You can contact at email: kalpesh@semaphore-software.com
|
