Hello all,

This is a newbie design question. I have a table with the following fields

TABLE A
ID, Prop1, Prop2

I expect that I might have multiple concurrent users accessing the same
record (in a recordset via SELECT). If I expect to have users modify either
Prop1 or (XOR) Prop2 I should also expect the DB to complain since one of
the users would have the record locked (I'm learning lots about locks).
Would that complaint be an ADO exception or would the locked out user just
wait for the record to be free? Would a better design for this scenario be

TABLE A
ID, Prop1

TABLE B
ID, Prop2

this way the users would not interfere?

Thanks

andrew wrote:
It depends - if you are using a cursor to do the update, whether you open it
with optimistic or pessimistic locking will be the determining factor. If
you are not useing cursors (rcordsets) to perform the updates, then the
database will control the transactions so that the first one will be done,
followed by the second.

Prop. Data (the "1" and "2") should be stored as data (in rows) not in
metadata (table.column names)

andrew (thegroup (AT) microsoft (DOT) com) writes:
This all depends on you implement the application. If you update only the
colunm that the user actually has changed, both changes will have effect.

Commonly, you update all columns, changed or not, and in this case one
change will overwrite another. There are mechanisms to prevent this from
happening, for instance timestamp columns. Or simply compare all columns
with the saved values which is what some client APIs do if you hand over the
responsibility to them, rather than crafting the UPDATE statements yourself.

As for the table design, it is impossible to say what is the best, as a good
database design is governed by several factors. In fact, I would say that
the particular problem you have presented carries very little weight. This
situations may be better handled in the application to permit concurrent
updates. But again, from your abstract description, it is impossible to
give an answer in either direction.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

andrew wrote:
attributes, i.e., multiple instances of the same type of property (same
datatype, etc.) If that's not the case, then my answer does not apply.