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

location of subscription database?

Hi, I have set up transactional replication. I have specified the folder
that the snapshot is to reside in (on our SAN). But how does one specify the
location of the database tables on the subscriber end? I wish to place the
..mdf and .ldf that the subecriber picks up, in a pre-defined location. I
cannot see it in the publisher properties or the agent properties. I can
only see the option to define the 'snapshot location' .. but what about the
subecriber database location? any help most appreciated!
I installed a fresh instance of SQL as the subscriber. I created the
publication and pushed it to the subscriber. Do i have to create a dummy
database (with same name as the database to be replicated) on the subecriber
and predefine the location of the .mdf and .ldf files before i start the
transactional replication? What steps am i missing?
Any help most appreciated!
cheers, john
Did you create a subscription yet? During that, it asks you where you want to
put your replicated db. You can create that prior to your setup and point
your subscription to point to that db. Also, when you are creating the
subscription, it asks you for the subscription db. At this time, you can
chose the one you created or you can create one with all the things you want
to define.
Tejas
|||Replication needs a preexisting database. There is no way to deploy a
database through replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"john r" <johnr@.trailer.com> wrote in message
news:%23z1ChEF8FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Hi, I have set up transactional replication. I have specified the folder
> that the snapshot is to reside in (on our SAN). But how does one specify
> the location of the database tables on the subscriber end? I wish to place
> the .mdf and .ldf that the subecriber picks up, in a pre-defined location.
> I cannot see it in the publisher properties or the agent properties. I can
> only see the option to define the 'snapshot location' .. but what about
> the subecriber database location? any help most appreciated!
> I installed a fresh instance of SQL as the subscriber. I created the
> publication and pushed it to the subscriber. Do i have to create a dummy
> database (with same name as the database to be replicated) on the
> subecriber and predefine the location of the .mdf and .ldf files before i
> start the transactional replication? What steps am i missing?
> Any help most appreciated!
> cheers, john
>
|||Hilary, when you are creating a subscription through the Create subscription
Wizard, What's the "Choose Destination Database"? There, it says browse or
create and when you click on it, It has a button to "Create New" and when you
click that, doesn't it allow you to replicate to the newly created db? Am i
missing something? Please tell me if i'm wrong. Thank you.
Tejas.
|||You are completely correct here Tejas. But if you script your subscription
out and then modify it for another subscriber which does not have the
subscription database it will not create it and the agent will fail. Or if
you deploy the subscription to a subscription group or multiple subscribers
you will not have the option to precreate the subscription database through
the Wizard.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:7BE03DE5-DA5F-43E5-A9C6-1CEBCF44EAD5@.microsoft.com...
> Hilary, when you are creating a subscription through the Create
> subscription
> Wizard, What's the "Choose Destination Database"? There, it says browse or
> create and when you click on it, It has a button to "Create New" and when
> you
> click that, doesn't it allow you to replicate to the newly created db? Am
> i
> missing something? Please tell me if i'm wrong. Thank you.
> Tejas.

LOCAL USER vs GLOBAL USER set in agent service

My replication is not working. I was able to create a transactional replication successfully. However, when i tried starting the agent on the snapshot, it wouldn't work. I figure that it was because my agent service login is different from that of the subscriber. my publisher and distributor is on local user whereas my subsciber sql services use our global login.

Thank you in advance. Good day!

Can you post the error message you're seeing while trying to start snapshot agent job? From that we can confirm whether the issue you're seeing is due to agent account setup. Sometimes the issue may be caused by local user doesn't have permission on network resources.

Thanks,

Zhiqiang Feng

|||

mongol wrote:

My replication is not working. I was able to create a transactional replication successfully. However, when i tried starting the agent on the snapshot, it wouldn't work. I figure that it was because my agent service login is different from that of the subscriber. my publisher and distributor is on local user whereas my subsciber sql services use our global login.

Thank you in advance. Good day!

hi mongol

be sure the the login used by the service has an NTFS permission in the

replication shared folder

and the services has also write permission to the replication target database