We have this code and I am wondering, under normal Ado.net transaction will it actually lock any rows? Sample:
var sql1 = "SELECT MAX(entryNum) FROM tbl1 WHERE processNum = 1";
var cmd = new SqlCommand(sql1, conn, tran);
var newEntryNum = cmd.ExecuteSacalar();
. . . . . .
var sql2 = "INSERT INTO tbl2 (entryNum) VALUES (" + newEntryNum + ")";
cmd = new SqlCommand(sql2, conn, tran);
processNum = 1
By locking a row, I assume you mean to prevent another transaction from modifying the row? I mention this because databases place all kinds of locks at different times that result in different locking behaviors.
In any case, the answer to your question has nothing to do with the programming language or ADO.NET. It is entirely dependent on the database and isolation level used for your transaction.
For instance, in SQL Server, if you're running in
read committed mode (which is the usual I would say), once the
select query completes, it will not prevent another transaction from modifying the rows with
processNum = 1.
However, if you are in
repeatable read or
serializable mode, then yes, executing the
select query will place locks on the rows with
processNum = 1 that will prevent other transactions from modifying the rows until you complete your transaction.
You can read more about how SQL Server places locks for different isolation levels here: SET TRANSACTION ISOLATION LEVEL.
In a previous edit of your question, you were also wondering about how this is handled in an Oracle database. That one is easy: no matter which isolation level you use, readers never block writers, so the
select query would never prevent other transactions from modifying the rows.