T.S. T.S. - 1 year ago 58
C# Question

Will Select Max(field) under transaction lock any rows?

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);

In the first
, will it lock records with
processNum = 1
? To me, it looks like nothing will be locked. Is there a good reason to execute
under transaction the way its done?

Answer Source

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.