Concurrency in Transaction

hi gurus

the scenario
Frontend - MS Access (not yet decided whether MDB or ADP)
Backend - MS SQL Server

it is a conversion from MS Access backend to MS SQL Server Backend.

Planning to create stored procedures for all the Inserts, Updates,
Deletes and Business Rules / Validations wherever it is possible.

the problem
i am running in concurrency problem. the same classic scenario of two
users retrieving the same row (record) from the same table. it allows
both the user to update the record, that is, the user who updates last
has his changes saved though he retrieved that particular record
second.

what i need is that the user who retrieved the record second shouldn't
be able to update or delete the record when it is already retrieved by
any other user.

would appreciate if someone pointed me in the right direction to solve
the above problem, i know it is related to isolation property but am
not sure

thanx in advance

regards
balabala (balkiir@.gmail.com) writes:
> the problem
> i am running in concurrency problem. the same classic scenario of two
> users retrieving the same row (record) from the same table. it allows
> both the user to update the record, that is, the user who updates last
> has his changes saved though he retrieved that particular record
> second.
> what i need is that the user who retrieved the record second shouldn't
> be able to update or delete the record when it is already retrieved by
> any other user.
> would appreciate if someone pointed me in the right direction to solve
> the above problem, i know it is related to isolation property but am
> not sure

One convenient solution is to use a timestamp column. A timestamp column
is a column which automatically is updated every time a row is update.
Timestamp has nothing to do with date and time, but is a 8-byte binary
value.

When you read a row, you retrieve the timestamp value to the client,
and then you use that in the WHERE condition when you update:

UPDATE tbl
SET ...
WHERE keycol = @.key
AND tstamp = @.tstamp

If @.@.rowcount is 0 after the update, this means that the tstamp value
that you read is no longer good, because someone else have updated the
row.

This is a little different from your request, as here the first process
that update wins, not the first that reads it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanx a lot erland. have a great day

Concatinating Select Results

Hey everyone,

I have an SSIS conversion issue. I'm pulling two tables from a DB2 database into SQL 2005. One table has a list of work orders, and the other has a list of work order comments. There is a unique identifier between the two tables so that a join can be used, however, due to size limitations, I need to be able to combine both tables.

The end result will be replicated out for SQL Mobile Edition and the file is too large when both tables exist so I am wanting to concatinate all the comments for each work order into a single text field in the work orders table.

Here is what I am wanting to accomplish:

UPDATE tblWorkOrders
SET Comments = (SELECT Comments
FROM tblComments
WHERE tblWorkOrders.ReqNum =
tblComments.ReqNum)

I know that this statement will not work because there is a one-to-many relationship between the tables so each work order could get multiple results.

I would appreciate any suggestions.

Thanks,

Lee.

There are probably a number of ways of doing this. The first thing that occurs to me is to use an asynchronous script component that takes a set of data (ordered by ReqNum). Inside the script component loop over the set of data, concatenating comments for each ReqNum.

-Jamie

|||Hey Jamie,

Thanks for the response. I'm very new to SSIS so that's a little over my head. Could you elaborate a little more on all that? Or can you think of an easier way of accomplishing this?

Thanks

Lee.