Record and Table locking in C# WinForms with PostgreSql and ADO.NET

Question

I am using.NET Framework 4.6.1, WinForms, PostgreSQL 6.4beta4 and Npgsql and ADO.NET.

My current application is a multi-user-application where all users connect to the same database.

Data gets bound to the controls by using DataTable, BindingSource, and BindingNavigator.

I want to avoid that two users can edit a DataRow at the same time. Because I want to implement this on a more general approach I was thinking about creating a DataTable descendant and add the property LockMode (None, Row, Table).

I found out that you can use the ColumnChanged event in combination with RowState to detect changes on the data.

I now know whether the user is inserting a new value, editing (RowState = modified) an existing one or just looks (RowState = Unchanged) at the record.

Therefore I am looking for a solution to lock the DataRow once a user starts editing it. In the application, i want to display a message once a user navigates (by using the Bindingnavigator or programmatically) to a locked record.

Most solutions I found target MySql Server like this one: How to perform a row lock? or TransactionScope locking table and IsolationLevel.

However I am looking for a PostgreSQL solution, so even articles on this topic from MS (https://msdn.microsoft.com/en-us/library/system.transactions.transactionscope(v=vs.110).aspx) cannot be used here.

I would appreciate if someone with experience in PostgreSQL and ADO.NET could help me out here. Thanks.

Thoreau

In NET, the data you load from the client is "disconnected" from that in the DB so there is no link. Edits to that data typically do not matter because they easily be undone/discarded. Additionally, what if someone starts to edit a row and is interrupted by an Important Phone call? Do you want all users locked out for the duration? What if they then go to lunch or get called away by The Boss? – Shanon

Yes, if someone starts edit a row i want to make it non-editable by other users, even if that is over a long period of time. – Thoreau

The simplest solution might be adding a column (bit) that flags a row to be in editing mode. Once a user starts editing, set the bit (1). After user is done editing, set the bit (0) – Hollywood

So something like the accepted answer here: #21284771 ? – Thoreau

Answer 1

You have to sync your clients to achieve that.

I would add an extra nullable date column (RowIsBeeingEdited), indicating the time when the row started beeing edited. I would set the row editable/not on client app start out of row[RowIsBeeingEdited] value.

Also I would implement two signals: {UserStartedEditingRow} & {UserFinishedEditingRow}, that would propagate to all clients, indicating, that client X started/finished editing row Y.

On begin edit row I would set row[RowIsBeeingEdited] = {now} and send {UserStartedEditingRow} signal. On end edit I would set row[RowIsBeeingEdited] = null and send {UserFinishedEditingRow} signal. Currently active clients should receive both signals and set row editable/not.

Hope that has some value.

Purgatory

Answer 2

Suggestion provided by @lokusking sounds good. Easy for maintenance and extend-ability.

Madrigalist

Answer 3

You don't need to use the client module in this. If you want the changes to be displayed in another client's table, either dynamically update the table data in case of a change, or send notifications that the [yourID yourName] record has been changed, and give the user the opportunity to see the changes. It is pointless to block a form element for this task, because two users don't use the same device at the same time.

Etheline

I think your answer would probably better appreciated, if you would elaborate the details of how to apply the solution you propose. – Duelist

Answer 4

You should add a RowVersion or Timestamp typed column in the table, and use that as a concurrency token in the update statement and throw a concurrency exception when the value is changed before submitting user changes.

Airless