Showing posts with label locks. Show all posts
Showing posts with label locks. Show all posts

Saturday, February 25, 2012

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

Saturday, February 11, 2012

A little advice about locks with SQL 2000

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:576062
Hi there,
I am hoping someone can give me a little advice about locks.
I have just finished reading a chapter about them in a reference book
and i am now more than when I started.
What I want to do is simple, i hope.
I have multiple users selecting from the same table, I am worried about
2 users getting the same result, which would be very bad, I just need
to find away to stop this from happening.
My set up is real simple.
The SP is below:
Basically A user will call this SP the first part brings back a row,
then updates that table and sets a field to 1, which is used in
selecting the initial row. Basically, I am worried that someone will
have selected the row and in between that persons SP updating the
table, another user come along and then get the same row, and then try
to update the same table. My program will then perform the same
functionon the identical data, which is bad.
I know that a lot of books, say this is unlikely, but my app is such
that this SP will be hit by hundreds of users, every second.
in an ideal world, a user would not be able to execute the SP, until it
has finished executing the last, but I still have to be able to process
hundreds of requests per second, without having massive bottle necks.
Any help would be much appreciated.
CREATE PROCEDURE [dbo].[Engine_Fetch_List]
AS
DECLARE @.client_id int
DECLARE @.url_id int
/*LOCK THIS TABLE UNTIL IT HAS BEEN UPDATED TO STOP ANYONE ELSE FROM
SELECTING IT*/
SELECT TOP 1 @.client_id = l.client_id, @.url_id = l.url_id
FROM dbo.tbl_client_fetch_list l
WHERE (l.updating = 0) AND (l.updated = 0)
GROUP BY l.client_id, l.url_id
ORDER BY l.client_id
/*SET THE UPDATING FLAG, TO STOP ANYONE ELSE FROM SELECTING THIS URL*/
UPDATE dbo.tbl_client_fetch_list SET updating = 1 WHERE client_id =
@.client_id AND url_id = @.url_id
/* REMOVE LOCK*/
GOChris
DECLARE @.pr INT
BEGIN TRANSACTION
SELECT @.pr =MAX(col) FROM Table WITH (UPDLOCK,HOLDLOCK)
UPDATE Table1 SET col =@.pr WHERE blabala
COMMIT TRANSACTION
I'd recommend you to search on interent an explanation posted by Brian
Selzer about those lock hints or please refer for the details in BOL.
"ChrisDangerfield" <chris@.dangerfieldbrothers.co.uk> wrote in message
news:1136639527.280129.256420@.g14g2000cwa.googlegroups.com...
> Hi there,
> I am hoping someone can give me a little advice about locks.
> I have just finished reading a chapter about them in a reference book
> and i am now more than when I started.
> What I want to do is simple, i hope.
> I have multiple users selecting from the same table, I am worried about
> 2 users getting the same result, which would be very bad, I just need
> to find away to stop this from happening.
> My set up is real simple.
> The SP is below:
> Basically A user will call this SP the first part brings back a row,
> then updates that table and sets a field to 1, which is used in
> selecting the initial row. Basically, I am worried that someone will
> have selected the row and in between that persons SP updating the
> table, another user come along and then get the same row, and then try
> to update the same table. My program will then perform the same
> functionon the identical data, which is bad.
> I know that a lot of books, say this is unlikely, but my app is such
> that this SP will be hit by hundreds of users, every second.
> in an ideal world, a user would not be able to execute the SP, until it
> has finished executing the last, but I still have to be able to process
> hundreds of requests per second, without having massive bottle necks.
> Any help would be much appreciated.
> CREATE PROCEDURE [dbo].[Engine_Fetch_List]
> AS
> DECLARE @.client_id int
> DECLARE @.url_id int
> /*LOCK THIS TABLE UNTIL IT HAS BEEN UPDATED TO STOP ANYONE ELSE FROM
> SELECTING IT*/
> SELECT TOP 1 @.client_id = l.client_id, @.url_id = l.url_id
> FROM dbo.tbl_client_fetch_list l
> WHERE (l.updating = 0) AND (l.updated = 0)
> GROUP BY l.client_id, l.url_id
> ORDER BY l.client_id
> /*SET THE UPDATING FLAG, TO STOP ANYONE ELSE FROM SELECTING THIS URL*/
> UPDATE dbo.tbl_client_fetch_list SET updating = 1 WHERE client_id =
> @.client_id AND url_id = @.url_id
> /* REMOVE LOCK*/
> GO
>|||Hello Chris,
Usually the best way is just to start with the UPDATE, if you used SQL 2005
you could also use the OUTPUT clause of the UPDATE.
This way in this example you don't need to worry about locks etc. ex:
CREATE PROCEDURE [dbo].[Engine_Fetch_List]
AS
DECLARE @.client_id int
DECLARE @.url_id int
/*LOCK THIS TABLE UNTIL IT HAS BEEN UPDATED TO STOP ANYONE ELSE FROM
SELECTING IT*/
/*SET THE UPDATING FLAG, TO STOP ANYONE ELSE FROM SELECTING THIS URL*/
SET ROWCOUNT 1 -- Only update one row.
UPDATE dbo.tbl_client_fetch_list SET
@.client_id = client_id -- Here you want to fecth the table PK to search
on that in any SELECTs below.
,@.url_id = url_id
,updating = 1
WHERE updating = 0 AND updated = 0
SET ROWCOUNT 0
-- Do any selects that you need here, you may (not always) want to open a
tran. before the update to hold the X-lock it took.
HTH
Tobias