LOCK & SELECT Behavior

I do a transactional INSERT on a table (using VB COM+ as middle tier calling
SPs) and then immediately do a SELECT on that same table (using VB COM+ to
call the SP and return data for display) to display info from the table. On
my development machine everything works fine, but I've noticed on the
production server that often, the returned dataset from the SELECT query
returns no rows (even though the old and new data are in the database as I
look later - the INSERT did work). I'm thinking since the INSERT and SELECT
are almost called simultaneously that this might have something to do with
transactional locking and that I should use WITH (NOLOCK) for the SELECT
statement.
When a SELECT is executed and the table is temporarily locked because a
transaction is still in process, is the behavior just to return no rows? or
should the rows be returned eventually? Is there any type of notification
when this happens? Right now, I get no errors, nothing in the event log, and
just don't get the rows I expect back to display even though they are there.
Thanks for any tips.What exactly do you mean by "do a transactional Insert"? Are the Insert and
Select done from the same connection or two different ones?
If the row is locked then a SELECT will be blocked unless you are using
NOLOCK or Read Uncommitted already. Can you give a little more details on
exactly what code you are calling and how?
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:%23UGy9H$2FHA.700@.TK2MSFTNGP15.phx.gbl...
>I do a transactional INSERT on a table (using VB COM+ as middle tier
>calling
> SPs) and then immediately do a SELECT on that same table (using VB COM+ to
> call the SP and return data for display) to display info from the table.
> On
> my development machine everything works fine, but I've noticed on the
> production server that often, the returned dataset from the SELECT query
> returns no rows (even though the old and new data are in the database as I
> look later - the INSERT did work). I'm thinking since the INSERT and
> SELECT
> are almost called simultaneously that this might have something to do with
> transactional locking and that I should use WITH (NOLOCK) for the SELECT
> statement.
> When a SELECT is executed and the table is temporarily locked because a
> transaction is still in process, is the behavior just to return no rows?
> or
> should the rows be returned eventually? Is there any type of notification
> when this happens? Right now, I get no errors, nothing in the event log,
> and
> just don't get the rows I expect back to display even though they are
> there.
> Thanks for any tips.
>|||The entire process of entering data from my ASP web app is transactional,
that is, if the INSERT fails (e.g. a typo error in an SP or a VBscript or
ASP error) any changes to the database will be automatically rolled back. My
VB COM+ "write" components "use transactions", my ASP code
"requires_transaction".
The INSERT and SELECT are done from different components and different
connections (same login to SQLServer though).
I haven't been using NOLOCK or Read Uncommitted in any of my SELECTs.
As far as my code, somebody fills out a web form, I pass the form to the VB
COM, that access an SP to insert the data. The same method that does the
INSERT and returns a string to the caller also calls another component that
does the SELECT, formats the returned rows as HTML and is eventually
inserted asynchronously into an existing web page (Ajax).
Since my post I've tried the NOLOCK with the SELECT statement and now the
correct rows appear more often than they did but not consistently. If I
refresh the view, all of the target rows are there including the new row. I
just don't know why the SELECT statement does not return rows when it
should.
When you say the SELECT will be blocked, what does that mean? No rows
returned, or the rows returned by a few microseconds later when the row is
not locked, or is some error logged?
Thanks for helping me out on this one.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e2ruoQ$2FHA.3880@.TK2MSFTNGP12.phx.gbl...
> What exactly do you mean by "do a transactional Insert"? Are the Insert
and
> Select done from the same connection or two different ones?
> If the row is locked then a SELECT will be blocked unless you are using
> NOLOCK or Read Uncommitted already. Can you give a little more details on
> exactly what code you are calling and how?
> --
> Andrew J. Kelly SQL MVP
>
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:%23UGy9H$2FHA.700@.TK2MSFTNGP15.phx.gbl...
to
I
with
notification
>|||> Since my post I've tried the NOLOCK with the SELECT statement and now the
> correct rows appear more often than they did but not consistently. If I
> refresh the view, all of the target rows are there including the new row.
> I
> just don't know why the SELECT statement does not return rows when it
> should.
What view? Are you using a view on a data set? Is so then it certainly
won't just appear. If it is a SQL Server view then what does the code look
like that creates the view?

> When you say the SELECT will be blocked, what does that mean? No rows
> returned, or the rows returned by a few microseconds later when the row is
> not locked, or is some error logged?
If the row that was inserted is still in an open transaction (the outer most
commit has not been issued yet) the select will wait at any locked rows
before it will read and return the data. If the commit is happening just
before the select is run it may only wait a very brief time if at all. But
it should not return any data if the row to be read is in an open
transaction and has been modified in any way. I suspect there is more to
this than it seems when it comes to how you are reading and inserting the
data. I would like to know more of these views and how many layers are in
between the com objects and the actual data.
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:e4oZre$2FHA.3020@.TK2MSFTNGP15.phx.gbl...
> The entire process of entering data from my ASP web app is transactional,
> that is, if the INSERT fails (e.g. a typo error in an SP or a VBscript or
> ASP error) any changes to the database will be automatically rolled back.
> My
> VB COM+ "write" components "use transactions", my ASP code
> "requires_transaction".
> The INSERT and SELECT are done from different components and different
> connections (same login to SQLServer though).
> I haven't been using NOLOCK or Read Uncommitted in any of my SELECTs.
> As far as my code, somebody fills out a web form, I pass the form to the
> VB
> COM, that access an SP to insert the data. The same method that does the
> INSERT and returns a string to the caller also calls another component
> that
> does the SELECT, formats the returned rows as HTML and is eventually
> inserted asynchronously into an existing web page (Ajax).
> Since my post I've tried the NOLOCK with the SELECT statement and now the
> correct rows appear more often than they did but not consistently. If I
> refresh the view, all of the target rows are there including the new row.
> I
> just don't know why the SELECT statement does not return rows when it
> should.
> When you say the SELECT will be blocked, what does that mean? No rows
> returned, or the rows returned by a few microseconds later when the row is
> not locked, or is some error logged?
> Thanks for helping me out on this one.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e2ruoQ$2FHA.3880@.TK2MSFTNGP12.phx.gbl...
> and
> to
> I
> with
> notification
>sql

Local instances being used for all linked (remote) instances

I'm getting this bizarre behavior where the local instances are being used i
ntead of the remote instances I'm trying to attach to. For background sake,
I'm running on a Windows 2003 Server that is a Domain controller. The other
SQL instances are on other
systems on another domain.
On the Windows 2003 server that has this problem it can only connect to remo
te instances that have similar names and then it overrides it with its own i
nstance. For example:
2003 server domain D2:
Instance: SRV1 (default, using maching name only)
Other systems on other domain D1:
Instance: SRV2 (default, using maching name only)
SRV2\Test
SRV3
SRV3\Test2
Assume all default instances have same sa password.
In enterprise manager on SRV1 you can register remote default instances and
it seems to connect up to them but when you view the databases you realize i
t connected to the local default instance.
Now if you try to connect up to an remote instance name that isn't on the lo
cal SRV1 system or if the password is different, it can't register it.
Finally if you go on SRV2 or SRV3, there is no conflicts registering instanc
es from the other systems including SRV1.
I tried to remove/reinstall SQL (and all instances) from SRV1, but same thin
g occurred.
Any ideas?Try registring the server using the IP and port number instead of the DNS
name. See if that helps..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MJF" <anonymous@.discussions.microsoft.com> wrote in message
news:F7B2A2D9-5304-4B3A-8F39-04C4AB9F390B@.microsoft.com...
> I'm getting this bizarre behavior where the local instances are being used
intead of the remote instances I'm trying to attach to. For background sake,
I'm running on a Windows 2003 Server that is a Domain controller. The other
SQL instances are on other systems on another domain.
> On the Windows 2003 server that has this problem it can only connect to
remote instances that have similar names and then it overrides it with its
own instance. For example:
> 2003 server domain D2:
> Instance: SRV1 (default, using maching name only)
> Other systems on other domain D1:
> Instance: SRV2 (default, using maching name only)
> SRV2\Test
> SRV3
> SRV3\Test2
> Assume all default instances have same sa password.
> In enterprise manager on SRV1 you can register remote default instances
and it seems to connect up to them but when you view the databases you
realize it connected to the local default instance.
> Now if you try to connect up to an remote instance name that isn't on the
local SRV1 system or if the password is different, it can't register it.
> Finally if you go on SRV2 or SRV3, there is no conflicts registering
instances from the other systems including SRV1.
> I tried to remove/reinstall SQL (and all instances) from SRV1, but same
thing occurred.
> Any ideas?
>
>|||Also look at the SQL Client Network Utility on the SRV1 server and see if
there is an alias that points to SRV1. It could be using that alias when
you rgister the other SRV1 servers. If there is one there, remove it and
see what happnes. You should not need an alias on the actual server itself.
Rand
This posting is provided "as is" with no warranties and confers no rights.