SQL - usage of Transaction and Lock

Below is an example of using "Transaction" / "Lock" to update / insert a record

{
Random random = new Random();
strTxID = "TX_" + random.Next(1000).ToString();

strCmd = @"BEGIN TRANSACTION " + strTxID;
cmd.CommandText = strCmd;
cmd.ExecuteNonQuery();

// check if record with specified recid already exist
strCmd = "select count(*) as NumRec from XXX with (HOLDLOCK) where SIN='...
cmd.CommandText = strCmd;
iNumRec = (int)cmd.ExecuteScalar();
....

// form value of SID (session ID)
strCmd = "insert into XXX with (HOLDLOCK) (SID,...
cmd.CommandText = strCmd;
cmd.ExecuteNonQuery();
…

// commit Tx
strCmd = @"COMMIT TRANSACTION " + strTxID;
cmd.CommandText = strCmd;
cmd.ExecuteNonQuery();
....
}