Adam Adam - 1 month ago 13
C# Question

How can I safely exit a DBMS when records are locked?

I'm a hobbyist programmer but have been doing it a while. I'm writing a small document management application for use at work (in c#). When a user opens a record, I update it to indicate it's currently locked for editing.

What I'm not sure about is how to ensure the database gets updated when the application exits unsafely (eg. computer shuts down unexpectedly)? Also, how should I update it when the application exits via the computer being shut down by the user? I just want to make sure that I don't end up with records being marked as locked when nobody is viewing them.

Tim Tim
Answer

Here's how this is normally done with SQL Server. Developer-issued "record locks" are not relevant to client-server architecture. You are confusing shared-file architecture with client-server architecture.

Make sure the table has a timestamp column (which is automatically updated by the database engine).

Read in the row you want to edit. Put the timestamp from the row in a variable.

Update statement looks like this:

update myTable
set col = {some value}
where id = {your id}
AND
timestampcolumn = {the timestamp the row had when you read it in}

If someone has changed the row since you read it in, it will have a different timestamp and no record will match your WHERE clause conditions, and so your update will fail. You can then decide what to do.

You can pull the plug on the client PC when you're using SQL-Server (or Oracle or any true client-server architecture) without having any adverse impact on the server.

Comments