You can wrap a transaction around the record you are updating. You would set the isolation level for the type of locking you want to accomplish. A transaction’s isolation level controls how, or if, the work performed on a transaction will affect work performed on other transactions. SQL Server uses “read committed” as its default transaction isolation level. With this isolation level, rows are locked once they’re modified in the transaction. Simply retrieving the contents of a row in a SELECT query will not lock the row. If, however, you use an isolation level of “repeatable read” or “serializable,” you lock the rows you retrieve in a SELECT query.

string strSQL;
// With SQL Server, you can issue the following query to lock a row of
// data in a transaction, regardless of the transaction’s isolation level
strSQL = "SELECT CustomerID, CompanyName FROM Customers " +
         "WITH (UPDLOCK) WHERE CustomerID = 'ALFKI'";
SqlConnection cn = new OleDbConnection(strConn);
cn.Open();
SqlTransaction txn = cn.BeginTransaction();
SqlCommand cmd = new SqlCommand(strSQL, cn, txn);
SqlDataAdapter da = new SqlDataAdapter(cmd);

// Create command statements
...

DataTable tbl = new DataTable();
da.Fill(tbl);
DataRow row = tbl.Rows[0];
row["CompanyName"] = "Modified";
da.Update(tbl);
txn.Rollback();
cn.Close();

>Hello all, I use a table to keep track of my primary keys. Basically the table contains a field for the tablename and a field for the next available primary key. Is there a way to lock the sql server record until I update it? Also can I run a loop checking the status of the lock so I can grab another lock as soon as the first lock is complete? Thanks for the help.