12

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.

Community
  • 1
  • 1
Thomas
  • 677
  • 1
  • 5
  • 19
  • 2
    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? – Ňɏssa Pøngjǣrdenlarp Aug 26 '16 at 14:20
  • 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. – Thomas Aug 27 '16 at 09:10
  • 2
    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) – lokusking Aug 27 '16 at 09:38
  • 1
    So something like the accepted answer here: http://stackoverflow.com/questions/21284271/how-to-implement-a-simple-lock-mechanism-for-multi-user-application ? – Thomas Aug 28 '16 at 07:02

3 Answers3

0

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.

Anton Maiorov
  • 181
  • 13
0

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

Mukul Varshney
  • 3,131
  • 1
  • 12
  • 19
-1

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.

dbc
  • 104,963
  • 20
  • 228
  • 340
Masud Safari
  • 108
  • 8