Does SqlDataSource hold the connection ?

If we bind a GridView to a SQLDataSource, is it not a connected usage, which locks up one connection thread ?I don't think so. Once it's databound the connection should be closed. Any sorting or paging will open a new connection.|||No, the connection will be closed. However, you may tie up a connection on the server for a little while if you have connection pooling turned on.

Does Replication Agent locks the database?

Hi,
Does the first synchronization(Replication Agent) while setting up merge
replication locks both the publisher and subscriber databases or the
databases can be used while the first synchronization is taking place?
If first synchronization does not lock the databases, will the changes that
have taken place at both the ends be merged later on, when synchronization
is complete?
Thanks
Anukul
Anukul,
there is no exclusive lock on the database, if that is what you are
referring to. There is a shared lock on the database as there would be on
any connection. As far as I know, there aren't exclusive locks held on the
table during the snapshot generation in merge replication. There are
page-level shared locks which'll prevent updates while the snapshot of a
particular page is being generated, but concurrent reads are compatible. In
transactional there is the option to use concurrent snapshot processing
where concurrent changes can occur during the snapshot generation, but not
so in merge or snapshot replication.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul:
I am talking about Replication Agent, which runs after snapshot agent.
Replication Agnet does the syncronization.
Please suggest.
Thanks,
Anukul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OLV3bsFGFHA.3888@.TK2MSFTNGP12.phx.gbl...
> Anukul,
> there is no exclusive lock on the database, if that is what you are
> referring to. There is a shared lock on the database as there would be on
> any connection. As far as I know, there aren't exclusive locks held on the
> table during the snapshot generation in merge replication. There are
> page-level shared locks which'll prevent updates while the snapshot of a
> particular page is being generated, but concurrent reads are compatible.
In
> transactional there is the option to use concurrent snapshot processing
> where concurrent changes can occur during the snapshot generation, but not
> so in merge or snapshot replication.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||My understanding is that the merge agent works with
batches of records and each separate batch is treated as
a transaction, but the combined synchronization process
is not held under a global transaction. In this case, an
edit to a record on a batch already processed would be
acceptable and would enter MSmerge_contents.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Does it locks database/tables when Only Select query comes in SQLTransaction

Hi,

I want to make SQLTransaction as global and use it checking the State.
But then where there are Only Select queries are going to fire, it will open transaction.

So, Does it locks database/tables when Only Select query comes in SQLTransaction.

If you have another successful way of doing this, Please suggest.

Thanking you.

tatsA select typically only takes shared lock and releases the lock as soon as the select is done. If you want to an exclusive lock, you will have to use "tablockx" hint. Please see book online for detail.|||Thank you for your suggestion.|||

Just to top it....

SELECT COL1 FROM TBL2 WITH(NOLOCK) WHERE COL2 = @.VAL

|||You should avoid this technique or be sure to understand what is going on behind the scenes. Many people beginning with TSQL somewhere read that (NOLOCK) will not produce any problems, because one can read without careing about any locking, on the one side this is true, but you can′t be sure to have the most recent data, because you will do dirty ready on the database.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

What are you trying to accomplish? Every statement locks something, at least the schema so you can't drop a table while the user is executing a statement :)

Also, EVERY statement is in a transaction already, the difference between starting one using the client and just the implicit one that starts for every statement is that you might never reach the code to close it if the user gets bored and decides they want to go to lunch.

What locks are taken and how long locks are held is determined by the isolation level. The default is read committed (though your client defautl CAN BE DIFFERENT!) Note too that all of the locks I will talk about are shared locks for data that you read NOT updated data. Updated data requires locks to be held until the end of a transaction.

READ UNCOMMITTED - No locks issued or obeyed other than schema locks

READ COMMITTED - Locks are (generally) taken only as long as needed to protect the lowest level thing you are working with (like a row in a table.) Generally speaking it "crabs" through the objects grab a lock, release a lock. Another user can modify data you have already read

REPEATABLE READ - LIke Read Committed, only it doesn't release lock it takes. Prevents another user from modifying data you have already read, doesn't prevent user from creating new rows (referred to as Phantoms).

SERIALIZABLE - Like Repeatable Read, only it also places a lock on Ranges of data, so if you say WHERE between 0 and 100, a range lock is places on 0 and 100 to say no other user can create data in that range either.

SNAPSHOT (2005) - Readers look at only committed data, and if other users have locked portions of the data, they will only see the database as it was when they started their command. Really cool, but uses more tempdb than would otherwise.

So the only real concern about executing your select in a transaction is that you will run the risk of a user failure locking other users until cleanup occurs and the connection is noticed to be stale and not in use.

I generally would suggest that you execute your transactions in a single batch and put the BEGIN TRAN and COMMIT TRAN in the same batch if possible and using TRY...CATCH blocks around statements if you have 2005. Then you lower the risk of hanging a transaction. The connection might still exist, but the commit/rollback will certainly execute.

does "with (nolock)" absolutely guarantee no locks are taken?

I need to run a few short-running queries against a production system. I need to be absolutely certain that SS doesn't take out any locks on the table as a result.

Does "with (nolock)" absolutely guarantee this? I've read BOL on the topic and I understand isolation level read-uncommitted. But I want to validate that there's not any undocumented behavior in SS that might violate the documentation (which clearly states that no shared locks are issued).

Thanks!

You're talking two different things.

with (nolock) is a HINT. That means there is a possibility that SS will override it.

SET TRANSACTION ISOLATION LEVEL is a command. AFAIK, SS won't override this.

|||SQL Server honors the read uncommitted hint. It is however possible that you might get some error due to the dirty read behavior. The transaction isolation level is recommended if you do not want to specify hint on every table that you query and it is for the session. You can also set it in the tools option so it is always automatic. Note that this does affect behavior of any query since you will be reading uncommitted data i.e., perform dirty reads. In SQL Server 2005, you can use the READ COMMITTED SNAPSHOT option at the database level or the new snapshot isoaltion level which uses versioning mechanism to provide consistent view of data without taking locks.|||

The answer I am about to give is not likely to be pertinent to your question since you state short running, but just in case since you were so adament about NO LOCKS :) There is one tiny exception to the nolock/read uncommitted isolation level, in that it does take a shared schema lock so other users cannot drop/alter the table while your query is running. in 2005, run this:

create table testLocks
(
testLockId int,
bigValue char(8000) default (replicate('*',8000))
)

insert into testLocks(testLockId)
select 1
go 1000

set transaction isolation level read uncommitted
select * from testLocks
cross join testLocks as t2
-

Then on another connection run this (that query will return 1000000 16KB rows, so it will take a while :)

select login_name,
case des.transaction_isolation_level
when 0 then 'Unspecified' when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted' when 3 then 'Repeatable'
when 4 then 'Serializable' when 5 then 'Snapshot'
end as transaction_isolation_level,
request_session_id, resource_type, resource_subtype, request_mode,
request_type, request_status, request_owner_type,
case when resource_type = 'object' then object_name(resource_associated_entity_id)
when resource_type = 'database' then db_name(resource_associated_entity_id)
when resource_type in ('key','page') then
(select object_name(object_id) from sys.partitions
where hobt_id = resource_associated_entity_id)
else cast(resource_associated_entity_id as varchar(20))
end
from sys.dm_tran_locks dtl
left outer join sys.dm_exec_sessions des
on dtl.request_session_id = des.session_id
where request_session_id <> @.@.spid

On my 3Ghz, 512 MB machine running Express Edition (on a Media Center PC) I got two rows, one of which was the shared schema lock, the other was a bulk operation lock, likely in Tempdb trying to build these rows. Unless you are dropping and creating rows, this is unlikely to be a concern.

|||If you perform insert/update in a transaction that has isolation level READ UNCOMMITTED it will anyhow acquire X locks. I suppose your short transaction are read-only though.|||We all kind of forgot about that one huh? Good point :)|||

thanks everyone for your very helpful information.

|||sorry I should have mentioned that I'm only running queries

does "with (nolock)" absolutely guarantee no locks are taken?

I need to run a few short-running queries against a production system. I need to be absolutely certain that SS doesn't take out any locks on the table as a result.

Does "with (nolock)" absolutely guarantee this? I've read BOL on the topic and I understand isolation level read-uncommitted. But I want to validate that there's not any undocumented behavior in SS that might violate the documentation (which clearly states that no shared locks are issued).

Thanks!

You're talking two different things.

with (nolock) is a HINT. That means there is a possibility that SS will override it.

SET TRANSACTION ISOLATION LEVEL is a command. AFAIK, SS won't override this.

|||SQL Server honors the read uncommitted hint. It is however possible that you might get some error due to the dirty read behavior. The transaction isolation level is recommended if you do not want to specify hint on every table that you query and it is for the session. You can also set it in the tools option so it is always automatic. Note that this does affect behavior of any query since you will be reading uncommitted data i.e., perform dirty reads. In SQL Server 2005, you can use the READ COMMITTED SNAPSHOT option at the database level or the new snapshot isoaltion level which uses versioning mechanism to provide consistent view of data without taking locks.|||

The answer I am about to give is not likely to be pertinent to your question since you state short running, but just in case since you were so adament about NO LOCKS :) There is one tiny exception to the nolock/read uncommitted isolation level, in that it does take a shared schema lock so other users cannot drop/alter the table while your query is running. in 2005, run this:

create table testLocks
(
testLockId int,
bigValue char(8000) default (replicate('*',8000))
)

insert into testLocks(testLockId)
select 1
go 1000

set transaction isolation level read uncommitted
select * from testLocks
cross join testLocks as t2
-

Then on another connection run this (that query will return 1000000 16KB rows, so it will take a while :)

select login_name,
case des.transaction_isolation_level
when 0 then 'Unspecified' when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted' when 3 then 'Repeatable'
when 4 then 'Serializable' when 5 then 'Snapshot'
end as transaction_isolation_level,
request_session_id, resource_type, resource_subtype, request_mode,
request_type, request_status, request_owner_type,
case when resource_type = 'object' then object_name(resource_associated_entity_id)
when resource_type = 'database' then db_name(resource_associated_entity_id)
when resource_type in ('key','page') then
(select object_name(object_id) from sys.partitions
where hobt_id = resource_associated_entity_id)
else cast(resource_associated_entity_id as varchar(20))
end
from sys.dm_tran_locks dtl
left outer join sys.dm_exec_sessions des
on dtl.request_session_id = des.session_id
where request_session_id <> @.@.spid

On my 3Ghz, 512 MB machine running Express Edition (on a Media Center PC) I got two rows, one of which was the shared schema lock, the other was a bulk operation lock, likely in Tempdb trying to build these rows. Unless you are dropping and creating rows, this is unlikely to be a concern.

|||If you perform insert/update in a transaction that has isolation level READ UNCOMMITTED it will anyhow acquire X locks. I suppose your short transaction are read-only though.|||We all kind of forgot about that one huh? Good point :)|||

thanks everyone for your very helpful information.

|||sorry I should have mentioned that I'm only running queries