deAttach database: scenario

Showing posts with label scenario. Show all posts

Sunday, March 25, 2012

Deadlock that does not make sence.

I am getting a deadlock error message that is perplexing me.
Our scenario is like this; separate applications using ADO and ADO.NET with EnterpriseServices are coexisting in this project. The
database and stored procedures are what we have in common. We are doing some testing now to see how well these different
applications can get along with each other, and in one of our tests, ADO edits random records and ADO.NET selects all records, we
have received a deadlock error in the ADO.NET application.
We are both in a transaction when this occurs. The transaction on the ADO side is per record, and the transaction on the ADO.NET
side is for the entire select.
I can understand that the select will want to read a record that is locked by the edit, however the edit is so quick that I would
think that the select would not be blocked very long. If that were the case, where the select was blocked too long, wouldn't there
be a timeout instead of a deadlock?
The other factor here is that if we only edit 5 records we never get a deadlock. If we edit more than that, we always get a
deadlock.
Any insight into this perplexing problem would be very welcomed.
MikeHard to say without having access to your db, but the scenario you describe
doesn't sound like deadlock proof.
Sometimes deadlock might occur because of lack of appropriate indexes, and
sometimes because of the way your applications/transactions are written.
Best way to figure this out is to use Profiler to trace statement starting,
deadlock and deadlock chain events. Once you identify the conflicting
processes, reopen the trace file and filter by process id's. Move your way
upwards from the deadlock event and write down a time-based chain of events
under columns representing the different processes. Examine the tables'
indexes and try to figure out the cause of the deadlock.
--
BG, SQL Server MVP
Solid Quality Learning
www.solidqualitylearning.com
"Mike Malter" <mikemalter@.nospam.com> wrote in message
news:Ot0HQ5idDHA.736@.TK2MSFTNGP09.phx.gbl...
> I am getting a deadlock error message that is perplexing me.
> Our scenario is like this; separate applications using ADO and ADO.NET
with EnterpriseServices are coexisting in this project. The
> database and stored procedures are what we have in common. We are doing
some testing now to see how well these different
> applications can get along with each other, and in one of our tests, ADO
edits random records and ADO.NET selects all records, we
> have received a deadlock error in the ADO.NET application.
> We are both in a transaction when this occurs. The transaction on the ADO
side is per record, and the transaction on the ADO.NET
> side is for the entire select.
> I can understand that the select will want to read a record that is locked
by the edit, however the edit is so quick that I would
> think that the select would not be blocked very long. If that were the
case, where the select was blocked too long, wouldn't there
> be a timeout instead of a deadlock?
> The other factor here is that if we only edit 5 records we never get a
deadlock. If we edit more than that, we always get a
> deadlock.
> Any insight into this perplexing problem would be very welcomed.
> Mike
>|||First of all, do you have triggers on your tables? If you do, that's the
place you need to check.
Second, make sure both your applications use Optimistic concurrency control.
Especially the one that only reads should be optimistic.
Finally,
open your books online and check for the "ROWLOCK" documentation. It gives
you a list of query hints like "Read Past" which skips locked rows, or
HoldLock which will wait for the records.
"Mike Malter" <mikemalter@.nospam.com> wrote in message
news:Ot0HQ5idDHA.736@.TK2MSFTNGP09.phx.gbl...
> I am getting a deadlock error message that is perplexing me.
> Our scenario is like this; separate applications using ADO and ADO.NET
with EnterpriseServices are coexisting in this project. The
> database and stored procedures are what we have in common. We are doing
some testing now to see how well these different
> applications can get along with each other, and in one of our tests, ADO
edits random records and ADO.NET selects all records, we
> have received a deadlock error in the ADO.NET application.
> We are both in a transaction when this occurs. The transaction on the ADO
side is per record, and the transaction on the ADO.NET
> side is for the entire select.
> I can understand that the select will want to read a record that is locked
by the edit, however the edit is so quick that I would
> think that the select would not be blocked very long. If that were the
case, where the select was blocked too long, wouldn't there
> be a timeout instead of a deadlock?
> The other factor here is that if we only edit 5 records we never get a
deadlock. If we edit more than that, we always get a
> deadlock.
> Any insight into this perplexing problem would be very welcomed.
> Mike
>|||Just some general information here. Maybe it is of some help to you.
In SQL-Server, there are basically two situations that may lead to
deadlocks:
1. Locks are acquired in different order in different transactions. This
is the 'classic' deadlock. The chance is increased when the transactions
can not use an index to lock at row level. The chance is also increased
when there are many lock requests, or when memory is low.
2. Lock escalation (from row/page locks to table locks) can lead to
deadlocks. If lock escalation is caused by low memory, then lowering
your locking granularity from row locks to page locks may help.
Gert-Jan
Mike Malter wrote:
> I am getting a deadlock error message that is perplexing me.
> Our scenario is like this; separate applications using ADO and ADO.NET with EnterpriseServices are coexisting in this project. The
> database and stored procedures are what we have in common. We are doing some testing now to see how well these different
> applications can get along with each other, and in one of our tests, ADO edits random records and ADO.NET selects all records, we
> have received a deadlock error in the ADO.NET application.
> We are both in a transaction when this occurs. The transaction on the ADO side is per record, and the transaction on the ADO.NET
> side is for the entire select.
> I can understand that the select will want to read a record that is locked by the edit, however the edit is so quick that I would
> think that the select would not be blocked very long. If that were the case, where the select was blocked too long, wouldn't there
> be a timeout instead of a deadlock?
> The other factor here is that if we only edit 5 records we never get a deadlock. If we edit more than that, we always get a
> deadlock.
> Any insight into this perplexing problem would be very welcomed.
> Mike|||May be it will be more helpful if you can enable the Trace Flags (1204,
3605) and run the scenario you are describing post the Portions of the
deadlock images that (if any) get logged in the SQL Error logs.
DBCC TRACEON(1204, 3605)
--
HTH
Satish Balusa
Corillian Corp.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:uKiIeAjdDHA.1632@.TK2MSFTNGP12.phx.gbl...
> Hard to say without having access to your db, but the scenario you
describe
> doesn't sound like deadlock proof.
> Sometimes deadlock might occur because of lack of appropriate indexes, and
> sometimes because of the way your applications/transactions are written.
> Best way to figure this out is to use Profiler to trace statement
starting,
> deadlock and deadlock chain events. Once you identify the conflicting
> processes, reopen the trace file and filter by process id's. Move your way
> upwards from the deadlock event and write down a time-based chain of
events
> under columns representing the different processes. Examine the tables'
> indexes and try to figure out the cause of the deadlock.
> --
> BG, SQL Server MVP
> Solid Quality Learning
> www.solidqualitylearning.com
>
> "Mike Malter" <mikemalter@.nospam.com> wrote in message
> news:Ot0HQ5idDHA.736@.TK2MSFTNGP09.phx.gbl...
> > I am getting a deadlock error message that is perplexing me.
> >
> > Our scenario is like this; separate applications using ADO and ADO.NET
> with EnterpriseServices are coexisting in this project. The
> > database and stored procedures are what we have in common. We are doing
> some testing now to see how well these different
> > applications can get along with each other, and in one of our tests, ADO
> edits random records and ADO.NET selects all records, we
> > have received a deadlock error in the ADO.NET application.
> >
> > We are both in a transaction when this occurs. The transaction on the
ADO
> side is per record, and the transaction on the ADO.NET
> > side is for the entire select.
> >
> > I can understand that the select will want to read a record that is
locked
> by the edit, however the edit is so quick that I would
> > think that the select would not be blocked very long. If that were the
> case, where the select was blocked too long, wouldn't there
> > be a timeout instead of a deadlock?
> >
> > The other factor here is that if we only edit 5 records we never get a
> deadlock. If we edit more than that, we always get a
> > deadlock.
> >
> > Any insight into this perplexing problem would be very welcomed.
> >
> > Mike
> >
> >
>|||Gert,
Thanks for your reply.
The deal in this case is that there is only one table called parent.
What I am doing is a select on this table for two columns while the other system is doing random updates.
I am wondering if you could elaborate a little more about too many lock requests.
Thanks.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message news:3F5CD36E.C05CE94F@.toomuchspamalready.nl...
> Just some general information here. Maybe it is of some help to you.
> In SQL-Server, there are basically two situations that may lead to
> deadlocks:
> 1. Locks are acquired in different order in different transactions. This
> is the 'classic' deadlock. The chance is increased when the transactions
> can not use an index to lock at row level. The chance is also increased
> when there are many lock requests, or when memory is low.
> 2. Lock escalation (from row/page locks to table locks) can lead to
> deadlocks. If lock escalation is caused by low memory, then lowering
> your locking granularity from row locks to page locks may help.
> Gert-Jan
>
> Mike Malter wrote:
> >
> > I am getting a deadlock error message that is perplexing me.
> >
> > Our scenario is like this; separate applications using ADO and ADO.NET with EnterpriseServices are coexisting in this project.
The
> > database and stored procedures are what we have in common. We are doing some testing now to see how well these different
> > applications can get along with each other, and in one of our tests, ADO edits random records and ADO.NET selects all records,
we
> > have received a deadlock error in the ADO.NET application.
> >
> > We are both in a transaction when this occurs. The transaction on the ADO side is per record, and the transaction on the
ADO.NET
> > side is for the entire select.
> >
> > I can understand that the select will want to read a record that is locked by the edit, however the edit is so quick that I
would
> > think that the select would not be blocked very long. If that were the case, where the select was blocked too long, wouldn't
there
> > be a timeout instead of a deadlock?
> >
> > The other factor here is that if we only edit 5 records we never get a deadlock. If we edit more than that, we always get a
> > deadlock.
> >
> > Any insight into this perplexing problem would be very welcomed.
> >
> > Mike|||Vassilis,
Thanks for your reply.
We do not have any triggers on the table in this test.
The one that reads is using ADO.NET. Do you know how to set concurrency control to Optimistic in ADO.NET?
I will look into query hints, thanks.
Mike
"Vassilis Devletoglou" <vdev@.acn.gr> wrote in message news:OjlLowjdDHA.1876@.TK2MSFTNGP12.phx.gbl...
> First of all, do you have triggers on your tables? If you do, that's the
> place you need to check.
> Second, make sure both your applications use Optimistic concurrency control.
> Especially the one that only reads should be optimistic.
> Finally,
> open your books online and check for the "ROWLOCK" documentation. It gives
> you a list of query hints like "Read Past" which skips locked rows, or
> HoldLock which will wait for the records.
>
>
> "Mike Malter" <mikemalter@.nospam.com> wrote in message
> news:Ot0HQ5idDHA.736@.TK2MSFTNGP09.phx.gbl...
> > I am getting a deadlock error message that is perplexing me.
> >
> > Our scenario is like this; separate applications using ADO and ADO.NET
> with EnterpriseServices are coexisting in this project. The
> > database and stored procedures are what we have in common. We are doing
> some testing now to see how well these different
> > applications can get along with each other, and in one of our tests, ADO
> edits random records and ADO.NET selects all records, we
> > have received a deadlock error in the ADO.NET application.
> >
> > We are both in a transaction when this occurs. The transaction on the ADO
> side is per record, and the transaction on the ADO.NET
> > side is for the entire select.
> >
> > I can understand that the select will want to read a record that is locked
> by the edit, however the edit is so quick that I would
> > think that the select would not be blocked very long. If that were the
> case, where the select was blocked too long, wouldn't there
> > be a timeout instead of a deadlock?
> >
> > The other factor here is that if we only edit 5 records we never get a
> deadlock. If we edit more than that, we always get a
> > deadlock.
> >
> > Any insight into this perplexing problem would be very welcomed.
> >
> > Mike
> >
> >
>|||The maximum number of outstanding locks is determined by some internal
formula. A major factor in this formula is the total amount of memory
that is available to SQL-Server. If the number of actual locks comes
close to this maximum number, lock escalation will occur/increase. In
that situation, SQL-Server is more likely to 'trade in' several row
locks or page locks for one table lock. If this happens for two
processes that have locks on the same table, this results in a deadlock.
BOL has a special section for "number of locks the system can allocate"
Gert-Jan
Mike Malter wrote:
> Gert,
> Thanks for your reply.
> The deal in this case is that there is only one table called parent.
> What I am doing is a select on this table for two columns while the other system is doing random updates.
> I am wondering if you could elaborate a little more about too many lock requests.
> Thanks.
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message news:3F5CD36E.C05CE94F@.toomuchspamalready.nl...
> > Just some general information here. Maybe it is of some help to you.
> >
> > In SQL-Server, there are basically two situations that may lead to
> > deadlocks:
> > 1. Locks are acquired in different order in different transactions. This
> > is the 'classic' deadlock. The chance is increased when the transactions
> > can not use an index to lock at row level. The chance is also increased
> > when there are many lock requests, or when memory is low.
> >
> > 2. Lock escalation (from row/page locks to table locks) can lead to
> > deadlocks. If lock escalation is caused by low memory, then lowering
> > your locking granularity from row locks to page locks may help.
> >
> > Gert-Jan
> >
> >
> > Mike Malter wrote:
> > >
> > > I am getting a deadlock error message that is perplexing me.
> > >
> > > Our scenario is like this; separate applications using ADO and ADO.NET with EnterpriseServices are coexisting in this project.
> The
> > > database and stored procedures are what we have in common. We are doing some testing now to see how well these different
> > > applications can get along with each other, and in one of our tests, ADO edits random records and ADO.NET selects all records,
> we
> > > have received a deadlock error in the ADO.NET application.
> > >
> > > We are both in a transaction when this occurs. The transaction on the ADO side is per record, and the transaction on the
> ADO.NET
> > > side is for the entire select.
> > >
> > > I can understand that the select will want to read a record that is locked by the edit, however the edit is so quick that I
> would
> > > think that the select would not be blocked very long. If that were the case, where the select was blocked too long, wouldn't
> there
> > > be a timeout instead of a deadlock?
> > >
> > > The other factor here is that if we only edit 5 records we never get a deadlock. If we edit more than that, we always get a
> > > deadlock.
> > >
> > > Any insight into this perplexing problem would be very welcomed.
> > >
> > > Mike|||Quick clarification: Simultaneous attempts to escalate locks on the same
table by two processes will never directly lead to deadlock. If a table
level lock (S or X) can not be acquired when escalation is attempted, the
escalation attempt is cancelled and locks will continue to be acquired at
the row/page levels.
Mike: You should verify that the update transactions always commit after a
single update. If not, the randomness of the updates could easily lead to
deadlocks. What is the isolation mode for the select -- read committed, or
stronger?
It would also be helpful if you posted the queries involved and the table
schema -- deadlocks are possible if the update modifies non-clustered index
keys even if each update transasction only modifies one row.
--
Santeri Voutilainen
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F5E2280.E7455FE3@.toomuchspamalready.nl...
> The maximum number of outstanding locks is determined by some internal
> formula. A major factor in this formula is the total amount of memory
> that is available to SQL-Server. If the number of actual locks comes
> close to this maximum number, lock escalation will occur/increase. In
> that situation, SQL-Server is more likely to 'trade in' several row
> locks or page locks for one table lock. If this happens for two
> processes that have locks on the same table, this results in a deadlock.
> BOL has a special section for "number of locks the system can allocate"
> Gert-Jan
>
> Mike Malter wrote:
> >
> > Gert,
> >
> > Thanks for your reply.
> >
> > The deal in this case is that there is only one table called parent.
> >
> > What I am doing is a select on this table for two columns while the
other system is doing random updates.
> >
> > I am wondering if you could elaborate a little more about too many lock
requests.
> >
> > Thanks.
> >
> > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F5CD36E.C05CE94F@.toomuchspamalready.nl...
> > > Just some general information here. Maybe it is of some help to you.
> > >
> > > In SQL-Server, there are basically two situations that may lead to
> > > deadlocks:
> > > 1. Locks are acquired in different order in different transactions.
This
> > > is the 'classic' deadlock. The chance is increased when the
transactions
> > > can not use an index to lock at row level. The chance is also
increased
> > > when there are many lock requests, or when memory is low.
> > >
> > > 2. Lock escalation (from row/page locks to table locks) can lead to
> > > deadlocks. If lock escalation is caused by low memory, then lowering
> > > your locking granularity from row locks to page locks may help.
> > >
> > > Gert-Jan
> > >
> > >
> > > Mike Malter wrote:
> > > >
> > > > I am getting a deadlock error message that is perplexing me.
> > > >
> > > > Our scenario is like this; separate applications using ADO and
ADO.NET with EnterpriseServices are coexisting in this project.
> > The
> > > > database and stored procedures are what we have in common. We are
doing some testing now to see how well these different
> > > > applications can get along with each other, and in one of our tests,
ADO edits random records and ADO.NET selects all records,
> > we
> > > > have received a deadlock error in the ADO.NET application.
> > > >
> > > > We are both in a transaction when this occurs. The transaction on
the ADO side is per record, and the transaction on the
> > ADO.NET
> > > > side is for the entire select.
> > > >
> > > > I can understand that the select will want to read a record that is
locked by the edit, however the edit is so quick that I
> > would
> > > > think that the select would not be blocked very long. If that were
the case, where the select was blocked too long, wouldn't
> > there
> > > > be a timeout instead of a deadlock?
> > > >
> > > > The other factor here is that if we only edit 5 records we never get
a deadlock. If we edit more than that, we always get a
> > > > deadlock.
> > > >
> > > > Any insight into this perplexing problem would be very welcomed.
> > > >
> > > > Mikesql

Wednesday, March 21, 2012

Deadlock on replication update and NOLOCK hint question

Hi all,
I have an interesting situation. We have the following scenario:
1. Server A, Database A, table A as the replication publisher
2. Server B, Database B, table B as the replication subscriber.
3. Server B, Database C
Database A was replicating an update to Server B, Database B, table
B
At the same time, a stored procedure on Server B, Database C attempted
to perform the following type of query on Server B, Database B, table
B:
insert into table B
select ...
from database D (NOLOCK)
inner join other tables all with (NOLOCK) hints
Server B, Database C's stored procedure was the deadlock victim.
My understanding of a deadlock is when two queries are competing for
the same resources and the resource with the least cost or work done
is the victim.
I would think that the NOLOCK hint would not allow Server B, Database
C's stored proc to hold resources and therefore I could see blocking
occuring with the replication update but not deadlocking since I would
think that NOLOCK would not hold on to resources.
Could the insert select with the NOLOCK have held a page lock that
caused it to hold the same resources that the update replication
statement needed and vice-versa?
Btw, I am aware of the dirty reads for the NOLOCK statements and we
use them for business purposes for our DML statements.
Any ideas would be helpful.
Thanks in advance!Hi,
I suggest you try out the tool called SQL Deadlock Detector. It monitors
your database for locks and deadlocks and provides complete information on
captured events. It tells you everything you need to know (locked objects,
blocked statements, blocking statements, etc.) to solve your
blocking/deadlock problems. The great thing about this tool is it's event
diagram which makes it exremely easy to see what exactly is going on.
You can download it from here:
http://lakesidesql.com/downloads/DLD2/2_0_2007_809/DeadlockDetector2_Setup_08-09-2007.zip.
HTH.
"techgrl" <lfischmar@.yahoo.com> wrote in message
news:1188404072.124101.68920@.k79g2000hse.googlegroups.com...
> Hi all,
> I have an interesting situation. We have the following scenario:
> 1. Server A, Database A, table A as the replication publisher
> 2. Server B, Database B, table B as the replication subscriber.
> 3. Server B, Database C
> Database A was replicating an update to Server B, Database B, table
> B
> At the same time, a stored procedure on Server B, Database C attempted
> to perform the following type of query on Server B, Database B, table
> B:
> insert into table B
> select ...
> from database D (NOLOCK)
> inner join other tables all with (NOLOCK) hints
>
> Server B, Database C's stored procedure was the deadlock victim.
> My understanding of a deadlock is when two queries are competing for
> the same resources and the resource with the least cost or work done
> is the victim.
> I would think that the NOLOCK hint would not allow Server B, Database
> C's stored proc to hold resources and therefore I could see blocking
> occuring with the replication update but not deadlocking since I would
> think that NOLOCK would not hold on to resources.
> Could the insert select with the NOLOCK have held a page lock that
> caused it to hold the same resources that the update replication
> statement needed and vice-versa?
> Btw, I am aware of the dirty reads for the NOLOCK statements and we
> use them for business purposes for our DML statements.
> Any ideas would be helpful.
> Thanks in advance!
>|||Hi,
I suggest you try out the tool called SQL Deadlock Detector. It monitors
your database for locks and deadlocks and provides complete information on
captured events. It tells you everything you need to know (locked objects,
blocked statements, blocking statements, etc.) to solve your
blocking/deadlock problems. The great thing about this tool is it's event
diagram which makes it exremely easy to see what exactly is going on.
You can download it from here:
http://lakesidesql.com/downloads/DLD2/2_0_2007_809/DeadlockDetector2_Setup_08-09-2007.zip.
HTH.
"techgrl" <lfischmar@.yahoo.com> wrote in message
news:1188404072.124101.68920@.k79g2000hse.googlegroups.com...
> Hi all,
> I have an interesting situation. We have the following scenario:
> 1. Server A, Database A, table A as the replication publisher
> 2. Server B, Database B, table B as the replication subscriber.
> 3. Server B, Database C
> Database A was replicating an update to Server B, Database B, table
> B
> At the same time, a stored procedure on Server B, Database C attempted
> to perform the following type of query on Server B, Database B, table
> B:
> insert into table B
> select ...
> from database D (NOLOCK)
> inner join other tables all with (NOLOCK) hints
>
> Server B, Database C's stored procedure was the deadlock victim.
> My understanding of a deadlock is when two queries are competing for
> the same resources and the resource with the least cost or work done
> is the victim.
> I would think that the NOLOCK hint would not allow Server B, Database
> C's stored proc to hold resources and therefore I could see blocking
> occuring with the replication update but not deadlocking since I would
> think that NOLOCK would not hold on to resources.
> Could the insert select with the NOLOCK have held a page lock that
> caused it to hold the same resources that the update replication
> statement needed and vice-versa?
> Btw, I am aware of the dirty reads for the NOLOCK statements and we
> use them for business purposes for our DML statements.
> Any ideas would be helpful.
> Thanks in advance!
>

Deadlock Manager

In what appears to be a deadlock scenario, why doesn’t the deadlock manage
r engage and determine a victim?
Here is an example of what I am talking about:
Query 1
Select statement on table "A" with a suitable where clause to define only th
e records desired.
Query 2
Multiple insert statements within a single transaction to several different
tables including table "A."
The resulting scenario is that query 2 has crossed the magic threshold of 12
50 locks and therefore escalates its lock to a table lock. Query 1, which is
now blocked, also escalates to a table lock. The two queries then sit aroun
d waiting for each other to
finish, but they never do.
Here are my questions:
The lock manager will begin escalation if a single resource is using more th
an 1250 locks on table resources, or 765 locks on index resources. If the ma
chine in question has 2GB of physical memory, how big is the memory pool for
locks?
Even though this isn't the classic deadlock scenario, why doesn't the deadlo
ck manager recognize this as a deadlock and choose a victim to help free up
the resources?
Thanks.> The lock manager will begin escalation if a single resource is using more
than 1250 locks on table resources, or 765 locks on index resources. If the
machine in question has 2GB of physical memory, how big is the memory pool
for locks?
>
If default not changed, it is 40% of memory allocated.

> Even though this isn't the classic deadlock scenario, why doesn't the
deadlock manager recognize this as a deadlock and choose a victim to help
free up the resources?
>
Since your situation is not a deadlock (it is a block), it will try to
finish the first (blocking) process. Deadlock does not have any hope of
finishing, and therefore sql server has the mechanism of getting itself out
of it.
Quentin

Deadlock Manager

In what appears to be a deadlock scenario, why doesn’t the deadlock manager engage and determine a victim?
Here is an example of what I am talking about:
Query 1
Select statement on table "A" with a suitable where clause to define only the records desired.
Query 2
Multiple insert statements within a single transaction to several different tables including table "A."
The resulting scenario is that query 2 has crossed the magic threshold of 1250 locks and therefore escalates its lock to a table lock. Query 1, which is now blocked, also escalates to a table lock. The two queries then sit around waiting for each other to
finish, but they never do.
Here are my questions:
The lock manager will begin escalation if a single resource is using more than 1250 locks on table resources, or 765 locks on index resources. If the machine in question has 2GB of physical memory, how big is the memory pool for locks?
Even though this isn't the classic deadlock scenario, why doesn't the deadlock manager recognize this as a deadlock and choose a victim to help free up the resources?
Thanks.
> The lock manager will begin escalation if a single resource is using more
than 1250 locks on table resources, or 765 locks on index resources. If the
machine in question has 2GB of physical memory, how big is the memory pool
for locks?
>
If default not changed, it is 40% of memory allocated.

> Even though this isn't the classic deadlock scenario, why doesn't the
deadlock manager recognize this as a deadlock and choose a victim to help
free up the resources?
>
Since your situation is not a deadlock (it is a block), it will try to
finish the first (blocking) process. Deadlock does not have any hope of
finishing, and therefore sql server has the mechanism of getting itself out
of it.
Quentin

Monday, March 19, 2012

deadlock due to transactions within a single SPID(syslockinfo table)

I got a deadlock scenario and so I took a snap shot of the syslockinfo table. I found out an interesting scenario where the deadlock is because of two transactions within the same spid.

1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 113 0 1 96462284 00000000-0000-0000-0000-000000000000

1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 67108864 113 0 1 96594607 00000000-0000-0000-0000-000000000000

For the first one the lock was granted(transactionID:96462284)
but for the next one(transactionId:96594607) it was put in wait state and SQL server detected it as a deadlock.

I just wanted some clarifications.
1) what is the significance of req_transactionID column in syslockinfo table?
2) what is the relationship between req_transactionId column and spid column?
3) In case of nested transactions what will be the values of these 2 columns and what will be the relationship between them in that scenario?
4) In this case the deadlock is occurring while executing a SP(the nested level of calls go till 3rd level).
5) Will there be contention for locks between transactions within a single spid.

it is something like this:
sp_cache
sp_cache1
while
sp_cache2
end while

the deadlock occurred when executing sp_cache 2.
At that time there were only two transactionId values in the syslockinfo table for this spid,They were:
1) 96462284
2)96594607

If some body could please help me it would be really helpful for me.

Thanks in advance!!

have you got a deadlock trace set on your server, or a deadlock graph from profiler (if on 2005)?

either would give alot more diagnostic info

|||

Hi, Yes this the exact situation i am facing here as well.

Point to note here is the database is TEMPDB. (i.e. rsc_dbid = 2).

To see the below text properly, please copy from here and paste in notepad then it would be easier to understand.

rsc_text rsc_bin rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid req_ecid req_ownertype req_transactionID req_transactionUOW
1:25339 0x00088243378C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 149 0 1 87573395 00000000-0000-0000-0000-000000000000
1:25339 0x00088243378C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 58219975 149 0 1 87685718 00000000-0000-0000-0000-000000000000

As suggested by rb1n I have enabled the traces and the profiler logs as well but nothing much helpful exists there.

Basically it really requires understanding about the field "req_transactionID " in syslockinfo table. Not much information is available about this filed on Microsoft websites.

Is it right to say:

- In a normal situation one SPID will have same "req_transactionID " in syslockinfo table?

Reason for asking this is: On normal days when my SP executes (lest say under SPID X) without any problem the syslockinfo table has same "req_transactionID " for all transaction under SPID X . i.e. "req_transactionID " never changes. Days, when deadlock happens the "req_transactionID " is different and have same kind of situation as posted above.

- If not agree with the above understanding then can someone tell me in what situation the "req_transactionID " can be different for same SPID? Or simply what is the significance of column "req_transactionID ”?

- Is this something Microsoft is aware of?

Thanks very much for you time on this.|||did you get a deadlock graph logged in sql profiler? (in the TextData)|||The deadlock occurred in sql server 2000.

I have attached the error log below:

Wait-for graph

Lock Big Smile eadlock Chain Deadlock Chain SPID = 112
Node:1
PAG: 2:4:103776 CleanCnt:2 Mode: X Flags: 0x0
Grant List 1::
Owner:0x4b713220 Mode: X Flg:0x0 Ref:1 Life:00000000 SPID:112 ECID:0
SPID: 112 ECID: 0 Statement Type: CREATE INDEX Line #: 1
Input Buf: RPC Event: gsa_proc_homepage_cache_refresh;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:112 ECID:0 Ec0x33705528) Value:0x47908120 Cost51/B87FB18)

deadlock due to transactions within a single SPID(syslockinfo table)

I got a deadlock scenario and so I took a snap shot of the syslockinfo table. I found out an interesting scenario where the deadlock is because of two transactions within the same spid.

1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 113 0 1 96462284 00000000-0000-0000-0000-000000000000

1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 67108864 113 0 1 96594607 00000000-0000-0000-0000-000000000000

For the first one the lock was granted(transactionID:96462284)
but for the next one(transactionId:96594607) it was put in wait state and SQL server detected it as a deadlock.

If some body could please help me it would be really helpful for me.

Thanks in advance!!

have you got a deadlock trace set on your server, or a deadlock graph from profiler (if on 2005)?

either would give alot more diagnostic info

|||

Hi, Yes this the exact situation i am facing here as well.

Point to note here is the database is TEMPDB. (i.e. rsc_dbid = 2).

To see the below text properly, please copy from here and paste in notepad then it would be easier to understand.

As suggested by rb1n I have enabled the traces and the profiler logs as well but nothing much helpful exists there.

I have attached the error log below:

Wait-for graph

Thursday, March 8, 2012

DDl Triggers for tables...

Hi,

I have a scenario in which I need to restrict schema changes for around 5 tables only in a database. When changes are done to the other tables, it should be allowed. I am planning to use DDL Trigger. But DDL Trigger is having only 2 options in the ON Clause like ON DATABASE and ON ALL SERVERS. If i use On Database I will not be able to make modifications in the other tables.

Is there any way i could achieve my requirement?

Regards,

Swapna.B.

Move the thread to "SQL Server Database Engine" forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1|||

If you define your trigger on the DDL_TABLE_EVENTS event group then, within the trigger, you can parse the EventData function's value to return the following values when an ALTER TABLE statement is issued:

<EVENT_INSTANCE> <EventType>type</EventType>
 <PostTime>date-time</PostTime>
 <SPID>spid</SPID>
 <ServerName>name</ServerName>
 <LoginName>name</LoginName>
 <UserName>name</UserName>
 <DatabaseName>name</DatabaseName>
 <SchemaName>name</SchemaName>
 <ObjectName>name</ObjectName>
 <ObjectType>type</ObjectType>
 <TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>

Inside the trigger's code you should check to see if the ObjectName and SchemaName values match those of any of the tables that you want to preserve and then issue a rollback command if necessary.

Check out the EVENTDATA Function topic in BOL for more info.

Chris

|||

Hi,

Thanks a lot for the help provided. I have achieved the requirement by using eventdata function and validating the values in a separate sp.

I have the DDL trigger which calls a stored procedure. This SP does the segregating of values from eventdata function , validating those values and commits or rollsback according to the objectname retrieved. Now its working fine. But i face one error like the one below.

When ever the DDL Trigger is executed, it throws an error like

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

The transactions are getting completed successfully but this error occurs everytime we try to make changes to any table in the database.

What is the reason for this error? Kindly let me know the way to avoid it.

One more point here is when we try to execute a batch of alter statements without go command in the database in which the DDL trigger is present, Only the first one gets executed other statements are not getting considered. Is this anyway related to the error above?

Thanks and Regards,

Swapna.B.

|||

Could you post the definitions of both your trigger and your stored proc?

Chris

|||

Hi Chris,

Here is the definition of my trigger and Stored Procedure.

Trigger :

USE [Jeux]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

create trigger [DDL_TRG_DB] on database for ALTER_TABLE as

set ANSI_NULLS ON

set ANSI_PADDING ON

set ANSI_WARNINGS ON

set ARITHABORT ON

set CONCAT_NULL_YIELDS_NULL ON

set NUMERIC_ROUNDABORT OFF

set QUOTED_IDENTIFIER ON

declare @.EventData xml

set @.EventData=EventData()

exec sp_Sample @.EventData, 1

SET ANSI_NULLS OFF

SET QUOTED_IDENTIFIER OFF

ENABLE TRIGGER [DDL_TRG_DB] ON DATABASE

Stored Procedure :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

create procedure [dbo].[sp_Sample]

(

@.EventData xml

,@.procmapid int

)

begin

set nocount on

if is_member('db_owner') <> 1

begin

raiserror (21050, 16, -1)

return (1)

end

-- validate the procmapid

if @.procmapid not in (1,2,3,4)

begin

raiserror(15021, 16, -1, '@.procmapid')

Rollback Transaction

Return (1);

end

declare @.object_name sysname

,@.object_owner sysname

,@.qual_object_name nvarchar(512) --qualified 3-part-name

,@.objid int

,@.objecttype varchar(32)

,@.encrypted nvarchar(32)

,@.pass_through_scripts nvarchar(max)

,@.eventDoc int

,@.db_name sysname

,@.targetobject nvarchar(51)

set @.targetobject=N''

-- parse event data

select @.object_name = event_instance.value('ObjectName[1]', 'sysname')

,@.object_owner = event_instance.value('SchemaName[1]', 'sysname')

,@.objecttype = event_instance.value('ObjectType[1]', 'varchar(32)')

,@.encrypted = event_instance.value('(TSQLCommand/SetOptions/@.ENCRYPTED)[1]', 'nvarchar(32)')

,@.pass_through_scripts = event_instance.value('(TSQLCommand/CommandText)[1]', 'nvarchar(max)')

,@.targetobject = event_instance.value('TargetObjectName[1]', 'nvarchar(512)')

FROM @.EventData.nodes('/EVENT_INSTANCE') as R(event_instance)

select @.qual_object_name = QUOTENAME(@.object_owner) + N'.' + QUOTENAME(@.object_name)

select @.objid = object_id(@.qual_object_name)

select @.db_name=db_name()

select @.pass_through_scripts = sys.fn_replgetparsedddlcmd(@.pass_through_scripts

,N'ALTER'

,@.objecttype

,@.db_name

,@.object_owner

,@.object_name

,@.targetobject)

if UPPER(@.objecttype) != N'TABLE' and UPPER(@.objecttype) != N'TRIGGER'

begin

select @.pass_through_scripts = N'ALTER ' + @.objecttype + N' '

+ @.qual_object_name + N' '

+ @.pass_through_scripts

end

If (@.procmapid = 1)

begin

IF(@.object_name in (Select Article from MSsubscription_articles))

begin

Print 'Alter table Statements are not allowed in this table.'

Rollback Transaction

end

Else

begin

Commit Transaction

Print 'Transaction Commited!!!!!'

end

Kindly check and let me know.

Thanks and Regards,

Swapna.B.

|||

Try removing 'COMMIT TRANSACTION' from the second BEGIN END block at the end of your stored proc, see below, leave ROLLBACK TRANSACTION in place.

Chris

If (@.procmapid = 1)

BEGIN

IF(@.object_name in (SELECT Article from MSsubscription_articles))

BEGIN

Print 'Alter table Statements are not allowed in this table.'
Rollback Transaction

end

--Else

--BEGIN

--Commit Transaction
--Print 'Transaction Commited!!!!!'

--end

end

DDL Trigger...

Hi,

I have a scenario in which i need to restrict the schema changes of around 5 tables in a database. If I use ON Database option of DDL Trigger, the restriction is imposed on the entire database. But the requirement is retricting only 5 tables in the database, since other tables will undergo some schame changes in the future.

Is there anyway I could achieve this requirement?

Thanks,

Swapna.B.

Take a look into the eventdata function. It can be used within your code to scope the restriction to a specific set of tables.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/675b8320-9c73-4526-bd2f-91ba42c1b604.htm

|||

Hi,

I have achieved the requirement using eventdata function and validating the values in a separate sp. Thanks for the help provided.

When ever the DDL Trigger is executed, it throws an error like

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

The transactions are getting completed successfully but this error occurs everytime we try to make changes to any table in the database.

What is the reason for this error? Kindly let me know the way to avoid it.

Thanks and Regards,

Swapna.B.

Saturday, February 25, 2012

DCOM Error 10005

I have a setup where in following scenario i get DCOM error
1. Install a 32 bit NT service on x64
2. Install SQL 2005 Server
3. Upgrade the 32bit NT service using msi package.
After step 2 the NT service is running fine.
But after step 3,
Service fails to start with Error,
The MyService service failed to start due to the following error:
The service did not respond to the start or control request in a
timely fashion. (event 7000)
Event viewer also shows the DCOM error 10005.
DCOM got error "The service did not respond to the start or control
request in a timely fashion. " attempting to start the service
MyService with arguments "-Service" in order to run the server.
This problem is seen only on setups where i have SQL 2005 installed.
Is there a way to find out why "Service Control Manager" manager gives
errors 7000 & 7009. '
MyService is created using VC6 ATL wizard. It is run in "Local System"
account and in non-interactive mode.
Any help will be greatly appreciated.
Thanks,
wnHi
"wn123456@.gmail.com" wrote:
> I have a setup where in following scenario i get DCOM error
> 1. Install a 32 bit NT service on x64
> 2. Install SQL 2005 Server
> 3. Upgrade the 32bit NT service using msi package.
> After step 2 the NT service is running fine.
> But after step 3,
> Service fails to start with Error,
> The MyService service failed to start due to the following error:
> The service did not respond to the start or control request in a
> timely fashion. (event 7000)
> Event viewer also shows the DCOM error 10005.
> DCOM got error "The service did not respond to the start or control
> request in a timely fashion. " attempting to start the service
> MyService with arguments "-Service" in order to run the server.
> This problem is seen only on setups where i have SQL 2005 installed.
> Is there a way to find out why "Service Control Manager" manager gives
> errors 7000 & 7009. '
> MyService is created using VC6 ATL wizard. It is run in "Local System"
> account and in non-interactive mode.
> Any help will be greatly appreciated.
> Thanks,
> wn
>
You may want to check http://support.microsoft.com/kb/892500 this shows how
to turn on logging so you may get more information.
John|||On Feb 27, 7:23 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
>
> "wn123...@.gmail.com" wrote:
> > I have a setup where in following scenario i get DCOM error
> > 1. Install a 32 bit NT service on x64
> > 2. Install SQL 2005 Server
> > 3. Upgrade the 32bit NT service using msi package.
> > After step 2 the NT service is running fine.
> > But after step 3,
> > Service fails to start with Error,
> > The MyService service failed to start due to the following error:
> > The service did not respond to the start or control request in a
> > timely fashion. (event 7000)
> > Event viewer also shows the DCOM error 10005.
> > DCOM got error "The service did not respond to the start or control
> > request in a timely fashion. " attempting to start the service
> > MyService with arguments "-Service" in order to run the server.
> > This problem is seen only on setups where i have SQL 2005 installed.
> > Is there a way to find out why "Service Control Manager" manager gives
> > errors 7000 & 7009. '
> > MyService is created using VC6 ATL wizard. It is run in "Local System"
> > account and in non-interactive mode.
> > Any help will be greatly appreciated.
> > Thanks,
> > wn
> You may want to checkhttp://support.microsoft.com/kb/892500this shows how
> to turn on logging so you may get more information.
> John- Hide quoted text -
> - Show quoted text -
Thanks John.
I tried by adding registry entries mentioned in the KB article. But i
am not getting any additional error events.
Looks like this is not an DCOM related problem.
I have another NT service which is not a COM service that too shows
the same problem.
Do you have any idea if i can enable some logging for "Service control
Manager" ?
The control do not reach to ServiceMain function of MyService.
Is it that "Service control Manager" is returning error ?|||Hi
> Thanks John.
> I tried by adding registry entries mentioned in the KB article. But i
> am not getting any additional error events.
> Looks like this is not an DCOM related problem.
> I have another NT service which is not a COM service that too shows
> the same problem.
> Do you have any idea if i can enable some logging for "Service control
> Manager" ?
> The control do not reach to ServiceMain function of MyService.
> Is it that "Service control Manager" is returning error ?
>
Have you looked in the event log for any messages?
John

DCOM Error 10005

I have a setup where in following scenario i get DCOM error
1. Install a 32 bit NT service on x64
2. Install SQL 2005 Server
3. Upgrade the 32bit NT service using msi package.
After step 2 the NT service is running fine.
But after step 3,
Service fails to start with Error,
The MyService service failed to start due to the following error:
The service did not respond to the start or control request in a
timely fashion. (event 7000)
Event viewer also shows the DCOM error 10005.
DCOM got error "The service did not respond to the start or control
request in a timely fashion. " attempting to start the service
MyService with arguments "-Service" in order to run the server.
This problem is seen only on setups where i have SQL 2005 installed.
Is there a way to find out why "Service Control Manager" manager gives
errors 7000 & 7009. '
MyService is created using VC6 ATL wizard. It is run in "Local System"
account and in non-interactive mode.
Any help will be greatly appreciated.
Thanks,
wnHi
"wn123456@.gmail.com" wrote:

> I have a setup where in following scenario i get DCOM error
> 1. Install a 32 bit NT service on x64
> 2. Install SQL 2005 Server
> 3. Upgrade the 32bit NT service using msi package.
> After step 2 the NT service is running fine.
> But after step 3,
> Service fails to start with Error,
> The MyService service failed to start due to the following error:
> The service did not respond to the start or control request in a
> timely fashion. (event 7000)
> Event viewer also shows the DCOM error 10005.
> DCOM got error "The service did not respond to the start or control
> request in a timely fashion. " attempting to start the service
> MyService with arguments "-Service" in order to run the server.
> This problem is seen only on setups where i have SQL 2005 installed.
> Is there a way to find out why "Service Control Manager" manager gives
> errors 7000 & 7009. '
> MyService is created using VC6 ATL wizard. It is run in "Local System"
> account and in non-interactive mode.
> Any help will be greatly appreciated.
> Thanks,
> wn
>
You may want to check http://support.microsoft.com/kb/892500 this shows how
to turn on logging so you may get more information.
John|||On Feb 27, 7:23 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
>
> "wn123...@.gmail.com" wrote:
>
>
>
>
>
>
>
> You may want to checkhttp://support.microsoft.com/kb/892500this shows how
> to turn on logging so you may get more information.
> John- Hide quoted text -
> - Show quoted text -
Thanks John.
I tried by adding registry entries mentioned in the KB article. But i
am not getting any additional error events.
Looks like this is not an DCOM related problem.
I have another NT service which is not a COM service that too shows
the same problem.
Do you have any idea if i can enable some logging for "Service control
Manager" ?
The control do not reach to ServiceMain function of MyService.
Is it that "Service control Manager" is returning error ?|||Hi

> Thanks John.
> I tried by adding registry entries mentioned in the KB article. But i
> am not getting any additional error events.
> Looks like this is not an DCOM related problem.
> I have another NT service which is not a COM service that too shows
> the same problem.
> Do you have any idea if i can enable some logging for "Service control
> Manager" ?
> The control do not reach to ServiceMain function of MyService.
> Is it that "Service control Manager" is returning error ?
>
Have you looked in the event log for any messages?
John

Tuesday, February 14, 2012

DB-Library error 10038

Hi There,
We have VC++6.0 based application which uses DB-Library calls to communicate with the SQL Server2000 database.
There is typical scenario in the application where we want to process the result of a multiple-row based query in WHILE loop and execute another query inside WHILE loop based on the data in the result fetched.
The psuedo-code is as below
While (result.fetch())
{
//prepare where clause based on the data in the row fetched
char* strWhere= ...
//Execute the Query on the same connection using db-lib API
//Fetch the result
}
But DB-Library do not allow such scenario and throws below error
"DB-Library error 10038: Attempt to initiate a new SQL Server operation with results pending."
Because of the tightly coupled business logic, its impossible to change the WHILE LOOP and also the flow of the application.
Is there any solution for above said problem?
Thanks in advance.
Regards,
Yog
Hi. You have the wrong newsgroup, but one thing that would fix it
is to make the inner query with a different connection than the
outer query. An alternative would be to switch away from DbLib, so you
could do fetches in a cursor-based approach.
Joe Weinstein at BEA
Yog wrote:

> Hi There,
> We have VC++6.0 based application which uses DB-Library calls to communicate with the SQL Server2000 database.
> There is typical scenario in the application where we want to process the result of a multiple-row based query in WHILE loop and execute another query inside WHILE loop based on the data in the result fetched.
> The psuedo-code is as below
> While (result.fetch())
> {
> //prepare where clause based on the data in the row fetched
> char* strWhere= ...
> //Execute the Query on the same connection using db-lib API
> //Fetch the result
> }
> But DB-Library do not allow such scenario and throws below error
> "DB-Library error 10038: Attempt to initiate a new SQL Server operation with results pending."
> Because of the tightly coupled business logic, its impossible to change the WHILE LOOP and also the flow of the application.
> Is there any solution for above said problem?
> Thanks in advance.
> Regards,
> Yog

DB-Library error 10038

Hi There,
We have VC++6.0 based application which uses DB-Library calls to communicate with the SQL Server2000 database.
There is typical scenario in the application where we want to process the result of a multiple-row based query in WHILE loop and execute another query inside WHILE loop based on the data in the result fetched.
The psuedo-code is as below
While (result.fetch())
{
//prepare where clause based on the data in the row fetched
char* strWhere= ...
//Execute the Query on the same connection using db-lib API
//Fetch the result
}
But DB-Library do not allow such scenario and throws below error
"DB-Library error 10038: Attempt to initiate a new SQL Server operation with results pending."
Because of the tightly coupled business logic, its impossible to change the WHILE LOOP and also the flow of the application.
Is there any solution for above said problem?
Thanks in advance.
Regards,
Yog
Yog,
You will need to either fetch all the values first, close the result set,
and then do your inner loop query logic or use two separate connections to
the same database. Just like the error message says, the problem is that you
have initiated an operation that still has data to be retrieved from the
server and then attempted to execute another query.
Jim
"Yog" <y.bang@.zensar.com> wrote in message
news:C638CAD8-C4E2-49DE-955B-BE0B34977DD2@.microsoft.com...
> Hi There,
> We have VC++6.0 based application which uses DB-Library calls to
communicate with the SQL Server2000 database.
> There is typical scenario in the application where we want to process the
result of a multiple-row based query in WHILE loop and execute another query
inside WHILE loop based on the data in the result fetched.
> The psuedo-code is as below
> While (result.fetch())
> {
> //prepare where clause based on the data in the row fetched
> char* strWhere= ...
> //Execute the Query on the same connection using db-lib API
> //Fetch the result
> }
> But DB-Library do not allow such scenario and throws below error
> "DB-Library error 10038: Attempt to initiate a new SQL Server operation
with results pending."
> Because of the tightly coupled business logic, its impossible to change
the WHILE LOOP and also the flow of the application.
> Is there any solution for above said problem?
> Thanks in advance.
> Regards,
> Yog

DB-Library error 10038

Hi There,
We have VC++6.0 based application which uses DB-Library calls to communicate
with the SQL Server2000 database.
There is typical scenario in the application where we want to process the re
sult of a multiple-row based query in WHILE loop and execute another query i
nside WHILE loop based on the data in the result fetched.
The psuedo-code is as below
While (result.fetch())
{
//prepare where clause based on the data in the row fetched
char* strWhere= ...
//Execute the Query on the same connection using db-lib API
//Fetch the result
}
But DB-Library do not allow such scenario and throws below error
"DB-Library error 10038: Attempt to initiate a new SQL Server operation with
results pending."
Because of the tightly coupled business logic, its impossible to change the
WHILE LOOP and also the flow of the application.
Is there any solution for above said problem?
Thanks in advance.
Regards,
YogYog,
Have you tried opening a second connection for the internal result set?
Russell Fields
"Yog" <y.bang@.zensar.com> wrote in message
news:0E65530E-C692-4850-8DF4-800912D22DBD@.microsoft.com...
> Hi There,
> We have VC++6.0 based application which uses DB-Library calls to
communicate with the SQL Server2000 database.
> There is typical scenario in the application where we want to process the
result of a multiple-row based query in WHILE loop and execute another query
inside WHILE loop based on the data in the result fetched.
> The psuedo-code is as below
> While (result.fetch())
> {
> //prepare where clause based on the data in the row fetched
> char* strWhere= ...
> //Execute the Query on the same connection using db-lib API
> //Fetch the result
> }
> But DB-Library do not allow such scenario and throws below error
> "DB-Library error 10038: Attempt to initiate a new SQL Server operation
with results pending."
> Because of the tightly coupled business logic, its impossible to change
the WHILE LOOP and also the flow of the application.
> Is there any solution for above said problem?
> Thanks in advance.
> Regards,
> Yog|||You need to fetch all the rows to completion before issueing another query.
Ex do while .not eof()
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

DB-Library error 10038

Hi There,
We have VC++6.0 based application which uses DB-Library calls to communicate with the SQL Server2000 database.
There is typical scenario in the application where we want to process the result of a multiple-row based query in WHILE loop and execute another query inside WHILE loop based on the data in the result fetched.
The psuedo-code is as below
While (result.fetch())
{
//prepare where clause based on the data in the row fetched
char* strWhere= ...
//Execute the Query on the same connection using db-lib API
//Fetch the result
}
But DB-Library do not allow such scenario and throws below error
"DB-Library error 10038: Attempt to initiate a new SQL Server operation with results pending."
Because of the tightly coupled business logic, its impossible to change the WHILE LOOP and also the flow of the application.
Is there any solution for above said problem?
Thanks in advance.
Regards,
Yog
1. Create another connection and execute your "subquery" in that connection
2. Wait for Yukon release - it is announced that Yukon will support
simultaneous queries on the same connection
3. Use T-SQL procedure for doing such a processing instead of client-side
cursor emulation
"Yog" <y.bang@.zensar.com> wrote in message
news:E50F797E-9F9D-4487-ADAF-AD251F1DC620@.microsoft.com...
> Hi There,
> We have VC++6.0 based application which uses DB-Library calls to
communicate with the SQL Server2000 database.
> There is typical scenario in the application where we want to process the
result of a multiple-row based query in WHILE loop and execute another query
inside WHILE loop based on the data in the result fetched.
> The psuedo-code is as below
> While (result.fetch())
> {
> //prepare where clause based on the data in the row fetched
> char* strWhere= ...
> //Execute the Query on the same connection using db-lib API
> //Fetch the result
> }
> But DB-Library do not allow such scenario and throws below error
> "DB-Library error 10038: Attempt to initiate a new SQL Server operation
with results pending."
> Because of the tightly coupled business logic, its impossible to change
the WHILE LOOP and also the flow of the application.
> Is there any solution for above said problem?

DB-Library error 10038

Hi There,
We have VC++6.0 based application which uses DB-Library calls to communicate
with the SQL Server2000 database.
There is typical scenario in the application where we want to process the re
sult of a multiple-row based query in WHILE loop and execute another query i
nside WHILE loop based on the data in the result fetched.
The psuedo-code is as below
While (result.fetch())
{
//prepare where clause based on the data in the row fetched
char* strWhere= ...
//Execute the Query on the same connection using db-lib API
//Fetch the result
}
But DB-Library do not allow such scenario and throws below error
"DB-Library error 10038: Attempt to initiate a new SQL Server operation with
results pending."
Because of the tightly coupled business logic, its impossible to change the
WHILE LOOP and also the flow of the application.
Is there any solution for above said problem?
Thanks in advance.
Regards,
Yog1. Create another connection and execute your "subquery" in that connection
2. Wait for Yukon release - it is announced that Yukon will support
simultaneous queries on the same connection
3. Use T-SQL procedure for doing such a processing instead of client-side
cursor emulation
"Yog" <y.bang@.zensar.com> wrote in message
news:E50F797E-9F9D-4487-ADAF-AD251F1DC620@.microsoft.com...
> Hi There,
> We have VC++6.0 based application which uses DB-Library calls to
communicate with the SQL Server2000 database.
> There is typical scenario in the application where we want to process the
result of a multiple-row based query in WHILE loop and execute another query
inside WHILE loop based on the data in the result fetched.
> The psuedo-code is as below
> While (result.fetch())
> {
> //prepare where clause based on the data in the row fetched
> char* strWhere= ...
> //Execute the Query on the same connection using db-lib API
> //Fetch the result
> }
> But DB-Library do not allow such scenario and throws below error
> "DB-Library error 10038: Attempt to initiate a new SQL Server operation
with results pending."
> Because of the tightly coupled business logic, its impossible to change
the WHILE LOOP and also the flow of the application.
> Is there any solution for above said problem?

DB-Library error 10038

Hi There,
We have VC++6.0 based application which uses DB-Library calls to communicate with the SQL Server2000 database.
There is typical scenario in the application where we want to process the result of a multiple-row based query in WHILE loop and execute another query inside WHILE loop based on the data in the result fetched.
The psuedo-code is as below
While (result.fetch())
{
//prepare where clause based on the data in the row fetched
char* strWhere= ...
//Execute the Query on the same connection using db-lib API
//Fetch the result
}
But DB-Library do not allow such scenario and throws below error
"DB-Library error 10038: Attempt to initiate a new SQL Server operation with results pending."
Because of the tightly coupled business logic, its impossible to change the WHILE LOOP and also the flow of the application.
Is there any solution for above said problem?
Thanks in advance.
Regards,
Yog1. Create another connection and execute your "subquery" in that connection
2. Wait for Yukon release - it is announced that Yukon will support
simultaneous queries on the same connection
3. Use T-SQL procedure for doing such a processing instead of client-side
cursor emulation
"Yog" <y.bang@.zensar.com> wrote in message
news:E50F797E-9F9D-4487-ADAF-AD251F1DC620@.microsoft.com...
> Hi There,
> We have VC++6.0 based application which uses DB-Library calls to
communicate with the SQL Server2000 database.
> There is typical scenario in the application where we want to process the
result of a multiple-row based query in WHILE loop and execute another query
inside WHILE loop based on the data in the result fetched.
> The psuedo-code is as below
> While (result.fetch())
> {
> //prepare where clause based on the data in the row fetched
> char* strWhere= ...
> //Execute the Query on the same connection using db-lib API
> //Fetch the result
> }
> But DB-Library do not allow such scenario and throws below error
> "DB-Library error 10038: Attempt to initiate a new SQL Server operation
with results pending."
> Because of the tightly coupled business logic, its impossible to change
the WHILE LOOP and also the flow of the application.
> Is there any solution for above said problem?

DBDesign Q2:

Hi All!

I like employee and department scenario from DBDesgin Q.
It looks more intuitive than my previous sample.

************************************************** *********************
Business rule:

Each employee works only in one department.
Department is managed by only one of employee who works in this
department.
One employee cannot work for two different departments and one
employee cannot manage two different departments

************************************************** ************************

*********
SolutionA(not good. Two entity referent each other)

Department (DepartmentID PK, Name, eEmployeeID FK)
Employee (EmployeeID PK , Name, , dDepartmetnID FK)
To add records in solutionA to Department and Employee tables:
Add a record to Department with eEmployeeID= NULL
Add Employee records
Set eEmployeeID to value in Department table

*********
Solution B: (I think it is cleaner than A, but there is a whole)
Department (DepartmentID PK, Name)
Employee (EmployeeID PK , Name, , dDepartmetnID FK)
(the next is subtyping of employee)
DepartmentMngr(EmployeeID PK/FK, dDepartmentID FK/U1)

It will satisfy bus. rule, but there is a whole.
Employee who is a manager can reference one department in Employee
table and the other department in DepartmentMngr.

*********
Solution C.(Problem: How buss. rule can be forced that only one
employee from department is a manager?)
Department (DepartmentID PK, Name)
Employee (EmployeeID PK , Name, , dDepartmetnID FK, eEmployeeID FK)

Q. How buss. Rule can be forced that only one employee from
department is a manager?

Are there any suggestions about implementing the above business rule?
Which of implementations are you in favor?

Thank you in advance,
Andy.The following seems to match your business rules. Treat Department Manager
as an attribute of the Department rather than the Employee.

CREATE SCHEMA AUTHORIZATION dbo

CREATE TABLE Employees (empno INTEGER PRIMARY KEY, deptno INTEGER NOT NULL
REFERENCES Departments (deptno), UNIQUE (deptno, empno))

CREATE TABLE Departments (deptno INTEGER PRIMARY KEY, deptname VARCHAR(20)
NOT NULL UNIQUE, deptmanager_empno INTEGER NOT NULL, FOREIGN KEY (deptno,
deptmanager_empno) REFERENCES Employees (deptno, empno))

The usual caveat about design questions applies: it's very difficult to give
design advice online without the opportunity to research a particular
situation in detail.

--
David Portas
----
Please reply only to the newsgroup
--|||net__space@.hotmail.com (Andy) wrote in message news:<edb90340.0311262009.33976f1a@.posting.google.com>...
> Hi All!
> I like employee and department scenario from DBDesgin Q.
> It looks more intuitive than my previous sample.
> ************************************************** *********************
> Business rule:
> Each employee works only in one department.
> Department is managed by only one of employee who works in this
> department.
> One employee cannot work for two different departments and one
> employee cannot manage two different departments
> ************************************************** ************************
> *********
> SolutionA(not good. Two entity referent each other)
> Department (DepartmentID PK, Name, eEmployeeID FK)
> Employee (EmployeeID PK , Name, ?, dDepartmetnID FK)
> To add records in solutionA to Department and Employee tables:
> Add a record to Department with eEmployeeID= NULL
> Add Employee records
> Set eEmployeeID to value in Department table

I don't find this objectionable. It may pose a technical challenge,
but that can be overcome. For example, Oracle allows constraint
checking to be deferred until the end of the transaction, so you can
do this:

insert into department( departmentid, eemployeeid ) values
('D1','E1');
insert into employee (employeeid, name, ddepartmentid) values
('E1','Smith','D1');
commit;

To enforce the rule that the manager of the department must also be an
employee in the department would require an additional database
constraint (or "assertion"), which would also have to be checked at
the end of the transaction. Not all DBMS products support this though
(Oracle doesn't).

> Solution B: (I think it is cleaner than A, but there is a whole)
> Department (DepartmentID PK, Name)
> Employee (EmployeeID PK , Name, ?, dDepartmetnID FK)
> (the next is subtyping of employee)
> DepartmentMngr(EmployeeID PK/FK, dDepartmentID FK/U1)
> It will satisfy bus. rule, but there is a whole.
> Employee who is a manager can reference one department in Employee
> table and the other department in DepartmentMngr.

Another hole is that a department can be set up with no manager at
all. Again, database constraints deferred until the end of the
transaction would be required to enforce these rules (if the DBMS
supports such constraints).

> Solution C.(Problem: How buss. rule can be forced that only one
> employee from department is a manager?)
> Department (DepartmentID PK, Name)
> Employee (EmployeeID PK , Name, ?, dDepartmetnID FK, eEmployeeID FK)
> Q. How buss. Rule can be forced that only one employee from
> department is a manager?

I don't understand this one. What is employee.eemployeeid?

> Are there any suggestions about implementing the above business rule?
> Which of implementations are you in favor?

Out of those, I'd stick with solution A. Another possibility you
didn't mention is this:

Department (DepartmentID PK, Name)
Employee (EmployeeID PK , Name, ?, dDepartmetnID FK, manager_flag)

The "manager_flag" column is a boolean or yes/no type value that
specifies whether the employee is manager of the department he/she
belongs to. That enforces the rule that an employee can only manage
his own department, but does not enforce the rule that a department
must have one and only one manager - again, a deferred database
constraint is required.

Whichever solution you choose requires the addition of database
(inter-table) constraints that are deferred until the end of the
transaction. Combinations of primary, unique and foreign keys alone
cannot enforce all the rules. With most (all?) existing DBMS
products, this means that the business rules simply cannot all be
enforced in the database; some must be enforced in the application, or
by forcing use of stored procedures to maintain the data, or not
enforced at all (except by exception reporting and manual
intervention).|||well,

i just can't image how these two can be used! can you continue your sample
and try to populate the tables with some sample data.

</wqw
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Bp6dna5zxsvBf1iiRVn-hQ@.giganews.com...
> The following seems to match your business rules. Treat Department Manager
> as an attribute of the Department rather than the Employee.
> CREATE SCHEMA AUTHORIZATION dbo
> CREATE TABLE Employees (empno INTEGER PRIMARY KEY, deptno INTEGER NOT NULL
> REFERENCES Departments (deptno), UNIQUE (deptno, empno))
> CREATE TABLE Departments (deptno INTEGER PRIMARY KEY, deptname VARCHAR(20)
> NOT NULL UNIQUE, deptmanager_empno INTEGER NOT NULL, FOREIGN KEY (deptno,
> deptmanager_empno) REFERENCES Employees (deptno, empno))
> The usual caveat about design questions applies: it's very difficult to
give
> design advice online without the opportunity to research a particular
> situation in detail.
> --
> David Portas
> ----
> Please reply only to the newsgroup
> --|||> Solution B: (I think it is cleaner than A, but there is a whole)
> Department (DepartmentID PK, Name)
> Employee (EmployeeID PK , Name, ., dDepartmetnID FK)
> (the next is subtyping of employee)
> DepartmentMngr(EmployeeID PK/FK, dDepartmentID FK/U1)
> It will satisfy bus. rule, but there is a whole.
> Employee who is a manager can reference one department in Employee
> table and the other department in DepartmentMngr.

it's not clear what's the FK on DepartmentMngr refers to. try composite one
like this:

FOREIGN KEY(EmployeeID, dDepartmentID )
REFERENCES Employee (EmployeeID, dDepartmentID)

> Solution C.(Problem: How buss. rule can be forced that only one
> employee from department is a manager?)
> Department (DepartmentID PK, Name)
> Employee (EmployeeID PK , Name, ., dDepartmetnID FK, eEmployeeID FK)
> Q. How buss. Rule can be forced that only one employee from
> department is a manager?

if you go with the "manager_flag" to enforce "only one employee from a
department is its manager" you can use an indexed view. something like this:

SELECT dDepartmetnID
FROM Employee
WHERE manager_flag=1

and then create the PK on dDepartmetnID -- this will ensure no two employees
are manager of the same department.

cheers,
</wqw|||Ah! you spotted my deliberate mistake ;-).

Make Deptmanager_empno NULLable. Obviously that's a compromise to Andy's
requirements. Possibly you could make it non-nullable once you've populated
both tables though that could make for some interesting logical conundrums
if you need to carry out a management reshuffle between departments!

Possibly it might be acceptable to leave Deptmanager_empno nullable but add
a UNIQUE constraint so that no more than one department can be without a
manager at any one time.

--
David Portas
----
Please reply only to the newsgroup
--|||> > *********
> > SolutionA(not good. Two entity referent each other)
> > Department (DepartmentID PK, Name, eEmployeeID FK)
> > Employee (EmployeeID PK , Name, ?, dDepartmetnID FK)
> > To add records in solutionA to Department and Employee tables:
> > Add a record to Department with eEmployeeID= NULL
> > Add Employee records
> > Set eEmployeeID to value in Department table
> I don't find this objectionable. It may pose a technical challenge,
> but that can be overcome. For example, Oracle allows constraint
> checking to be deferred until the end of the transaction, so you can
> do this:
> insert into department( departmentid, eemployeeid ) values
> ('D1','E1');
> insert into employee (employeeid, name, ddepartmentid) values
> ('E1','Smith','D1');
> commit;
> To enforce the rule that the manager of the department must also be an
> employee in the department would require an additional database
> constraint (or "assertion"), which would also have to be checked at
> the end of the transaction. Not all DBMS products support this though
> (Oracle doesn't).

Are you ok with the style that employee references department table
and
department references employee table?

> > Solution C.(Problem: How buss. rule can be forced that only one
> > employee from department is a manager?)
> > Department (DepartmentID PK, Name)
> > Employee (EmployeeID PK , Name, ?, dDepartmetnID FK, eEmployeeID FK)
> > Q. How buss. Rule can be forced that only one employee from
> > department is a manager?
> I don't understand this one. What is employee.eemployeeid?

Employee.eEmployeeID(FK) references Employee.EmployeeID(PK)

> Out of those, I'd stick with solution A. Another possibility you
> didn't mention is this:
> Department (DepartmentID PK, Name)
> Employee (EmployeeID PK , Name, ?, dDepartmetnID FK, manager_flag)
> The "manager_flag" column is a boolean or yes/no type value that
> specifies whether the employee is manager of the department he/she
> belongs to. That enforces the rule that an employee can only manage
> his own department, but does not enforce the rule that a department
> must have one and only one manager - again, a deferred database
> constraint is required.
I am personally in favour of the solution with the flag. The problem
is more than one employee can be a managers or no managers at all if
all flag are false, but this can be fixed with a trigger.

Solution A. The problem is that the manager (Department.eEmployeeID)
can point to employee ((Employee.EmployeeID) from different department

Between the solution A and the proposed solution with flag which one
are you in favour of?|||"Vlad Vissoultchev" <wqweto@.nospam.myrealbox.com> wrote in message news:<uCXMLGStDHA.2060@.TK2MSFTNGP10.phx.gbl>...
> > Solution B: (I think it is cleaner than A, but there is a whole)
> > Department (DepartmentID PK, Name)
> > Employee (EmployeeID PK , Name, ., dDepartmetnID FK)
> > (the next is subtyping of employee)
> > DepartmentMngr(EmployeeID PK/FK, dDepartmentID FK/U1)
> > It will satisfy bus. rule, but there is a whole.
> > Employee who is a manager can reference one department in Employee
> > table and the other department in DepartmentMngr.
> it's not clear what's the FK on DepartmentMngr refers to.

DepartmentMngr.dDepartmentID refers Department.DepartmentID|||net__space@.hotmail.com (Andy) wrote in message news:<edb90340.0311272005.3500e39d@.posting.google.com>...
> > > *********
> > > SolutionA(not good. Two entity referent each other)
> > > > Department (DepartmentID PK, Name, eEmployeeID FK)
> > > Employee (EmployeeID PK , Name, ?, dDepartmetnID FK)
> > > To add records in solutionA to Department and Employee tables:
> > > Add a record to Department with eEmployeeID= NULL
> > > Add Employee records
> > > Set eEmployeeID to value in Department table
> > I don't find this objectionable. It may pose a technical challenge,
> > but that can be overcome. For example, Oracle allows constraint
> > checking to be deferred until the end of the transaction, so you can
> > do this:
> > insert into department( departmentid, eemployeeid ) values
> > ('D1','E1');
> > insert into employee (employeeid, name, ddepartmentid) values
> > ('E1','Smith','D1');
> > commit;
> > To enforce the rule that the manager of the department must also be an
> > employee in the department would require an additional database
> > constraint (or "assertion"), which would also have to be checked at
> > the end of the transaction. Not all DBMS products support this though
> > (Oracle doesn't).
> Are you ok with the style that employee references department table
> and
> department references employee table?
Yes, like I just said: "I don't find this objectionable." In reality,
I would probably allow a department to exist without a manager
assigned - a company might want to define its new department structure
first, and assign managers later. But if the rule really were that
every department MUST have a manager and every employee MUST be in a
department, then I would use deferred constraints to handle that.

> > > Solution C.(Problem: How buss. rule can be forced that only one
> > > employee from department is a manager?)
> > > Department (DepartmentID PK, Name)
> > > Employee (EmployeeID PK , Name, ?, dDepartmetnID FK, eEmployeeID FK)
> > > > Q. How buss. Rule can be forced that only one employee from
> > > department is a manager?
> > I don't understand this one. What is employee.eemployeeid?
> Employee.eEmployeeID(FK) references Employee.EmployeeID(PK)

OK, so this is a diferrent set up altogether: now it is employees who
are managed by other employees, and departments are not managed at
all. May be valid, but it isn't the same thing.

> > Out of those, I'd stick with solution A. Another possibility you
> > didn't mention is this:
> > Department (DepartmentID PK, Name)
> > Employee (EmployeeID PK , Name, ?, dDepartmetnID FK, manager_flag)
> > The "manager_flag" column is a boolean or yes/no type value that
> > specifies whether the employee is manager of the department he/she
> > belongs to. That enforces the rule that an employee can only manage
> > his own department, but does not enforce the rule that a department
> > must have one and only one manager - again, a deferred database
> > constraint is required.
> I am personally in favour of the solution with the flag. The problem
> is more than one employee can be a managers or no managers at all if
> all flag are false, but this can be fixed with a trigger.

I don't think it's that simple actually. Suppose you have a
department D1 with 2 employees E1 and E2, and that E1 is flagged as
being the manager. If a department must always have exactly 1
manager, how do you change the manager from E1 to E2? You can't
"unflag" E1 first, because then there would be no manager, and you
can't flag E2 first, because then there would be 2 managers.
This is a check that must be done at the transaction level (after both
updates), whereas triggers fire at the statement level.

Maybe you could get around that using a fancy update statement like:

update employee
set manager_flag = case when manager_flag='Y' then 'N' else 'Y' end
where employee_id in (E1, E2);

Another solution might be to use a trigger on employee to maintain a
new column "number_of_managers" on department. We can then add a
check constraint on this column: check(number_of_managers = 1). As
long as we can DEFER this check until the end of the transaction, we
are OK.

> Solution A. The problem is that the manager (Department.eEmployeeID)
> can point to employee ((Employee.EmployeeID) from different department
> Between the solution A and the proposed solution with flag which one
> are you in favour of?

Generally, I'd go for A because if every department is managed by 1
employee, that sounds like a foreign key from department to employee
to me.

Sunday, March 25, 2012

Deadlock that does not make sence.

Wednesday, March 21, 2012

Deadlock on replication update and NOLOCK hint question

Deadlock Manager

Deadlock Manager

Monday, March 19, 2012

deadlock due to transactions within a single SPID(syslockinfo table)

deadlock due to transactions within a single SPID(syslockinfo table)

Thursday, March 8, 2012

DDl Triggers for tables...

DDL Trigger...

Saturday, February 25, 2012

DCOM Error 10005

DCOM Error 10005

Tuesday, February 14, 2012

DB-Library error 10038

DB-Library error 10038

DB-Library error 10038

DB-Library error 10038

DB-Library error 10038

DB-Library error 10038

DB-Library error 10038

DBDesign Q2:

deAttach database

Blog Archive

About Me