Transaction Isolation Level

I want to tell you about Transaction Isolation Level in SQL Server 6.5 and SQL Server 7.0, what kinds of Transaction Isolation Level exist, and how you can set the appropriate Transaction Isolation Level.

There are four isolation levels:

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE


SQL Server 6.5 supports all of these Transaction Isolation Levels, but has only three different behaviors, because in SQL Server 6.5 REPEATABLE READ and SERIALIZABLE are synonyms. It because SQL Server 6.5 supports only page locking (the row level locking does not fully supported as in SQL Server 7.0) and if REPEATABLE READ isolation level was set, the another transaction cannot insert the row before the first transaction was finished, because page will be locked. So, there are no phantoms in SQL Server 6.5, if REPEATABLE READ isolation level was set.

SQL Server 7.0 supports all of these Transaction Isolation Levels and can separate REPEATABLE READ and SERIALIZABLE.
Let me to describe each isolation level.

read uncommitted

When it's used, SQL Server not issue shared locks while reading data. So, you can read an uncommitted transaction that might get rolled back later. This isolation level is also called dirty read. This is the lowest isolation level. It ensures only that a physically corrupt data will not be read.

read committed

This is the default isolation level in SQL Server. When it's used, SQL Server will use shared locks while reading data. It ensures that a physically corrupt data will not be read and will never read data that another application has changed and not yet committed, but it does not ensure that the data will not be changed before the end of the transaction.

repeatable read

When it's used, the dirty reads and nonrepeatable reads cannot occur. It means that locks will be placed on all data that is used in a query, and another transactions cannot update the data.

This is the definition of nonrepeatable read from SQL Server Books Online:

nonrepeatable read
When a transaction reads the same row more than one time, and between the
two (or more) reads, a separate transaction modifies that row. Because the
row was modified between reads within the same transaction, each read
produces different values, which introduces inconsistency.
serializable

Most restrictive isolation level. When it's used, then phantom values cannot occur. It prevents other users from updating or inserting rows into the data set until the transaction is complete.

This is the definition of phantom from SQL Server Books Online:

phantom
Phantom behavior occurs when a transaction attempts to select a row that
does not exist and a second transaction inserts the row before the first
transaction finishes. If the row is inserted, the row appears as a phantom
to the first transaction, inconsistently appearing and disappearing.
You can set the appropriate isolation level for an entire SQL Server session by using the SET TRANSACTION ISOLATION LEVEL statement. This is the syntax from SQL Server Books Online:

SET TRANSACTION ISOLATION LEVEL
{
READ COMMITTED
| READ UNCOMMITTED
| REPEATABLE READ
| SERIALIZABLE
}
You can use DBCC USEROPTIONS command to determine the Transaction Isolation Level currently set. This command returns the set options that are active for the current connection. This is the example:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
DBCC USEROPTIONS
GO
This is the result:

Set Option Value
------------------------------ ------------------------------------
textsize 64512
language us_english
dateformat mdy
datefirst 7
isolation level read uncommitted

textbox value should not be more than 14 character and take only integer value

//textbox onblur Event

asp:TextBox ID="TextBox1" runat="server" onblur="Checktextboxlenth(this);">/asp:TextBox>



//.js file

function ValidateCCNum(ccNum)
{
var ccno = ccNum.value;
if(ccno == "")
{

return true;
}

if(isNaN(ccno))
{

alert("Credit card number should be numeric");
ccNum.value="";
ccNum.focus();

return false;
}


if(ccno.length < 14 || ccno.length >18)
{

alert("Credit card number should have 14-18 digits length");
ccNum.value="";
ccNum.focus();

return false;
}
return true;
}

text box will not take more than 5 character

//.js file

function Checktextboxlenth(ccNum)
{
var ccno = ccNum.value;
if(ccno.length < 0 || ccno.length >5)
{
alert("length cant acces 5 character");
return false ;
}

return true;
}

////onpageload

btnId1.Attributes.Add("onclick", "Checktextboxlenth(TextBox1)");

Kumaoni folk song(Holi)

Find number of columns in a table in sql server 2000 and 2005 also

select count(*) NoOfColumns from SYSCOLUMNS
WHERE id= (Select id from SYSOBJECTS where name = 'emp')

What is live lock and deadlock? what is Lock escalation?

In SQL Server Live Lock occurs when read transactions are
applied on table which prevents write transaction to wait
indefinitely and Deadlocking occurs when two user processes
have locks on separate objects and each process is trying to
acquire a lock on the object that the other process has.

Lock escalation is the process of converting a lot of low
level locks (like row locks, page locks) into higher level
locks (like table locks).

What is OPENXML in SQL Server?

OPENXML can parse the xml data in SQL server very efficiently in SQL Server. OpenXML primarily gives the ability to insert XML data to the relational database, however we can query the data too using OpenXML. We need to specify the path of the xml element using xpath.