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