Blocked tables

From time to time I get blocked tables in my database and application stope
working.
So I try next example:
declare @.n int
set @.n=50
while @.n>0
begin
SELECT * FROM table1 INNER JOIN table2...
set @.n=@.n-1
end
While this selects are working I try
in other query analyzer window to create an update on table2:
UPDATE table2 set column1='test'
and I get blocked tables.
I guess something similar is happening in my application.
How can I prevent this blocking?
regards,SIf you are happy with dirty reads you can do this
SELECT * FROM table1 with (nolock) INNER JOIN table2 with (nolock) ...
http://sqlservercode.blogspot.com/
"simon" wrote:

> From time to time I get blocked tables in my database and application sto
pe
> working.
> So I try next example:
> declare @.n int
> set @.n=50
> while @.n>0
> begin
> SELECT * FROM table1 INNER JOIN table2...
> set @.n=@.n-1
> end
> While this selects are working I try
> in other query analyzer window to create an update on table2:
> UPDATE table2 set column1='test'
> and I get blocked tables.
> I guess something similar is happening in my application.
> How can I prevent this blocking?
> regards,S
>
>|||Why the tables are blocked until I restart the sql server ?
I can't write nolock in each query. Is there some other way?
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:E17B8CBD-CF39-4857-8FEF-7AEBEE25375C@.microsoft.com...
> If you are happy with dirty reads you can do this
> SELECT * FROM table1 with (nolock) INNER JOIN table2 with (nolock) ...
>
> http://sqlservercode.blogspot.com/
> "simon" wrote:
>|||On Tue, 25 Oct 2005 15:18:31 +0200, simon wrote:

>Why the tables are blocked until I restart the sql server ?
>I can't write nolock in each query. Is there some other way?
Hi Simon,
It appears to me that there are two things wrong:
1. You have somehow set your transactions to an isolation leven that is
higher than the standard "READ COMMITTED" level. With read committed,
locks for data being read are released when the statement finishes. With
REPEATABLE READ and SERIALIZABLE, locks are held until the end of the
transaction.
2. You are starting transactions that you don't finish. That might be
because not each BEGIN TRANSACTION in your app is matched by either a
COMMIT TRANSACTION or a ROLLBACK TRANSACTION, or because you don't use
explicit transactions, but have the autocommit transaction mode switched
off using SET IMPLICIT_TRANSACTIONS ON (that means that transactions are
automatically started by SQL Server, but they still have to be ended by
an explicit COMMIT or ROLLBACK statement).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
I don't set any transaction.
I just open sqlQueryAnalyzer and put this code:
declare @.n int
set @.n=50
while @.n>0
begin
SELECT * FROM table1 INNER JOIN table2...
set @.n=@.n-1
end
And open other window and put this code:
UPDATE table2 set column1='test'
If I execute each statement separately, than both works. The first statement
takes about minute, the update one less than second.
If I execute the update statement while select is working than I get blocked
tables for infinite time.
Any idea?
regards,S
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5cbtl1d40ntshsauftgqtjf2m4ucp3el1r@.
4ax.com...
> On Tue, 25 Oct 2005 15:18:31 +0200, simon wrote:
>
> Hi Simon,
> It appears to me that there are two things wrong:
> 1. You have somehow set your transactions to an isolation leven that is
> higher than the standard "READ COMMITTED" level. With read committed,
> locks for data being read are released when the statement finishes. With
> REPEATABLE READ and SERIALIZABLE, locks are held until the end of the
> transaction.
> 2. You are starting transactions that you don't finish. That might be
> because not each BEGIN TRANSACTION in your app is matched by either a
> COMMIT TRANSACTION or a ROLLBACK TRANSACTION, or because you don't use
> explicit transactions, but have the autocommit transaction mode switched
> off using SET IMPLICIT_TRANSACTIONS ON (that means that transactions are
> automatically started by SQL Server, but they still have to be ended by
> an explicit COMMIT or ROLLBACK statement).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 26 Oct 2005 09:21:56 +0200, simon wrote:

>Hugo,
>I don't set any transaction.
>I just open sqlQueryAnalyzer and put this code:
>declare @.n int
>set @.n=50
>while @.n>0
>begin
> SELECT * FROM table1 INNER JOIN table2...
> set @.n=@.n-1
>end
>And open other window and put this code:
>UPDATE table2 set column1='test'
>
>If I execute each statement separately, than both works. The first statemen
t
>takes about minute, the update one less than second.
>If I execute the update statement while select is working than I get blocke
d
>tables for infinite time.
>Any idea?
Hi Simon,
Not now - I'll need more information.
Please post details about your tables: CREATE TABLE statements
(including all properties, constraints, indexes, etc) for the tables,
INSERT statements for the data in your tables. Also, post the complete
code that you are using, as the code above will only return a syntax
error.
Another thing to try: when both statements are running, open a third
window and execute
sp_lock
Post the results in a reply to this message.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)