Re: Multiple concurrent isolated transactions on ADO Dataset

Thanks for the reply Chris :)

Well, your suggestion is along the lines of what i'm trying to do:

I have command objects that broadly have "Do", "Undo" and "GetData" methods
on them.

When a command is called (e.g. Deallocate), the GetData is called, and the
command object populates a user-specific dataset with the necessary data
from the central shared dataset, then the "Do" does the work on it.

Then, the allocate command would do similar. The commands are stacked up
(there could be many of them), so that when the user "Commits", they can be
run again, but this time, the data is retrieved directly from the database,
and the commands are run consequitively; all wrapped withing a database
transaction which succeeds or fails.

This is quite a common pattern, i believe, but it's not perfect: whenever a
query is performed to display data to the user, it must take into account
the changes he's made to his personal dataset... so if there's one dataset
with all the data, and one with his changes, the query has to run over them
both. A simple "GetAllocations" query then becomes a lot more complex.

Another suggestion could be to build up a stack of SQL statements (inserts,
deletes, updates etc.) that represent the commands, and then begin a
transaction and apply them all in order each time a query needs to be made,
to get the correct state of the database for the user, then rollback the
transaction. (Obviously, when the user presses "Commit", the transaction is
committed). There is some appeal to this, but it still doesn't seme right to
have to re-create transactions each time a view of pre-commit-user-specific
changes is required.


Regards,
Mark.





"christian kuendig" <xxxx> wrote in message
news:uaG%231EyWFHA.3464@xxxxxxxxxxxxxxxxxxxxxxx
>I still don't see the problem
>
> for you're scenario, you take out the data you need from the database
> change it and write it back. You just don't need to write back temporary
> results while you're in a business transaction.
>
> Person A want's to change the seat:
> - he checks the current status of seats and sees 89 is not taken yet,
> so he changes his local data: he removes him self from 171 and places him
> on 89 - just in the small dataset that contains the seat reservation
> relevant to him
> - when he comits, after his coffe break and phone calls, optimistic
> concurrency is applied
> - he won't be rejected on removing him from seat 171 because the
> customer only talked to one travel agent
> - he will maybe fail to change to seat 89 (be cause first come
> first serve or optmistic concurrency detected modifications on that
> "row" - or how ever this is done...) then the whole sysem transaction is
> rolled back which consists of removing him from 171 and placing him on 89
> (but it runs only for milliseconds)
>
> if it's web app there might not even be a dataset involved
> - the guy just says he want to change the booking, sees a list of
> available seats, clicks on one and the system will change the booking - if
> the list he saw was 2 hours old chances are high that the system detects
> concurrency vialoations (optimistic once in that case) and again the
> system transaction is rolled back...
>
> maybe you should try to get your datasets smaller and fill them on demand
> with data. I mean, it doesn't make sense to fill the dataset with all
> flight reservation informations for all the flights of the next 12 months
> when a customer is only interested in one particular flight. Then you're
> master dataset is suddenly the database and the datasets you have with the
> chances contain more than the changes but not all the data but only the
> one relevant to the user or the use cases that user is working in.
>
> Since it's likely that this client is human it's natural that he will have
> a cetain limitation on data he can work on in one particular use case or
> maybe a series of use cases. So, I still think you can get rid of the big
> caching dataset.
>
> cheers
>
> chris
>
> "Mark Roberts" <Mark_Roberts@xxxxxxxxxxxx> schrieb im Newsbeitrag
> news:u6PumnvWFHA.3864@xxxxxxxxxxxxxxxxxxxxxxx
>> Well, i have a situation where it's fairly critical for changes made to
>> the data to be only visible to the person making the changes (until
>> commit), whilst the unchanged version should be visible to all other
>> users... until they make changes.
>>
>> When the changes are committed, it's a first come, first served. I.e.
>> it's possible for person B to have their changes rejected as a result of
>> person A's changes.
>>
>> I'm using a command pattern to implement the updates, inserts and
>> deletes, and stacking them up for the final commit...
>>
>> Scenario:
>> Imagine a plane with 200 seats, and person A is allocated to seat 171,
>> but wants to be allocated to seat 89... the changes necessary re-allocate
>> him would be to deallocate from 171 and allocate to 89. Say the user was
>> interupted on the phone between deallocating from 171 and re-allocating
>> to 89, and at that same time, another user allocated someone to 89 and
>> another to 171... Person A would now be without a seat on the plane.
>>
>> Sorry for silly example :)
>>
>> Mark.
>>
>>
>>
>> "christian kuendig" <xxxx> wrote in message
>> news:uSFMEdvWFHA.1044@xxxxxxxxxxxxxxxxxxxxxxx
>>> You don't need to keep the connection open
>>>
>>> Why don't you use optimistic or pesimistic offline locking? (the
>>> optmisitc one is more or less included in the dataadapter)
>>>
>>> "Mark Roberts" <Mark_Roberts@xxxxxxxxxxxx> schrieb im Newsbeitrag
>>> news:u%23B7CWvWFHA.3760@xxxxxxxxxxxxxxxxxxxxxxx
>>>> Hi
>>>>
>>>> No, it's not really possible to operate directly on the database
>>>> unfortunately - the changes that the user can make will be over a long
>>>> time (i.e. could be 1-2 hours, maybe more)
>>>>
>>>> I don't really want to attempt to keep a database transaction open for
>>>> that long :)
>>>>
>>>> Regards,
>>>>
>>>> Mark.
>>>>
>>>>
>>>>
>>>> "Rogas69" <rogas69@xxxxxxxxxxxxxxxx> wrote in message
>>>> news:OpQNrSvWFHA.1796@xxxxxxxxxxxxxxxxxxxxxxx
>>>>> What database do you use? You describe scenario that is easily handled
>>>>> by practically any database. You said that amount of data is huge -
>>>>> creating per-user copies will kill your server - think about memory
>>>>> allocation and copying. Is it not possible to operate directly on
>>>>> database?
>>>>>
>>>>> Peter
>>>>>
>>>>> "Mark Roberts" <Mark_Roberts@xxxxxxxxxxxx> wrote in message
>>>>> news:exDNqHvWFHA.612@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>> Hi,
>>>>>>
>>>>>> I have been thinking about a problem for some time, and i'd like to
>>>>>> throw it open to the newsgroups in case someone has come across a
>>>>>> similar situation.
>>>>>>
>>>>>> I have a C# .Net 2.0 web application with a very large server-side
>>>>>> dataset (i.e. can only be 1 in memory).
>>>>>>
>>>>>> I have multiple users that wish to make changes to the dataset, and
>>>>>> each user's should be batched up and only visible to themselves until
>>>>>> they're committed, at which point they become part of the main
>>>>>> dataset.
>>>>>>
>>>>>> I am thinking of have one dataset per user as well as the main
>>>>>> dataset, and recording changes into the user-based dataset.
>>>>>>
>>>>>> This is ok, but it adds a lot of complexity when querying the data,
>>>>>> since queries are essentially across two datasets.
>>>>>>
>>>>>> Ideally, i'd like to have multiple transaction layers on the main
>>>>>> dataset, one for each user that isolates their changes, but permits
>>>>>> dataset searching to work in the context of their transaction until
>>>>>> it's committed or rolled back.
>>>>>>
>>>>>>
>>>>>> Many thanks!
>>>>>> Mark.
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: CommitBatchSize vs. CommitBatchThreshold for transactional dis
    ... I strongly suspect this 1 off command is the commit command. ... Commithreshold is honored at a transaction boundary. ... With regard to the threshold adding one more command that seems to be a bug. ...
    (microsoft.public.sqlserver.replication)
  • commit in Stored Procedure - Transaction Error
    ... There is a problem in executing the following stored procedure ... It is not having Begin Transaction but there is a commit ... If we start new transaction before calling the SP ... connection assigned to the command is in a pending local transaction. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: update rollback ... in theory
    ... would i need to roll the command into a transaction somehow? ... each statement is in an implicit transaction and will be irreversible if you make a mistake. ... I generally recommend an explicit transaction for ad-hoc statements so that you can commit or rollback as desired. ...
    (microsoft.public.sqlserver.server)
  • Re: Multiple concurrent isolated transactions on ADO Dataset
    ... User loads data with seats in plane. ... transaction and view should be updated after it. ... possibility that complex transaction that you would want to commit at the ... > When a command is called, the GetData is called, and the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Distribution Agent fails with error: Line 1: Incorrect syntax near ,null,
    ... You mentioned manipulating the command in the msrepl_commands table. ... transaction capable: 2 ... Last transaction timestamp: 0x000004810000069f000900000000 ... Transaction seqno: 0x00000481000006e70001 ...
    (microsoft.public.sqlserver.replication)
Loading