A question on Isolation level

Whenever the update statment is executed the patient table will be
locked even for the querying. Is there a way
where in the select locks the table so that the other transaction will
not even able to select.
If change the isolation level to repeatable read or higher it will not
allow update and insert by other transaction
but select will be allowed which I want to stop
SQL
--
BEGIN TRAN
SELECT * FROM PATIENT WHERE STATUS = 'A'
WAITFOR DELAY '00:00:10'
UPDATE PATIENT SET STATUS = 'I' WHERE STATUS = 'A'
WAITFOR DELAY '00:00:10'
COMMIT TRANI don't understand why you have the waitfor statements. My understanding of
how the lock manager works is that it will escalate the number of locks it
has to place based on the indexes in place and the perceived numbers of rows
it has to update.
So if you have an index in place on the status column and only a few rows of
a large table have a value of A row level locking should occur. If there are
no indexes or depending on the number of rows, it could be page level,
extent locks or a table lock.
I would update statsitics, evaluate how many rows are affected to see if you
can't change this behavior.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"shiju" <shiju.samuel@.gmail.com> wrote in message
news:1156937237.329228.258100@.m73g2000cwd.googlegroups.com...
> Whenever the update statment is executed the patient table will be
> locked even for the querying. Is there a way
> where in the select locks the table so that the other transaction will
> not even able to select.
> If change the isolation level to repeatable read or higher it will not
> allow update and insert by other transaction
> but select will be allowed which I want to stop
>
> SQL
> --
> BEGIN TRAN
> SELECT * FROM PATIENT WHERE STATUS = 'A'
> WAITFOR DELAY '00:00:10'
> UPDATE PATIENT SET STATUS = 'I' WHERE STATUS = 'A'
> WAITFOR DELAY '00:00:10'
> COMMIT TRAN
>|||On 30 Aug 2006 04:27:17 -0700, "shiju" <shiju.samuel@.gmail.com> wrote:
>Whenever the update statment is executed the patient table will be
>locked even for the querying. Is there a way
>where in the select locks the table so that the other transaction will
>not even able to select.
>If change the isolation level to repeatable read or higher it will not
>allow update and insert by other transaction
>but select will be allowed which I want to stop
try "select * from patient with (updlock) where status = 'A'"
>
>SQL
>--
>BEGIN TRAN
> SELECT * FROM PATIENT WHERE STATUS = 'A'
> WAITFOR DELAY '00:00:10'
> UPDATE PATIENT SET STATUS = 'I' WHERE STATUS = 'A'
> WAITFOR DELAY '00:00:10'
>COMMIT TRAN

A question on Isolation level

Whenever the update statment is executed the patient table will be
locked even for the querying. Is there a way
where in the select locks the table so that the other transaction will
not even able to select.
If change the isolation level to repeatable read or higher it will not
allow update and insert by other transaction
but select will be allowed which I want to stop
SQL
--
BEGIN TRAN
SELECT * FROM PATIENT WHERE STATUS = 'A'
WAITFOR DELAY '00:00:10'
UPDATE PATIENT SET STATUS = 'I' WHERE STATUS = 'A'
WAITFOR DELAY '00:00:10'
COMMIT TRANI don't understand why you have the waitfor statements. My understanding of
how the lock manager works is that it will escalate the number of locks it
has to place based on the indexes in place and the perceived numbers of rows
it has to update.
So if you have an index in place on the status column and only a few rows of
a large table have a value of A row level locking should occur. If there are
no indexes or depending on the number of rows, it could be page level,
extent locks or a table lock.
I would update statsitics, evaluate how many rows are affected to see if you
can't change this behavior.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"shiju" <shiju.samuel@.gmail.com> wrote in message
news:1156937237.329228.258100@.m73g2000cwd.googlegroups.com...
> Whenever the update statment is executed the patient table will be
> locked even for the querying. Is there a way
> where in the select locks the table so that the other transaction will
> not even able to select.
> If change the isolation level to repeatable read or higher it will not
> allow update and insert by other transaction
> but select will be allowed which I want to stop
>
> SQL
> --
> BEGIN TRAN
> SELECT * FROM PATIENT WHERE STATUS = 'A'
> WAITFOR DELAY '00:00:10'
> UPDATE PATIENT SET STATUS = 'I' WHERE STATUS = 'A'
> WAITFOR DELAY '00:00:10'
> COMMIT TRAN
>|||On 30 Aug 2006 04:27:17 -0700, "shiju" <shiju.samuel@.gmail.com> wrote:

>Whenever the update statment is executed the patient table will be
>locked even for the querying. Is there a way
>where in the select locks the table so that the other transaction will
>not even able to select.
>If change the isolation level to repeatable read or higher it will not
>allow update and insert by other transaction
>but select will be allowed which I want to stop
try "select * from patient with (updlock) where status = 'A'"

>
>SQL
>--
>BEGIN TRAN
> SELECT * FROM PATIENT WHERE STATUS = 'A'
> WAITFOR DELAY '00:00:10'
> UPDATE PATIENT SET STATUS = 'I' WHERE STATUS = 'A'
> WAITFOR DELAY '00:00:10'
>COMMIT TRAN

a question about Exec

Hi,

When I run the following command on sql server:

exec ('print "OK"')
go

This message appears:

Server: Msg 128, Level 15, State 1, Line 1
The name 'OK' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.

Why? Thanks.findu_2005@.yahoo.com (findu_2005@.yahoo.com) writes:

Quote:

Originally Posted by

When I run the following command on sql server:
>
exec ('print "OK"')
go
>
This message appears:
>
Server: Msg 128, Level 15, State 1, Line 1
The name 'OK' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
>
Why? Thanks.

Because when the setting QUOTED_IDENTIFIER is in effect, "" delmits
identifier. This permits you to use table names like Order Details, for
instance:

SELECT ... FROM "Order Details"

In the SQL Server world, we tend to use [] for this function, but "" is
what ANSI mandates.

QUOTED_IDENTIFIER is on by default in most contexts, but not when you
run from SQLCMD, OSQL or Enterprise Manager in SQL 2000. Or for that
matter all DB-Library applications.

When the setting is off, SQL Server reverts to the original behaviour
from 4.x days when you couls use both '' and "" to delimit strings.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Something like this will work

exec ('print ''OK''') . *Note: all are single quotes.

or you'll have to use SET QUOTED_IDENTIFIER OFF and then execute the
SQL.

Regards,
Thyagu.