can't delete a row?

I have transaction replication setup between publisher server and subscriber
server.
I tried to delete a row from publisher's table,
but it looks like it's running forever.
delete from sym_type where type_id=1
And I don't know what to troubleshoot. but if I do an insert, then it's
working. i also see the row being replicated to subscriber when I do an
insert.
insert into sym_type values (-3, 'test', null)
scraching my head hard. replication monitor show no errors.
kevin
Kevin,
sounds like a contention issue - you could use sp_who2 and look for blocking
(use dbcc inputbuffer to see the cause of the block).
If not that, then try running select * from sym_type (nolock) where
type_id=1 to see how many rows there are - it might be a big delete?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||no... there is only 10 rows in the table.
and I inserted a row type_id = 1 to test replication, which it works
fine.
So I tried to delete the same row to rollback the change, but then it failed
to delete the row.
I think it maybe has to do with replication.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OgpkHnn1FHA.3568@.TK2MSFTNGP15.phx.gbl...
> Kevin,
> sounds like a contention issue - you could use sp_who2 and look for
> blocking (use dbcc inputbuffer to see the cause of the block).
> If not that, then try running select * from sym_type (nolock) where
> type_id=1 to see how many rows there are - it might be a big delete?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Kevin - what about the blocking results from sp_who2 - see any?
Paul Ibison
|||yes, sp_who2 shows BlockBy - spid 60.
Command delete.
interesting huh? why can't I delete it?
I even did dbcc checktable, nothing wrong.
so i go to spid 60
it's command "delete from sym_type where type_id =1"
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OGrJB6n1FHA.2212@.TK2MSFTNGP15.phx.gbl...
> Kevin - what about the blocking results from sp_who2 - see any?
> Paul Ibison
>
|||Kevin,
strange - what's the spid/command command that is blocked by spid 60 - are
you saying it is another spid doing exactly the same command?
Paul
|||no.. there is [BlockBy] column in sp_who2 recordset,
there is value 60
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OuuDpro1FHA.2072@.TK2MSFTNGP14.phx.gbl...
> Kevin,
> strange - what's the spid/command command that is blocked by spid 60 - are
> you saying it is another spid doing exactly the same command?
> Paul
>
|||I'm still a little confused here You have 2 processes, and one blocks the
other. If I understand properly the one which is causing the block is the
delete, and the other one is blocked by the delete. If so, I agree that this
is strange and is the opposite of what I'd have expected. I suppose you
don't have some cunning triggers at work, or cascade deletes in action? -
worth checking out. DBCC OPENTRAN might also reveal something more but I
doubt it (still, worth a try). In your case I'd kill the delete spid, then
confirm that there's no blocking at all, and no open transactions. After
that, do a corresponding select, or perhaps just run the delete again if
it's ok.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||oh... I think I do have cascade delete in action.
my primary table is sym_type, foreign key table is portfolio_position ,
symbol_id is the key on both tables.
- it's enforce relationship for replication.
- it's enforce relationship for INSERTS and DELETES
it's cascading update related fields.
it's cascading delete related fields.
That must be it. when I do INSERT statement, there is no problem.
but when I do update or delete statement, query runs forever.
The next question is, why is this happen? I'm inserting a new symbol_id
which doesn't exist in foreign table,
why would this become a problem?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eLNLDCN2FHA.2444@.TK2MSFTNGP10.phx.gbl...
> I'm still a little confused here You have 2 processes, and one blocks
> the other. If I understand properly the one which is causing the block is
> the delete, and the other one is blocked by the delete. If so, I agree
> that this is strange and is the opposite of what I'd have expected. I
> suppose you don't have some cunning triggers at work, or cascade deletes
> in action? - worth checking out. DBCC OPENTRAN might also reveal something
> more but I doubt it (still, worth a try). In your case I'd kill the delete
> spid, then confirm that there's no blocking at all, and no open
> transactions. After that, do a corresponding select, or perhaps just run
> the delete again if it's ok.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Kevin,
is there a self join, or a circular-relationship between the tables perhaps?
Do you see the same behaviour in the absence of replication (if this is
possible to test). Actually you could DTS the tables to a test server and
test there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)