suppose i need to make update the employee as following
CREATE PROC UPDATE_EMPLOYEE
(
@EmployeeID INT,
@EmployeeName NVARCHAR(50)
)
AS
update Employee set EmployeeName=@EmployeeName where EmployeeID=@EmployeeID
Suppose I have two useres A,B
A modified or edit using stored procedure above employee no 5000 field EmployeeName
B need to modify employee no 5000 using stored procedure above field EmployeeName
i need to prevent user B To modify same record using time stamp How to do that from c# code and sql server and what modification in stored procedure to accept timestamp
EmployeeTable
EmployeeID
EmployeeName
TimeStampEmp
Meaning allow to another user after specified time
Loading
Anil KumarPosted Feb 25, 2015, 11:24 PM
ahmed saPosted Feb 25, 2015, 3:01 PM
ahmed saPosted Feb 25, 2015, 3:00 PM
Anil KumarPosted Feb 25, 2015, 12:46 PM
You can use the ROWLOCK hint, but SQL may decide to escalate it if it runs low on resources
ROWLOCK Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.
and
Lock hints ROWLOCK, UPDLOCK, AND XLOCK that acquire row-level locks may place locks on index keys rather than the actual data rows. For example, if a table has a nonclustered index, and a SELECT statement using a lock hint is handled by a covering index, a lock is acquired on the index key in the covering index rather than on the data row in the base table.
There is also, the very in depth: Locking in The Database Engine (in books online)
So, in general
UPDATE
Employees WITH (ROWLOCK)
SET Name='Mr Bean'
WHERE Age>93
Should be ok, but depending on the indexes and load on the server it may end up escalating to a page lock.
To help others, please mark the solution as accepted if it solves the purpose