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:
> 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?

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.

> Would a better design for this scenario be
> TABLE A
> ID, Prop1
>
> TABLE B
> ID, Prop2
>
> this way the users would not interfere?
>
No, the best design would be a single table with ID, PropType (1 or 2) and 
Prop. Data (the "1" and "2") should be stored as data (in rows) not in 
metadata (table.column names) 

andrew (thegroup@microsoft.com) writes:
> 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). 

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@sommarskog.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:
> 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?
>
My answer is based on the assumption that Prop1 and Prop2 are "repeating" 
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.