Opinions - with MS SQL Server - how do you lock?

[pranoy]

Sorry..TG u r right. I dont know what I was thinking when I wrote that.

[dee-u]

I've edited my sample, what would be the value of Field1 with each scenario I've given? Specially at the same millisecond, will it differentiate them through nanoseconds? And what if they are also executed at the same nanosecond?

[szlamany]

I've edited my sample, what would be the value of Field1 with each scenario I've given? Specially at the same millisecond, will it differentiate them through nanoseconds? And what if they are also executed at the same nanosecond?

With a SQL database there is nothing being executed at the same exact time. Everything is sequentially processed - every request for ACTION is put into the LOG file for processing. If the LOG file has two requests to update the same row in it - one will be first, and one will be second - that is the nature of the sequential processing of the LOG file.

If the LOG file indicates that the row the second UPDATE is attempting to touch is locked, the the second ACTION request in the LOG file will hold until the first request processes OR is will deadlock and timeout.

There is no such thing as two requests occuring at the same time. Remember that a single program called SQLSERVER.EXE is taking each ADO request for ACTION on the DATABASE and processing them sequentially.