Showing posts with label solution. Show all posts
Showing posts with label solution. Show all posts

Monday, March 19, 2012

Identity sequence of multi-threads insertion

Recently I'm working on a multi-thread solution based on SQL-Server, now I'm facing such a problem:

Suppose I have process No.1(with multi-threads) inserting data to Table A, which has its identity column auto generated. And process No.2(also with multi-threads) retrieving data from Table A ,generate some records and insert the result into Table B. Both of these two processes are doing batch processing(batch retrieving and batch writing), and they are running parallelly.

Now since process No.2 retrieve data sequencely by the identity of Table A, it found there exists missing results. This is due to that records with bigger identities are not necessarily commited earlier than those who have smaller identities.

One direct solution is add one flag field in Table A indicating whether this record has been processed by process No.2, and each time it was processed , the field will be set. But unfortunatelly the table structure is not supposed to be modified.

So is there any other good solutions for this problem? Thanks.

A solution may be that process 1 uses row-level locking when select/update and process 2 use the readpast hint when selecting the records:

READPAST specifies that locked rows be skipped during the read. READPAST only applies

to transactions operating at the default READ COMMITTED isolation level, and will

only read past row-level locks. READPAST can only be used in SELECT statements.

Normal blocking can be worked around by having transactions read past rows being

locked by other transactions.
See the following article: http://www.sql-server-performance.com/rd_table_hints.asp

Else I cannot see any other solution except adding some external data structure to keep track of processing status.|||

Hi carlop, I'm afraid locks won't resolve this issue.

Maybe I should explain my problem more detailly.

Suppose Process No.1 have 3 threads working , and here are the identities generated after the insertion.

thread 0 : 1 , 4, 7

thread 1: 2 , 5, 8

thread 2: 3, 6, 9

And at one time point, thread 0 commited the transaction with 3 rows inserted , whilst Process No.2 is retrieving data from Table A , so only these 3 records were retrieved and processed. So Process No.2 will record "7" as the biggist identity it has processed, and next time it will start processing from identities greater than "7" . So records with identities 2, 5,3,6 are lost if thread 1 and thread 2 commited later.

|||You have to add an external struct that keeps track of the processing jobs. I see no other way.|||You could add another table that is logged to on insert to table A. Process 2 reads these records and deletes them from the new tableonce processed. When it goes back for a second time it reads the nest set to process and then deletes from. You could use Service Broker if you wanted as it has a nice queue mechanism|||

Maybe this is the only solution I think.

Thank you all.

Identity sequence for multithreads insert

Recently I'm working on a multi-thread solution based on SQL-Server, now I'm facing such a problem:

Suppose I have process No.1(with multi-threads) inserting data to Table A, which has its identity column auto generated. And process No.2(also with multi-threads) retrieving data from Table A ,generate some records and insert the result into Table B. Both of these two processes are doing batch processing(batch retrieving and batch writing), and they are running parallelly.

Now since process No.2 retrieve data sequencely by the identity of Table A, it found there exists missing results. This is due to that records with bigger identities are not necessarily commited earlier than those who have smaller identities.

One direct solution is add one flag field in Table A indicating whether this record has been processed by process No.2, and each time it was processed , the field will be set. But unfortunatelly the table structure is not supposed to be modified.

So is there any other good solutions for this problem? Thanks.

A solution may be that process 1 uses row-level locking when select/update and process 2 use the readpast hint when selecting the records:

READPAST specifies that locked rows be skipped during the read. READPAST only applies

to transactions operating at the default READ COMMITTED isolation level, and will

only read past row-level locks. READPAST can only be used in SELECT statements.

Normal blocking can be worked around by having transactions read past rows being

locked by other transactions.
See the following article: http://www.sql-server-performance.com/rd_table_hints.asp

Else I cannot see any other solution except adding some external data structure to keep track of processing status.|||

Hi carlop, I'm afraid locks won't resolve this issue.

Maybe I should explain my problem more detailly.

Suppose Process No.1 have 3 threads working , and here are the identities generated after the insertion.

thread 0 : 1 , 4, 7

thread 1: 2 , 5, 8

thread 2: 3, 6, 9

And at one time point, thread 0 commited the transaction with 3 rows inserted , whilst Process No.2 is retrieving data from Table A , so only these 3 records were retrieved and processed. So Process No.2 will record "7" as the biggist identity it has processed, and next time it will start processing from identities greater than "7" . So records with identities 2, 5,3,6 are lost if thread 1 and thread 2 commited later.

|||You have to add an external struct that keeps track of the processing jobs. I see no other way.|||You could add another table that is logged to on insert to table A. Process 2 reads these records and deletes them from the new tableonce processed. When it goes back for a second time it reads the nest set to process and then deletes from. You could use Service Broker if you wanted as it has a nice queue mechanism|||

Maybe this is the only solution I think.

Thank you all.

Friday, February 24, 2012

Identity Column - transactional replication with immediate update

Hi,
We have a problem that we can't find a solution to ... yet ...
I hope someone can help us out.
I will try to discribe the problem below.
1. We publish a publication with articles to the subscriber(s) in immediate
replication mode.
2. The we make an insert at the subscriber side.
3. The inserted record pops up at the publisher (and other subscribers)
almost immediate as it
should.
4. But the msrepl_tran_version column differs between the publisher
and the subscriber!!
Therefore it is not allowed to update the newly inserted record at the
subscriber site. We get the error message
"Rows do not match between Publisher and Subscriber. Run the Distribution
Agent to refresh rows at the Subscriber." The Agent IS running.
5. The msrepl_tran_version remains different UNTIL you make an update at the
publisher site (or at another subscriber that have the same value in
msrepl_tran_version as the publisher)
6. After an update at the publisher it is now perfectly allowed to make
updates to the
record at the same subscriber site that made the insert.
This applies only to records that are inserted from the specific subscriber,
all other records works just fine...
An insert into a table without Identity collumn works fine ...
The Identity property is set to 'Yes'.
I hope that someone have a clue of what we are missing here ...
Best Regards
Thomas
I think this is by design. This condition should clear when the distribution
agent runs. To get around it set the polling interval to something small (ie
1 s) and run your distribution agent continuously.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Thomas" <Thomas.Eriksson@.intress.se> wrote in message
news:evvkICznEHA.1608@.TK2MSFTNGP15.phx.gbl...
> Hi,
> We have a problem that we can't find a solution to ... yet ...
> I hope someone can help us out.
> I will try to discribe the problem below.
> 1. We publish a publication with articles to the subscriber(s) in
immediate
> replication mode.
> 2. The we make an insert at the subscriber side.
> 3. The inserted record pops up at the publisher (and other subscribers)
> almost immediate as it
> should.
> 4. But the msrepl_tran_version column differs between the publisher
> and the subscriber!!
> Therefore it is not allowed to update the newly inserted record at the
> subscriber site. We get the error message
> "Rows do not match between Publisher and Subscriber. Run the Distribution
> Agent to refresh rows at the Subscriber." The Agent IS running.
> 5. The msrepl_tran_version remains different UNTIL you make an update at
the
> publisher site (or at another subscriber that have the same value in
> msrepl_tran_version as the publisher)
> 6. After an update at the publisher it is now perfectly allowed to make
> updates to the
> record at the same subscriber site that made the insert.
> This applies only to records that are inserted from the specific
subscriber,
> all other records works just fine...
> An insert into a table without Identity collumn works fine ...
> The Identity property is set to 'Yes'.
> I hope that someone have a clue of what we are missing here ...
> Best Regards
> Thomas
>
|||Thomas,
the only way I can reproduce this behaviour is if there is an insert trigger
on the publisher's table which updates the same row.
In this case the guids won't match.
Can you check if this is the case?
Rgds,
Paul Ibison