I have in a table with some 2000 records with a Document ID which is a Unique Seq Number and details with a Status Flag. Multiple Users access the Table. When a user accesses a particular Document ID the Status flag changes from "N" to "W". Once he finishes working with data on that particular Document ID the Status Changes to "C". When another user tries to pickup a Record the next record in the Seq with the Status as "N" will get fetched. I use a Stored Procedure to assign a Document id to a user and update the Record/status details in the Table. I have used the No lock clause in the SP while retrieving a particular Document ID. It was working fine till sometime. Now it has started giving the following error
Error: Run-time error '-2147467259(80004005)'
[Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction(process ID#127)was deadlocked with another process and as been chosen as the deadlock victim. Return your transaction.
Some one please Help me on how to go abt the problem. Thanks in advance.
Regards
Dinesh1. Run sp_recompile 'object_name' for all objects used.
2. Post it all. SP+DDL
Good luck !
Showing posts with label records. Show all posts
Showing posts with label records. Show all posts
Tuesday, March 27, 2012
Wednesday, March 21, 2012
Deadlock issue SQLServer2000
Hi,
I'm facing a deadlock issue in a stored procedure which only deletes
records from multiple tables.When i run this stotred proc. multiple
times I get deadlock between two SPIDs running the same stored
procedure's code.On drilling down in SQL trace using flag 1205 and the
SQL server trace I found that these are conversion deadlocks.The
records are deleted using PK in follwing sequence of
tables:-TbCIDAdmin ->TbCIDProduct ->TbUserService -> TbUserRole ->
TbUserAction -> TbCIDUsage ->TbUser ->TbPhoneNumber -> TbAddress ->
TbSFUsage -> TbCID -> TbCustomer.
As is evident from names of tables - TbUserService
,TbUserRole,TbUserAction,TbCIDAdmin depends(FK) on TbUser
tables - TbCIDAdmin,TbCIDProduct,TbCIDUsage,TbSFUsage,TbUser
depends(FK) on TbCID
tables -
TbUser depends(FK) on TbPhoneNumber,TbAddress,TbCID
tables -
TbCID depends(FK) on TbCustomer.
This is SQL trace I get...
Deadlock encountered ... Printing deadlock information
2004-10-04 19:06:25.27 spid4
2004-10-04 19:06:25.27 spid4 Wait-for graph
2004-10-04 19:06:25.27 spid4
2004-10-04 19:06:25.27 spid4 Node:1
2004-10-04 19:06:25.27 spid4 KEY: 17:277576027:1 (170315753ddb)
CleanCnt:2 Mode: X Flags: 0x0
2004-10-04 19:06:25.27 spid4 Wait List:
2004-10-04 19:06:25.27 spid4 Owner:0x1940e180 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:85 ECID:0
2004-10-04 19:06:25.27 spid4 SPID: 85 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.31 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageExpertInitializeRollback;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:78 ECID:0 Ec:(0x1B58F568) Value:0x193ff980 Cost:(0/614)
2004-10-04 19:06:25.32 spid4
2004-10-04 19:06:25.32 spid4 Node:2
2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (170315753ddb)
CleanCnt:2 Mode: X Flags: 0x0
2004-10-04 19:06:25.32 spid4 Grant List 0::
2004-10-04 19:06:25.32 spid4 Owner:0x1940dc80 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
2004-10-04 19:06:25.32 spid4 SPID: 83 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageExpertInitializeRollback;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:85 ECID:0 Ec:(0x1D915568) Value:0x1940e180 Cost:(0/518)
2004-10-04 19:06:25.32 spid4
2004-10-04 19:06:25.32 spid4 Node:3
2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (1d034e61a3fa)
CleanCnt:1 Mode: X Flags: 0x0
2004-10-04 19:06:25.32 spid4 Grant List 0::
2004-10-04 19:06:25.32 spid4 Owner:0x1940e340 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:78 ECID:0
2004-10-04 19:06:25.32 spid4 SPID: 78 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageProfiInitializeRollback;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
2004-10-04 19:06:25.32 spid4 Victim Resource Owner:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
2004-10-04 19:06:30.34 spid4
the resource 277576027 above is table TbCIDAdmin!
Until and unless i change the sequence of deletes I get the deadlock
in same table.
Can anyone figure out why is this happening?
I feel the problem lies in FK constraint checking while deleting rows.
Because SQL server must be reading(Shared Lock) the child tables
before deleting row from a parent table. Also, when I disabled all
foreign key constraint checking I stopped getting the deadlock errors!
If this is the reason can anyone pl. tell me how can I fix this?
Can we anyhow delay constraint cheking till I COMMIT TRANSACTION in
this stored procedure and at the same time other stored
procedures/transaction can work with the constraint checking as
ususal.
There used to be something like DISABLE_DEF_CNST_CHK in SQL server 6.5
. Can we somehow replicate this functionlaity in SQL server 2000?
Pl. help because this problem has become a real pain ....
Thanks in advance
BipulConstranits are good so do not turn them off so eagerly.
You might want to try select .. with (updlock) to avoid conversion related
deadlocks.
Here is an example of a deadlock free sequence.
begin tran
select ... from dept-table with (updlock) where dept_id = 99
delete employee-table where dept_id = 99
delete dept-table where dept_id = 99
commit
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bipul" <itsbipul@.gmail.com> wrote in message
news:f280e5a9.0410112211.2245022e@.posting.google.com...
> Hi,
> I'm facing a deadlock issue in a stored procedure which only deletes
> records from multiple tables.When i run this stotred proc. multiple
> times I get deadlock between two SPIDs running the same stored
> procedure's code.On drilling down in SQL trace using flag 1205 and the
> SQL server trace I found that these are conversion deadlocks.The
> records are deleted using PK in follwing sequence of
> tables:-TbCIDAdmin ->TbCIDProduct ->TbUserService -> TbUserRole ->
> TbUserAction -> TbCIDUsage ->TbUser ->TbPhoneNumber -> TbAddress ->
> TbSFUsage -> TbCID -> TbCustomer.
> As is evident from names of tables - TbUserService
> ,TbUserRole,TbUserAction,TbCIDAdmin depends(FK) on TbUser
> tables - TbCIDAdmin,TbCIDProduct,TbCIDUsage,TbSFUsage,TbUser
> depends(FK) on TbCID
> tables -
> TbUser depends(FK) on TbPhoneNumber,TbAddress,TbCID
> tables -
> TbCID depends(FK) on TbCustomer.
> This is SQL trace I get...
> Deadlock encountered ... Printing deadlock information
> 2004-10-04 19:06:25.27 spid4
> 2004-10-04 19:06:25.27 spid4 Wait-for graph
> 2004-10-04 19:06:25.27 spid4
> 2004-10-04 19:06:25.27 spid4 Node:1
> 2004-10-04 19:06:25.27 spid4 KEY: 17:277576027:1 (170315753ddb)
> CleanCnt:2 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.27 spid4 Wait List:
> 2004-10-04 19:06:25.27 spid4 Owner:0x1940e180 Mode: S
> Flg:0x0 Ref:1 Life:00000000 SPID:85 ECID:0
> 2004-10-04 19:06:25.27 spid4 SPID: 85 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.31 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageExpertInitializeRollback;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:78 ECID:0 Ec:(0x1B58F568) Value:0x193ff980 Cost:(0/614)
> 2004-10-04 19:06:25.32 spid4
> 2004-10-04 19:06:25.32 spid4 Node:2
> 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (170315753ddb)
> CleanCnt:2 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.32 spid4 Grant List 0::
> 2004-10-04 19:06:25.32 spid4 Owner:0x1940dc80 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
> 2004-10-04 19:06:25.32 spid4 SPID: 83 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageExpertInitializeRollback;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:85 ECID:0 Ec:(0x1D915568) Value:0x1940e180 Cost:(0/518)
> 2004-10-04 19:06:25.32 spid4
> 2004-10-04 19:06:25.32 spid4 Node:3
> 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (1d034e61a3fa)
> CleanCnt:1 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.32 spid4 Grant List 0::
> 2004-10-04 19:06:25.32 spid4 Owner:0x1940e340 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:78 ECID:0
> 2004-10-04 19:06:25.32 spid4 SPID: 78 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageProfiInitializeRollback;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
> 2004-10-04 19:06:25.32 spid4 Victim Resource Owner:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
> 2004-10-04 19:06:30.34 spid4
> the resource 277576027 above is table TbCIDAdmin!
> Until and unless i change the sequence of deletes I get the deadlock
> in same table.
> Can anyone figure out why is this happening?
> I feel the problem lies in FK constraint checking while deleting rows.
> Because SQL server must be reading(Shared Lock) the child tables
> before deleting row from a parent table. Also, when I disabled all
> foreign key constraint checking I stopped getting the deadlock errors!
> If this is the reason can anyone pl. tell me how can I fix this?
> Can we anyhow delay constraint cheking till I COMMIT TRANSACTION in
> this stored procedure and at the same time other stored
> procedures/transaction can work with the constraint checking as
> ususal.
> There used to be something like DISABLE_DEF_CNST_CHK in SQL server 6.5
> . Can we somehow replicate this functionlaity in SQL server 2000?
> Pl. help because this problem has become a real pain ....
> Thanks in advance
> Bipul|||Hi xiao,
I dont have any SELECT statements inside the transaction in my stored
procedure.
I have only DELETE statements with WHERE clause on the PK(whihc is
clustered index).
I'm basically not able to understand why deadlock chain is happening?
If you see the SQL Log I have provided in the first mail, all the
SPIDs are locking on the same Table and on the same IndId(index)and
each are having X lock and requesting for S lock. How is this possible
that each have been granted a X lock on same resource (since hash
values are different I guess they corresond to different records in
same table)? Should not they Block instead of deadlock? Will TABLOCK
help? But,then I would have to have TABLOCK on all tables from which
I'm deleteing in the transaction, which won't be a good idea?
Awaiting your comments?
Regds,
Bipul
yahoo/MSN id : itsbipul
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message news:<umxXUROsEHA.3564@.tk2msftngp13.phx.gbl>...
> Constranits are good so do not turn them off so eagerly.
> You might want to try select .. with (updlock) to avoid conversion related
> deadlocks.
> Here is an example of a deadlock free sequence.
> begin tran
> select ... from dept-table with (updlock) where dept_id = 99
> delete employee-table where dept_id = 99
> delete dept-table where dept_id = 99
> commit
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Bipul" <itsbipul@.gmail.com> wrote in message
> news:f280e5a9.0410112211.2245022e@.posting.google.com...
> > Hi,
> > I'm facing a deadlock issue in a stored procedure which only deletes
> > records from multiple tables.When i run this stotred proc. multiple
> > times I get deadlock between two SPIDs running the same stored
> > procedure's code.On drilling down in SQL trace using flag 1205 and the
> > SQL server trace I found that these are conversion deadlocks.The
> > records are deleted using PK in follwing sequence of
> > tables:-TbCIDAdmin ->TbCIDProduct ->TbUserService -> TbUserRole ->
> > TbUserAction -> TbCIDUsage ->TbUser ->TbPhoneNumber -> TbAddress ->
> > TbSFUsage -> TbCID -> TbCustomer.
> >
> > As is evident from names of tables - TbUserService
> > ,TbUserRole,TbUserAction,TbCIDAdmin depends(FK) on TbUser
> > tables - TbCIDAdmin,TbCIDProduct,TbCIDUsage,TbSFUsage,TbUser
> > depends(FK) on TbCID
> > tables -
> > TbUser depends(FK) on TbPhoneNumber,TbAddress,TbCID
> > tables -
> > TbCID depends(FK) on TbCustomer.
> >
> > This is SQL trace I get...
> > Deadlock encountered ... Printing deadlock information
> > 2004-10-04 19:06:25.27 spid4
> > 2004-10-04 19:06:25.27 spid4 Wait-for graph
> > 2004-10-04 19:06:25.27 spid4
> > 2004-10-04 19:06:25.27 spid4 Node:1
> > 2004-10-04 19:06:25.27 spid4 KEY: 17:277576027:1 (170315753ddb)
> > CleanCnt:2 Mode: X Flags: 0x0
> > 2004-10-04 19:06:25.27 spid4 Wait List:
> > 2004-10-04 19:06:25.27 spid4 Owner:0x1940e180 Mode: S
> > Flg:0x0 Ref:1 Life:00000000 SPID:85 ECID:0
> > 2004-10-04 19:06:25.27 spid4 SPID: 85 ECID: 0 Statement Type:
> > DELETE Line #: 241
> > 2004-10-04 19:06:25.31 spid4 Input Buf: RPC Event:
> > SpCreateCIDWebPageExpertInitializeRollback;1
> > 2004-10-04 19:06:25.32 spid4 Requested By:
> > 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> > S SPID:78 ECID:0 Ec:(0x1B58F568) Value:0x193ff980 Cost:(0/614)
> > 2004-10-04 19:06:25.32 spid4
> > 2004-10-04 19:06:25.32 spid4 Node:2
> > 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (170315753ddb)
> > CleanCnt:2 Mode: X Flags: 0x0
> > 2004-10-04 19:06:25.32 spid4 Grant List 0::
> > 2004-10-04 19:06:25.32 spid4 Owner:0x1940dc80 Mode: X
> > Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
> > 2004-10-04 19:06:25.32 spid4 SPID: 83 ECID: 0 Statement Type:
> > DELETE Line #: 241
> > 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> > SpCreateCIDWebPageExpertInitializeRollback;1
> > 2004-10-04 19:06:25.32 spid4 Requested By:
> > 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> > S SPID:85 ECID:0 Ec:(0x1D915568) Value:0x1940e180 Cost:(0/518)
> > 2004-10-04 19:06:25.32 spid4
> > 2004-10-04 19:06:25.32 spid4 Node:3
> > 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (1d034e61a3fa)
> > CleanCnt:1 Mode: X Flags: 0x0
> > 2004-10-04 19:06:25.32 spid4 Grant List 0::
> > 2004-10-04 19:06:25.32 spid4 Owner:0x1940e340 Mode: X
> > Flg:0x0 Ref:0 Life:02000000 SPID:78 ECID:0
> > 2004-10-04 19:06:25.32 spid4 SPID: 78 ECID: 0 Statement Type:
> > DELETE Line #: 241
> > 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> > SpCreateCIDWebPageProfiInitializeRollback;1
> > 2004-10-04 19:06:25.32 spid4 Requested By:
> > 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> > S SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
> > 2004-10-04 19:06:25.32 spid4 Victim Resource Owner:
> > 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
> > 2004-10-04 19:06:30.34 spid4
> >
> > the resource 277576027 above is table TbCIDAdmin!
> > Until and unless i change the sequence of deletes I get the deadlock
> > in same table.
> > Can anyone figure out why is this happening?
> > I feel the problem lies in FK constraint checking while deleting rows.
> > Because SQL server must be reading(Shared Lock) the child tables
> > before deleting row from a parent table. Also, when I disabled all
> > foreign key constraint checking I stopped getting the deadlock errors!
> > If this is the reason can anyone pl. tell me how can I fix this?
> > Can we anyhow delay constraint cheking till I COMMIT TRANSACTION in
> > this stored procedure and at the same time other stored
> > procedures/transaction can work with the constraint checking as
> > ususal.
> > There used to be something like DISABLE_DEF_CNST_CHK in SQL server 6.5
> > . Can we somehow replicate this functionlaity in SQL server 2000?
> > Pl. help because this problem has become a real pain ....
> >
> > Thanks in advance
> > Bipul|||On 13 Oct 2004 23:07:38 -0700, itsbipul@.gmail.com (Bipul) wrote:
>Awaiting your comments?
Are you doing joins in your delete statements?
Can you show your code?
It is curious, since you own the locks on early deletes, but I'd like
to try to figure out what SQLServer *thinks* it's doing!
Can you break the transaction into several independent pieces - quick
workaround, probably.
J.|||Hi,
This indeed is an intriguing problem. I don't have any joins in the
Delete queries. These are just pure delete statements with 'where'
clause on PK of the table from which I delete.
But,yes as you can see from my first mail there is child parent
relationship between the tables I'm deleting.
I can not break the transaction into smaller pieces. :(
What do you think will be the problem?
What I think is that when SQL server is deleting from the parent table
it searches the child tables for checking FK violations. For this it
takes locks on the indexes of these child tables and then 2 pids doing
the same get deadlocked on index resource.
Am I thinking on right lines ? or there is something else?
Awaiting responses...
Regds,
Bipul
JXStern <JXSternChangeX2R@.gte.net> wrote in message news:<cd9in0h4qtg9g9n63buuhoh85qte4asjvh@.4ax.com>...
> On 13 Oct 2004 23:07:38 -0700, itsbipul@.gmail.com (Bipul) wrote:
> >Awaiting your comments?
> Are you doing joins in your delete statements?
> Can you show your code?
> It is curious, since you own the locks on early deletes, but I'd like
> to try to figure out what SQLServer *thinks* it's doing!
> Can you break the transaction into several independent pieces - quick
> workaround, probably.
> J.|||Hi Bipul,
Is this problem resolved ? I was going thru the newsgroup to learn more
about deadlocks. Reading the thread and the response from Wei Xiao, I have a
feeling that he had the select statement with updlock in his transaction to
prevent sql server from allowing any sharing of those rows. so make your
delete work, you can try this:
begin tran
get upd lock on childTab
delete from childTab
delete from parentTab
commit tran
One thing that is confusing is that Wei had a select on the parentTab with
updlock, whereas your deadlock was due to contention on a child table. My
above suggestion is based on your assumption that sql server is not able to
establish the shared lock on the child table while checking the constraint.
Regards,
Mani.
"Bipul" wrote:
> Hi,
> This indeed is an intriguing problem. I don't have any joins in the
> Delete queries. These are just pure delete statements with 'where'
> clause on PK of the table from which I delete.
> But,yes as you can see from my first mail there is child parent
> relationship between the tables I'm deleting.
> I can not break the transaction into smaller pieces. :(
> What do you think will be the problem?
> What I think is that when SQL server is deleting from the parent table
> it searches the child tables for checking FK violations. For this it
> takes locks on the indexes of these child tables and then 2 pids doing
> the same get deadlocked on index resource.
> Am I thinking on right lines ? or there is something else?
> Awaiting responses...
> Regds,
> Bipul
>
>
> JXStern <JXSternChangeX2R@.gte.net> wrote in message news:<cd9in0h4qtg9g9n63buuhoh85qte4asjvh@.4ax.com>...
> > On 13 Oct 2004 23:07:38 -0700, itsbipul@.gmail.com (Bipul) wrote:
> > >Awaiting your comments?
> >
> > Are you doing joins in your delete statements?
> >
> > Can you show your code?
> >
> > It is curious, since you own the locks on early deletes, but I'd like
> > to try to figure out what SQLServer *thinks* it's doing!
> >
> > Can you break the transaction into several independent pieces - quick
> > workaround, probably.
> >
> > J.
>
I'm facing a deadlock issue in a stored procedure which only deletes
records from multiple tables.When i run this stotred proc. multiple
times I get deadlock between two SPIDs running the same stored
procedure's code.On drilling down in SQL trace using flag 1205 and the
SQL server trace I found that these are conversion deadlocks.The
records are deleted using PK in follwing sequence of
tables:-TbCIDAdmin ->TbCIDProduct ->TbUserService -> TbUserRole ->
TbUserAction -> TbCIDUsage ->TbUser ->TbPhoneNumber -> TbAddress ->
TbSFUsage -> TbCID -> TbCustomer.
As is evident from names of tables - TbUserService
,TbUserRole,TbUserAction,TbCIDAdmin depends(FK) on TbUser
tables - TbCIDAdmin,TbCIDProduct,TbCIDUsage,TbSFUsage,TbUser
depends(FK) on TbCID
tables -
TbUser depends(FK) on TbPhoneNumber,TbAddress,TbCID
tables -
TbCID depends(FK) on TbCustomer.
This is SQL trace I get...
Deadlock encountered ... Printing deadlock information
2004-10-04 19:06:25.27 spid4
2004-10-04 19:06:25.27 spid4 Wait-for graph
2004-10-04 19:06:25.27 spid4
2004-10-04 19:06:25.27 spid4 Node:1
2004-10-04 19:06:25.27 spid4 KEY: 17:277576027:1 (170315753ddb)
CleanCnt:2 Mode: X Flags: 0x0
2004-10-04 19:06:25.27 spid4 Wait List:
2004-10-04 19:06:25.27 spid4 Owner:0x1940e180 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:85 ECID:0
2004-10-04 19:06:25.27 spid4 SPID: 85 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.31 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageExpertInitializeRollback;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:78 ECID:0 Ec:(0x1B58F568) Value:0x193ff980 Cost:(0/614)
2004-10-04 19:06:25.32 spid4
2004-10-04 19:06:25.32 spid4 Node:2
2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (170315753ddb)
CleanCnt:2 Mode: X Flags: 0x0
2004-10-04 19:06:25.32 spid4 Grant List 0::
2004-10-04 19:06:25.32 spid4 Owner:0x1940dc80 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
2004-10-04 19:06:25.32 spid4 SPID: 83 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageExpertInitializeRollback;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:85 ECID:0 Ec:(0x1D915568) Value:0x1940e180 Cost:(0/518)
2004-10-04 19:06:25.32 spid4
2004-10-04 19:06:25.32 spid4 Node:3
2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (1d034e61a3fa)
CleanCnt:1 Mode: X Flags: 0x0
2004-10-04 19:06:25.32 spid4 Grant List 0::
2004-10-04 19:06:25.32 spid4 Owner:0x1940e340 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:78 ECID:0
2004-10-04 19:06:25.32 spid4 SPID: 78 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageProfiInitializeRollback;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
2004-10-04 19:06:25.32 spid4 Victim Resource Owner:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
2004-10-04 19:06:30.34 spid4
the resource 277576027 above is table TbCIDAdmin!
Until and unless i change the sequence of deletes I get the deadlock
in same table.
Can anyone figure out why is this happening?
I feel the problem lies in FK constraint checking while deleting rows.
Because SQL server must be reading(Shared Lock) the child tables
before deleting row from a parent table. Also, when I disabled all
foreign key constraint checking I stopped getting the deadlock errors!
If this is the reason can anyone pl. tell me how can I fix this?
Can we anyhow delay constraint cheking till I COMMIT TRANSACTION in
this stored procedure and at the same time other stored
procedures/transaction can work with the constraint checking as
ususal.
There used to be something like DISABLE_DEF_CNST_CHK in SQL server 6.5
. Can we somehow replicate this functionlaity in SQL server 2000?
Pl. help because this problem has become a real pain ....
Thanks in advance
BipulConstranits are good so do not turn them off so eagerly.
You might want to try select .. with (updlock) to avoid conversion related
deadlocks.
Here is an example of a deadlock free sequence.
begin tran
select ... from dept-table with (updlock) where dept_id = 99
delete employee-table where dept_id = 99
delete dept-table where dept_id = 99
commit
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bipul" <itsbipul@.gmail.com> wrote in message
news:f280e5a9.0410112211.2245022e@.posting.google.com...
> Hi,
> I'm facing a deadlock issue in a stored procedure which only deletes
> records from multiple tables.When i run this stotred proc. multiple
> times I get deadlock between two SPIDs running the same stored
> procedure's code.On drilling down in SQL trace using flag 1205 and the
> SQL server trace I found that these are conversion deadlocks.The
> records are deleted using PK in follwing sequence of
> tables:-TbCIDAdmin ->TbCIDProduct ->TbUserService -> TbUserRole ->
> TbUserAction -> TbCIDUsage ->TbUser ->TbPhoneNumber -> TbAddress ->
> TbSFUsage -> TbCID -> TbCustomer.
> As is evident from names of tables - TbUserService
> ,TbUserRole,TbUserAction,TbCIDAdmin depends(FK) on TbUser
> tables - TbCIDAdmin,TbCIDProduct,TbCIDUsage,TbSFUsage,TbUser
> depends(FK) on TbCID
> tables -
> TbUser depends(FK) on TbPhoneNumber,TbAddress,TbCID
> tables -
> TbCID depends(FK) on TbCustomer.
> This is SQL trace I get...
> Deadlock encountered ... Printing deadlock information
> 2004-10-04 19:06:25.27 spid4
> 2004-10-04 19:06:25.27 spid4 Wait-for graph
> 2004-10-04 19:06:25.27 spid4
> 2004-10-04 19:06:25.27 spid4 Node:1
> 2004-10-04 19:06:25.27 spid4 KEY: 17:277576027:1 (170315753ddb)
> CleanCnt:2 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.27 spid4 Wait List:
> 2004-10-04 19:06:25.27 spid4 Owner:0x1940e180 Mode: S
> Flg:0x0 Ref:1 Life:00000000 SPID:85 ECID:0
> 2004-10-04 19:06:25.27 spid4 SPID: 85 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.31 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageExpertInitializeRollback;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:78 ECID:0 Ec:(0x1B58F568) Value:0x193ff980 Cost:(0/614)
> 2004-10-04 19:06:25.32 spid4
> 2004-10-04 19:06:25.32 spid4 Node:2
> 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (170315753ddb)
> CleanCnt:2 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.32 spid4 Grant List 0::
> 2004-10-04 19:06:25.32 spid4 Owner:0x1940dc80 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
> 2004-10-04 19:06:25.32 spid4 SPID: 83 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageExpertInitializeRollback;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:85 ECID:0 Ec:(0x1D915568) Value:0x1940e180 Cost:(0/518)
> 2004-10-04 19:06:25.32 spid4
> 2004-10-04 19:06:25.32 spid4 Node:3
> 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (1d034e61a3fa)
> CleanCnt:1 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.32 spid4 Grant List 0::
> 2004-10-04 19:06:25.32 spid4 Owner:0x1940e340 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:78 ECID:0
> 2004-10-04 19:06:25.32 spid4 SPID: 78 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageProfiInitializeRollback;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
> 2004-10-04 19:06:25.32 spid4 Victim Resource Owner:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
> 2004-10-04 19:06:30.34 spid4
> the resource 277576027 above is table TbCIDAdmin!
> Until and unless i change the sequence of deletes I get the deadlock
> in same table.
> Can anyone figure out why is this happening?
> I feel the problem lies in FK constraint checking while deleting rows.
> Because SQL server must be reading(Shared Lock) the child tables
> before deleting row from a parent table. Also, when I disabled all
> foreign key constraint checking I stopped getting the deadlock errors!
> If this is the reason can anyone pl. tell me how can I fix this?
> Can we anyhow delay constraint cheking till I COMMIT TRANSACTION in
> this stored procedure and at the same time other stored
> procedures/transaction can work with the constraint checking as
> ususal.
> There used to be something like DISABLE_DEF_CNST_CHK in SQL server 6.5
> . Can we somehow replicate this functionlaity in SQL server 2000?
> Pl. help because this problem has become a real pain ....
> Thanks in advance
> Bipul|||Hi xiao,
I dont have any SELECT statements inside the transaction in my stored
procedure.
I have only DELETE statements with WHERE clause on the PK(whihc is
clustered index).
I'm basically not able to understand why deadlock chain is happening?
If you see the SQL Log I have provided in the first mail, all the
SPIDs are locking on the same Table and on the same IndId(index)and
each are having X lock and requesting for S lock. How is this possible
that each have been granted a X lock on same resource (since hash
values are different I guess they corresond to different records in
same table)? Should not they Block instead of deadlock? Will TABLOCK
help? But,then I would have to have TABLOCK on all tables from which
I'm deleteing in the transaction, which won't be a good idea?
Awaiting your comments?
Regds,
Bipul
yahoo/MSN id : itsbipul
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message news:<umxXUROsEHA.3564@.tk2msftngp13.phx.gbl>...
> Constranits are good so do not turn them off so eagerly.
> You might want to try select .. with (updlock) to avoid conversion related
> deadlocks.
> Here is an example of a deadlock free sequence.
> begin tran
> select ... from dept-table with (updlock) where dept_id = 99
> delete employee-table where dept_id = 99
> delete dept-table where dept_id = 99
> commit
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Bipul" <itsbipul@.gmail.com> wrote in message
> news:f280e5a9.0410112211.2245022e@.posting.google.com...
> > Hi,
> > I'm facing a deadlock issue in a stored procedure which only deletes
> > records from multiple tables.When i run this stotred proc. multiple
> > times I get deadlock between two SPIDs running the same stored
> > procedure's code.On drilling down in SQL trace using flag 1205 and the
> > SQL server trace I found that these are conversion deadlocks.The
> > records are deleted using PK in follwing sequence of
> > tables:-TbCIDAdmin ->TbCIDProduct ->TbUserService -> TbUserRole ->
> > TbUserAction -> TbCIDUsage ->TbUser ->TbPhoneNumber -> TbAddress ->
> > TbSFUsage -> TbCID -> TbCustomer.
> >
> > As is evident from names of tables - TbUserService
> > ,TbUserRole,TbUserAction,TbCIDAdmin depends(FK) on TbUser
> > tables - TbCIDAdmin,TbCIDProduct,TbCIDUsage,TbSFUsage,TbUser
> > depends(FK) on TbCID
> > tables -
> > TbUser depends(FK) on TbPhoneNumber,TbAddress,TbCID
> > tables -
> > TbCID depends(FK) on TbCustomer.
> >
> > This is SQL trace I get...
> > Deadlock encountered ... Printing deadlock information
> > 2004-10-04 19:06:25.27 spid4
> > 2004-10-04 19:06:25.27 spid4 Wait-for graph
> > 2004-10-04 19:06:25.27 spid4
> > 2004-10-04 19:06:25.27 spid4 Node:1
> > 2004-10-04 19:06:25.27 spid4 KEY: 17:277576027:1 (170315753ddb)
> > CleanCnt:2 Mode: X Flags: 0x0
> > 2004-10-04 19:06:25.27 spid4 Wait List:
> > 2004-10-04 19:06:25.27 spid4 Owner:0x1940e180 Mode: S
> > Flg:0x0 Ref:1 Life:00000000 SPID:85 ECID:0
> > 2004-10-04 19:06:25.27 spid4 SPID: 85 ECID: 0 Statement Type:
> > DELETE Line #: 241
> > 2004-10-04 19:06:25.31 spid4 Input Buf: RPC Event:
> > SpCreateCIDWebPageExpertInitializeRollback;1
> > 2004-10-04 19:06:25.32 spid4 Requested By:
> > 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> > S SPID:78 ECID:0 Ec:(0x1B58F568) Value:0x193ff980 Cost:(0/614)
> > 2004-10-04 19:06:25.32 spid4
> > 2004-10-04 19:06:25.32 spid4 Node:2
> > 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (170315753ddb)
> > CleanCnt:2 Mode: X Flags: 0x0
> > 2004-10-04 19:06:25.32 spid4 Grant List 0::
> > 2004-10-04 19:06:25.32 spid4 Owner:0x1940dc80 Mode: X
> > Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
> > 2004-10-04 19:06:25.32 spid4 SPID: 83 ECID: 0 Statement Type:
> > DELETE Line #: 241
> > 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> > SpCreateCIDWebPageExpertInitializeRollback;1
> > 2004-10-04 19:06:25.32 spid4 Requested By:
> > 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> > S SPID:85 ECID:0 Ec:(0x1D915568) Value:0x1940e180 Cost:(0/518)
> > 2004-10-04 19:06:25.32 spid4
> > 2004-10-04 19:06:25.32 spid4 Node:3
> > 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (1d034e61a3fa)
> > CleanCnt:1 Mode: X Flags: 0x0
> > 2004-10-04 19:06:25.32 spid4 Grant List 0::
> > 2004-10-04 19:06:25.32 spid4 Owner:0x1940e340 Mode: X
> > Flg:0x0 Ref:0 Life:02000000 SPID:78 ECID:0
> > 2004-10-04 19:06:25.32 spid4 SPID: 78 ECID: 0 Statement Type:
> > DELETE Line #: 241
> > 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> > SpCreateCIDWebPageProfiInitializeRollback;1
> > 2004-10-04 19:06:25.32 spid4 Requested By:
> > 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> > S SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
> > 2004-10-04 19:06:25.32 spid4 Victim Resource Owner:
> > 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
> > 2004-10-04 19:06:30.34 spid4
> >
> > the resource 277576027 above is table TbCIDAdmin!
> > Until and unless i change the sequence of deletes I get the deadlock
> > in same table.
> > Can anyone figure out why is this happening?
> > I feel the problem lies in FK constraint checking while deleting rows.
> > Because SQL server must be reading(Shared Lock) the child tables
> > before deleting row from a parent table. Also, when I disabled all
> > foreign key constraint checking I stopped getting the deadlock errors!
> > If this is the reason can anyone pl. tell me how can I fix this?
> > Can we anyhow delay constraint cheking till I COMMIT TRANSACTION in
> > this stored procedure and at the same time other stored
> > procedures/transaction can work with the constraint checking as
> > ususal.
> > There used to be something like DISABLE_DEF_CNST_CHK in SQL server 6.5
> > . Can we somehow replicate this functionlaity in SQL server 2000?
> > Pl. help because this problem has become a real pain ....
> >
> > Thanks in advance
> > Bipul|||On 13 Oct 2004 23:07:38 -0700, itsbipul@.gmail.com (Bipul) wrote:
>Awaiting your comments?
Are you doing joins in your delete statements?
Can you show your code?
It is curious, since you own the locks on early deletes, but I'd like
to try to figure out what SQLServer *thinks* it's doing!
Can you break the transaction into several independent pieces - quick
workaround, probably.
J.|||Hi,
This indeed is an intriguing problem. I don't have any joins in the
Delete queries. These are just pure delete statements with 'where'
clause on PK of the table from which I delete.
But,yes as you can see from my first mail there is child parent
relationship between the tables I'm deleting.
I can not break the transaction into smaller pieces. :(
What do you think will be the problem?
What I think is that when SQL server is deleting from the parent table
it searches the child tables for checking FK violations. For this it
takes locks on the indexes of these child tables and then 2 pids doing
the same get deadlocked on index resource.
Am I thinking on right lines ? or there is something else?
Awaiting responses...
Regds,
Bipul
JXStern <JXSternChangeX2R@.gte.net> wrote in message news:<cd9in0h4qtg9g9n63buuhoh85qte4asjvh@.4ax.com>...
> On 13 Oct 2004 23:07:38 -0700, itsbipul@.gmail.com (Bipul) wrote:
> >Awaiting your comments?
> Are you doing joins in your delete statements?
> Can you show your code?
> It is curious, since you own the locks on early deletes, but I'd like
> to try to figure out what SQLServer *thinks* it's doing!
> Can you break the transaction into several independent pieces - quick
> workaround, probably.
> J.|||Hi Bipul,
Is this problem resolved ? I was going thru the newsgroup to learn more
about deadlocks. Reading the thread and the response from Wei Xiao, I have a
feeling that he had the select statement with updlock in his transaction to
prevent sql server from allowing any sharing of those rows. so make your
delete work, you can try this:
begin tran
get upd lock on childTab
delete from childTab
delete from parentTab
commit tran
One thing that is confusing is that Wei had a select on the parentTab with
updlock, whereas your deadlock was due to contention on a child table. My
above suggestion is based on your assumption that sql server is not able to
establish the shared lock on the child table while checking the constraint.
Regards,
Mani.
"Bipul" wrote:
> Hi,
> This indeed is an intriguing problem. I don't have any joins in the
> Delete queries. These are just pure delete statements with 'where'
> clause on PK of the table from which I delete.
> But,yes as you can see from my first mail there is child parent
> relationship between the tables I'm deleting.
> I can not break the transaction into smaller pieces. :(
> What do you think will be the problem?
> What I think is that when SQL server is deleting from the parent table
> it searches the child tables for checking FK violations. For this it
> takes locks on the indexes of these child tables and then 2 pids doing
> the same get deadlocked on index resource.
> Am I thinking on right lines ? or there is something else?
> Awaiting responses...
> Regds,
> Bipul
>
>
> JXStern <JXSternChangeX2R@.gte.net> wrote in message news:<cd9in0h4qtg9g9n63buuhoh85qte4asjvh@.4ax.com>...
> > On 13 Oct 2004 23:07:38 -0700, itsbipul@.gmail.com (Bipul) wrote:
> > >Awaiting your comments?
> >
> > Are you doing joins in your delete statements?
> >
> > Can you show your code?
> >
> > It is curious, since you own the locks on early deletes, but I'd like
> > to try to figure out what SQLServer *thinks* it's doing!
> >
> > Can you break the transaction into several independent pieces - quick
> > workaround, probably.
> >
> > J.
>
Thursday, March 8, 2012
deadlock
hello
im inserting some records from access file to sql server table with for loop
but oafter inseting some records i get this error
please help me
thank you
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 87)
was deadlocked on lock | communication buffer resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.Hi
Any activities during the inserting? How do you perform this batch?
"javad ebrahimnezhad" <sorena@.parskhazar.net> wrote in message
news:eCwO15KIGHA.516@.TK2MSFTNGP15.phx.gbl...
> hello
> im inserting some records from access file to sql server table with for
> loop
> but oafter inseting some records i get this error
> please help me
> thank you
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 87)
> was deadlocked on lock | communication buffer resources with another
> process and has been chosen as the deadlock victim. Rerun the transaction.
>|||javad ebrahimnezhad (sorena@.parskhazar.net) writes:
> im inserting some records from access file to sql server table with for
> loop but oafter inseting some records i get this error
> please help me
> thank you
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 87)
> was deadlocked on lock | communication buffer resources with another
> process and has been chosen as the deadlock victim. Rerun the
> transaction.
Apparently there is other activitity on the server that your insert process
collides with. You should contact your DBA. If he already have enabled
deadlock tracing, you might be enable to identify the issue by checking
the error log.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||It might work better bulk inserting the MS Access data using a DTS package.
"javad ebrahimnezhad" <sorena@.parskhazar.net> wrote in message
news:eCwO15KIGHA.516@.TK2MSFTNGP15.phx.gbl...
> hello
> im inserting some records from access file to sql server table with for
> loop
> but oafter inseting some records i get this error
> please help me
> thank you
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 87)
> was deadlocked on lock | communication buffer resources with another
> process and has been chosen as the deadlock victim. Rerun the transaction.
>
im inserting some records from access file to sql server table with for loop
but oafter inseting some records i get this error
please help me
thank you
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 87)
was deadlocked on lock | communication buffer resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.Hi
Any activities during the inserting? How do you perform this batch?
"javad ebrahimnezhad" <sorena@.parskhazar.net> wrote in message
news:eCwO15KIGHA.516@.TK2MSFTNGP15.phx.gbl...
> hello
> im inserting some records from access file to sql server table with for
> loop
> but oafter inseting some records i get this error
> please help me
> thank you
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 87)
> was deadlocked on lock | communication buffer resources with another
> process and has been chosen as the deadlock victim. Rerun the transaction.
>|||javad ebrahimnezhad (sorena@.parskhazar.net) writes:
> im inserting some records from access file to sql server table with for
> loop but oafter inseting some records i get this error
> please help me
> thank you
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 87)
> was deadlocked on lock | communication buffer resources with another
> process and has been chosen as the deadlock victim. Rerun the
> transaction.
Apparently there is other activitity on the server that your insert process
collides with. You should contact your DBA. If he already have enabled
deadlock tracing, you might be enable to identify the issue by checking
the error log.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||It might work better bulk inserting the MS Access data using a DTS package.
"javad ebrahimnezhad" <sorena@.parskhazar.net> wrote in message
news:eCwO15KIGHA.516@.TK2MSFTNGP15.phx.gbl...
> hello
> im inserting some records from access file to sql server table with for
> loop
> but oafter inseting some records i get this error
> please help me
> thank you
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 87)
> was deadlocked on lock | communication buffer resources with another
> process and has been chosen as the deadlock victim. Rerun the transaction.
>
deadfully slow cursor?
I've got the following piece of code in a stored procedure, and despite the tables only having about 25K records, its dreadfully slow.
DECLARE Perfer CURSOR FOR
SELECT PerformerID
FROM PAMRA_tbl_navnmatch (NOLOCK)
DECLARE @.test as int
OPEN Perfer
FETCH NEXT FROM Perfer
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Perfer into @.test
UPDATE PAMRA_tbl_navnmatch
SET PAMRA_tbl_navnmatch.Sgenavn = convert(char(50),NAMEMATCH_vw_memberdata.Sgenavn) ,
PAMRA_tbl_navnmatch.Medlemsnavn = convert(char(50),NAMEMATCH_vw_memberdata.Medlemsna vn),
PAMRA_tbl_navnmatch.Medlemsnavn2 = convert(char(50),NAMEMATCH_vw_memberdata.[Medlemsnavn 2]),
PAMRA_tbl_navnmatch.Medlemsnummer = convert(int, NAMEMATCH_vw_memberdata.Medlemsnummer),
PAMRA_tbl_navnmatch.Nationalitet = convert(char(10), NAMEMATCH_vw_memberdata.Nationalitet),
PAMRA_tbl_navnmatch.Organisationsnummer = convert(char(10),NAMEMATCH_vw_memberdata.Organisat ionsnummer),
PAMRA_tbl_navnmatch.Medlemskab = convert(char(20), NAMEMATCH_vw_memberdata.Medlemsskab),
PAMRA_tbl_navnmatch.IPDnummer = convert(int, NAMEMATCH_vw_memberdata.[IPD Nummer]),
PAMRA_tbl_navnmatch.IPDroll = convert(char(20), NAMEMATCH_vw_memberdata.IPDrolle),
PAMRA_tbl_navnmatch.Franavision = 1
FROM PAMRA_tbl_navnmatch INNER JOIN NAMEMATCH_vw_memberdata ON ltrim(rtrim(PAMRA_tbl_navnmatch.Matchfelt)) = ltrim(rtrim(NAMEMATCH_vw_memberdata.[Sgenavn]))
WHERE PAMRA_tbl_navnmatch.PerformerID = @.test
END
CLOSE Perfer
DEALLOCATE Perfer
GO
Is there any way to speed things up? I mean, its been running for more than 45 minutes now. I can track the progress, and it does move forward, BUT yawn its slow.
Its even run on a dual xeon 3.2 server with 4 gigs of memory, only other acticity is a few simple selects on other databases. No locks or anything.
Whats amiss? or is the comparison between char fields just dreadded?HUH?
First, your FETCH Statement doesn't have an into.
Second you don't need the cursor
Third you're already refereincing the table in the update that's in the cursor.
Forth, you're going to update all rows anyway...
Is someone playing a trick on you?|||index on NAMEMATCH_vw_memberdata?
why are you using a cursor for this ? it looks as if you should be able to use an insert...
-Kilka|||why are you using a cursor for this ? it looks as if you should be able to use an insert...
-Kilka
Huh?
This should do the same thing.
UPDATE n
SET Sgenavn = convert(char(50),NAMEMATCH_vw_memberdata.Sgenavn)
, Medlemsnavn = convert(char(50),NAMEMATCH_vw_memberdata.Medlemsna vn)
, Medlemsnavn2 = convert(char(50),NAMEMATCH_vw_memberdata.[Medlemsnavn 2])
, Medlemsnummer = convert(int, NAMEMATCH_vw_memberdata.Medlemsnummer)
, Nationalitet = convert(char(10), NAMEMATCH_vw_memberdata.Nationalitet)
, Organisationsnummer = convert(char(10),NAMEMATCH_vw_memberdata.Organisat ionsnummer)
, Medlemskab = convert(char(20), NAMEMATCH_vw_memberdata.Medlemsskab)
, IPDnummer = convert(int, NAMEMATCH_vw_memberdata.[IPD Nummer])
, IPDroll = convert(char(20), NAMEMATCH_vw_memberdata.IPDrolle)
, Franavision = 1
FROM PAMRA_tbl_navnmatch n
INNER JOIN NAMEMATCH_vw_memberdata m
ON ltrim(rtrim(n.Matchfelt)) = ltrim(rtrim(m.[Sgenavn]))|||yup :)
do you have better luck that way ?|||the only reason why I do it using a cursor is because the full update simply dies... it takes yonks time...
my first guess was "somethings terribly wrong"... which is true... but since I can't change that the server is slow, I figured doing it cursor-wise, record by record, the update would take time, but in the end complete anyway.
So, yes, something is playing with, the fact that the database is - apparently - mindnumbingly slow for God knows what reason..
/Trin
P.S. I let the clean update run for 3+ hours, then I just gave up... the cursor version takes little under an hour to do.. so although not exactly Einstein, it gets the job done. I was just hoping there was any other way to boost it.|||Apparently the problem is solved.
Somewhere in the scripting of creating tables, I hadn't included indexes... so, at new creation, no indexes were made..
After I added indexes I was able to the basic UPDATE without cursor fairly quickly...
sigh..|||Let's be very clear here.
A Set Update will always out-perform a cursor based solution.
If you are having performance problems, you should trouble shoot that...not through a cursor at it...
And I'm just curious...what's with all the TRIM and CONVERT usage?
Post the DDL of thos 2 tables please.|||Troubleshooting isn't always an option when you have a deadline... the cursor got the job done on time, and now I can troubleshoot while performing the same operations on a different set of data.
The reason for the trims is that much of the populated data is inserted into the tables by various dubious access forms and excel sheets. Spaces in front of and behind stuff... so I merely do it to ensure blanks are killed off until I get to the point of trimming at the front-end.
DDL?
DECLARE Perfer CURSOR FOR
SELECT PerformerID
FROM PAMRA_tbl_navnmatch (NOLOCK)
DECLARE @.test as int
OPEN Perfer
FETCH NEXT FROM Perfer
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Perfer into @.test
UPDATE PAMRA_tbl_navnmatch
SET PAMRA_tbl_navnmatch.Sgenavn = convert(char(50),NAMEMATCH_vw_memberdata.Sgenavn) ,
PAMRA_tbl_navnmatch.Medlemsnavn = convert(char(50),NAMEMATCH_vw_memberdata.Medlemsna vn),
PAMRA_tbl_navnmatch.Medlemsnavn2 = convert(char(50),NAMEMATCH_vw_memberdata.[Medlemsnavn 2]),
PAMRA_tbl_navnmatch.Medlemsnummer = convert(int, NAMEMATCH_vw_memberdata.Medlemsnummer),
PAMRA_tbl_navnmatch.Nationalitet = convert(char(10), NAMEMATCH_vw_memberdata.Nationalitet),
PAMRA_tbl_navnmatch.Organisationsnummer = convert(char(10),NAMEMATCH_vw_memberdata.Organisat ionsnummer),
PAMRA_tbl_navnmatch.Medlemskab = convert(char(20), NAMEMATCH_vw_memberdata.Medlemsskab),
PAMRA_tbl_navnmatch.IPDnummer = convert(int, NAMEMATCH_vw_memberdata.[IPD Nummer]),
PAMRA_tbl_navnmatch.IPDroll = convert(char(20), NAMEMATCH_vw_memberdata.IPDrolle),
PAMRA_tbl_navnmatch.Franavision = 1
FROM PAMRA_tbl_navnmatch INNER JOIN NAMEMATCH_vw_memberdata ON ltrim(rtrim(PAMRA_tbl_navnmatch.Matchfelt)) = ltrim(rtrim(NAMEMATCH_vw_memberdata.[Sgenavn]))
WHERE PAMRA_tbl_navnmatch.PerformerID = @.test
END
CLOSE Perfer
DEALLOCATE Perfer
GO
Is there any way to speed things up? I mean, its been running for more than 45 minutes now. I can track the progress, and it does move forward, BUT yawn its slow.
Its even run on a dual xeon 3.2 server with 4 gigs of memory, only other acticity is a few simple selects on other databases. No locks or anything.
Whats amiss? or is the comparison between char fields just dreadded?HUH?
First, your FETCH Statement doesn't have an into.
Second you don't need the cursor
Third you're already refereincing the table in the update that's in the cursor.
Forth, you're going to update all rows anyway...
Is someone playing a trick on you?|||index on NAMEMATCH_vw_memberdata?
why are you using a cursor for this ? it looks as if you should be able to use an insert...
-Kilka|||why are you using a cursor for this ? it looks as if you should be able to use an insert...
-Kilka
Huh?
This should do the same thing.
UPDATE n
SET Sgenavn = convert(char(50),NAMEMATCH_vw_memberdata.Sgenavn)
, Medlemsnavn = convert(char(50),NAMEMATCH_vw_memberdata.Medlemsna vn)
, Medlemsnavn2 = convert(char(50),NAMEMATCH_vw_memberdata.[Medlemsnavn 2])
, Medlemsnummer = convert(int, NAMEMATCH_vw_memberdata.Medlemsnummer)
, Nationalitet = convert(char(10), NAMEMATCH_vw_memberdata.Nationalitet)
, Organisationsnummer = convert(char(10),NAMEMATCH_vw_memberdata.Organisat ionsnummer)
, Medlemskab = convert(char(20), NAMEMATCH_vw_memberdata.Medlemsskab)
, IPDnummer = convert(int, NAMEMATCH_vw_memberdata.[IPD Nummer])
, IPDroll = convert(char(20), NAMEMATCH_vw_memberdata.IPDrolle)
, Franavision = 1
FROM PAMRA_tbl_navnmatch n
INNER JOIN NAMEMATCH_vw_memberdata m
ON ltrim(rtrim(n.Matchfelt)) = ltrim(rtrim(m.[Sgenavn]))|||yup :)
do you have better luck that way ?|||the only reason why I do it using a cursor is because the full update simply dies... it takes yonks time...
my first guess was "somethings terribly wrong"... which is true... but since I can't change that the server is slow, I figured doing it cursor-wise, record by record, the update would take time, but in the end complete anyway.
So, yes, something is playing with, the fact that the database is - apparently - mindnumbingly slow for God knows what reason..
/Trin
P.S. I let the clean update run for 3+ hours, then I just gave up... the cursor version takes little under an hour to do.. so although not exactly Einstein, it gets the job done. I was just hoping there was any other way to boost it.|||Apparently the problem is solved.
Somewhere in the scripting of creating tables, I hadn't included indexes... so, at new creation, no indexes were made..
After I added indexes I was able to the basic UPDATE without cursor fairly quickly...
sigh..|||Let's be very clear here.
A Set Update will always out-perform a cursor based solution.
If you are having performance problems, you should trouble shoot that...not through a cursor at it...
And I'm just curious...what's with all the TRIM and CONVERT usage?
Post the DDL of thos 2 tables please.|||Troubleshooting isn't always an option when you have a deadline... the cursor got the job done on time, and now I can troubleshoot while performing the same operations on a different set of data.
The reason for the trims is that much of the populated data is inserted into the tables by various dubious access forms and excel sheets. Spaces in front of and behind stuff... so I merely do it to ensure blanks are killed off until I get to the point of trimming at the front-end.
DDL?
Friday, February 24, 2012
dbo.DTA.Tuninglog table
Can the records in this table be deleted? Currently the size of this table
is over 460 MB and I would like to clear it, but I am not sure what it is
used for (used by the database tuning engine I would guess).
Thanks.
Hi Tim.
This table is used for DTA's tuning log. You can TRUNCATE the table if you
want to free up space and you no longer care about its contents.
The following Books Online Link contains information on the tuning log:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8edc95f8-581f-4391-9fe9-5ae8d03f9193.htm
Also, the DTA command-line utility allows you to store the tuning log on a
different table of your choice:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/a0b210ce-9b58-4709-80cb-9363b68a1f5a.htm
Regards,
Leo
"Tim Kelley" <tkelley@.company.com> wrote in message
news:O4rYjKx9GHA.2408@.TK2MSFTNGP05.phx.gbl...
> Can the records in this table be deleted? Currently the size of this
> table is over 460 MB and I would like to clear it, but I am not sure what
> it is used for (used by the database tuning engine I would guess).
> Thanks.
>
is over 460 MB and I would like to clear it, but I am not sure what it is
used for (used by the database tuning engine I would guess).
Thanks.
Hi Tim.
This table is used for DTA's tuning log. You can TRUNCATE the table if you
want to free up space and you no longer care about its contents.
The following Books Online Link contains information on the tuning log:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8edc95f8-581f-4391-9fe9-5ae8d03f9193.htm
Also, the DTA command-line utility allows you to store the tuning log on a
different table of your choice:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/a0b210ce-9b58-4709-80cb-9363b68a1f5a.htm
Regards,
Leo
"Tim Kelley" <tkelley@.company.com> wrote in message
news:O4rYjKx9GHA.2408@.TK2MSFTNGP05.phx.gbl...
> Can the records in this table be deleted? Currently the size of this
> table is over 460 MB and I would like to clear it, but I am not sure what
> it is used for (used by the database tuning engine I would guess).
> Thanks.
>
dbo.DTA.Tuninglog table
Can the records in this table be deleted? Currently the size of this table
is over 460 MB and I would like to clear it, but I am not sure what it is
used for (used by the database tuning engine I would guess).
Thanks.Hi Tim.
This table is used for DTA's tuning log. You can TRUNCATE the table if you
want to free up space and you no longer care about its contents.
The following Books Online Link contains information on the tuning log:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8edc95f8-581f-4391-9fe9-5ae8d03f9193.htm
Also, the DTA command-line utility allows you to store the tuning log on a
different table of your choice:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/a0b210ce-9b58-4709-80cb-9363b68a1f5a.htm
Regards,
Leo
"Tim Kelley" <tkelley@.company.com> wrote in message
news:O4rYjKx9GHA.2408@.TK2MSFTNGP05.phx.gbl...
> Can the records in this table be deleted? Currently the size of this
> table is over 460 MB and I would like to clear it, but I am not sure what
> it is used for (used by the database tuning engine I would guess).
> Thanks.
>
is over 460 MB and I would like to clear it, but I am not sure what it is
used for (used by the database tuning engine I would guess).
Thanks.Hi Tim.
This table is used for DTA's tuning log. You can TRUNCATE the table if you
want to free up space and you no longer care about its contents.
The following Books Online Link contains information on the tuning log:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8edc95f8-581f-4391-9fe9-5ae8d03f9193.htm
Also, the DTA command-line utility allows you to store the tuning log on a
different table of your choice:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/a0b210ce-9b58-4709-80cb-9363b68a1f5a.htm
Regards,
Leo
"Tim Kelley" <tkelley@.company.com> wrote in message
news:O4rYjKx9GHA.2408@.TK2MSFTNGP05.phx.gbl...
> Can the records in this table be deleted? Currently the size of this
> table is over 460 MB and I would like to clear it, but I am not sure what
> it is used for (used by the database tuning engine I would guess).
> Thanks.
>
dbo.DTA.Tuninglog table
Can the records in this table be deleted? Currently the size of this table
is over 460 MB and I would like to clear it, but I am not sure what it is
used for (used by the database tuning engine I would guess).
Thanks.Hi Tim.
This table is used for DTA's tuning log. You can TRUNCATE the table if you
want to free up space and you no longer care about its contents.
The following Books Online Link contains information on the tuning log:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8edc95f8-581f-4391-9fe9-5ae8
d03f9193.htm
Also, the DTA command-line utility allows you to store the tuning log on a
different table of your choice:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/a0b210ce-9b58-4709-80cb-
9363b68a1f5a.htm
Regards,
Leo
"Tim Kelley" <tkelley@.company.com> wrote in message
news:O4rYjKx9GHA.2408@.TK2MSFTNGP05.phx.gbl...
> Can the records in this table be deleted? Currently the size of this
> table is over 460 MB and I would like to clear it, but I am not sure what
> it is used for (used by the database tuning engine I would guess).
> Thanks.
>
is over 460 MB and I would like to clear it, but I am not sure what it is
used for (used by the database tuning engine I would guess).
Thanks.Hi Tim.
This table is used for DTA's tuning log. You can TRUNCATE the table if you
want to free up space and you no longer care about its contents.
The following Books Online Link contains information on the tuning log:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8edc95f8-581f-4391-9fe9-5ae8
d03f9193.htm
Also, the DTA command-line utility allows you to store the tuning log on a
different table of your choice:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/a0b210ce-9b58-4709-80cb-
9363b68a1f5a.htm
Regards,
Leo
"Tim Kelley" <tkelley@.company.com> wrote in message
news:O4rYjKx9GHA.2408@.TK2MSFTNGP05.phx.gbl...
> Can the records in this table be deleted? Currently the size of this
> table is over 460 MB and I would like to clear it, but I am not sure what
> it is used for (used by the database tuning engine I would guess).
> Thanks.
>
Tuesday, February 14, 2012
DB-LIB and SQL 2005
Hello,
I've an application developped in VC ++ 6.0 using Db library for bulk copying flat files records to a SQL server (SQL server 7) table. We're trying to migrate to MSSQL 2005. While performing tests, the bcp command fails telling that "The primary key constraint has been violated'. All other Db lib command works with no problem. I've even changed the format file (.fmt) in eliminating the Primary key field as well as in the flat file (source file). But the bcp_exec command succeeds with no problem if i delete the primary constraint. Where's the problem with this new version of SQL SERVER 2005 ?
Thanks for your help
Let me see if I understand you correctly.
You have an application that copies a flat file into a SQL Server 2005 table using bcp_exec in the DB-Lib. When trying to do this, you receive the error "The primary key constraint has been violated". If you delete the primary key constraint on the table, bcp_exec then succeeds. Is this correct? Did the table in SQL Server 7 contain the primary key constraint as well but imports worked anyways?
You probably know this, but I'll state it anyways -- primary keys must be unique, including that they cannot be set to NULL. If I understand correctly, simply deleting the columns from the flat file and the format file will not work as that would try to put a default value of NULL in the primary key column, something not allowed.
Does the table in SQL Server 7 already have unique values in this column? If so, I'm surprised that there would be a problem, unless there's a collision with the data already in the table on SQL Server 2005.
I would suggest comparing the schemas. If they are identical, including constraints, then see if there is something amiss in the data, a NULL or something else. If there isn't, then try looking at what's already in the SQL Server 2005 table and compare that to what's in the flat file. Is there a conflict or collision there preventing the import?
If none of these is the case, please reply and we can see what else we can find.
|||Thanks a lot for your reply.
The sql server 7.0 table contains the primary key constraint and the bcp_exec via DB-Lib works fine. The format file references this primary key and in the flat file that column contains 0 for each record. The field separator is ;. The table was created on SQLSERVER 2005 using the same commands as for SQL 7.
CREATE TABLE [dbo].[Table1] (
[Field1] [int] IDENTITY (1, 1) NOT NULL ,
etc
etc...
)
GO
ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[Field1]
) ON [PRIMARY]
GO
As i've explained earlier that when i found that the bcp_exec did'nt work with SQL 2005, i've complety removed the primary key (field1 in our ex.) from the format file and the Zros from the flat file. Nothing doing... this time bcp exec fails with no message.
Thanks a lot again
Subscribe to:
Posts (Atom)