sql local data: table

Showing posts with label table. Show all posts

Friday, March 30, 2012

Locking

It appears that when we issue an UPDATE statement on a table, it locks the
WHOLE table so that any other users can not complete SELECT queries on the
table (without using "WITH (nolock)" in the SELECT statement)
Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing an
update, so that maybe it takes longer, but it only locks 1 row or page at a
time?Mike
The optimiser escalates to a full table lock when it thinks it will be more
efficient way of processing the data than using page locks. There are a
number of things you can do if you think it should not be performing a table
lock.
Are your statistics up to date? If you don't regularly update statistics,
the optimiser is working with at least one hand tied behind it's back. If the
statistics are not up tp date it may be making incorrect choices.
Do you have suitable indexes? If you have indexes on the data you are
updating (especially a clustered index) it will be easier for the optimiser
to use page locks rather than table locks, if suitable.
It may be you are updating enough of the table that a table lock is the
correct option. In this case you can split up your updates into smaller
transactions, small enough that the optimiser uses page locks.
Hope this helps
John
"Mike" wrote:
> It appears that when we issue an UPDATE statement on a table, it locks the
> WHOLE table so that any other users can not complete SELECT queries on the
> table (without using "WITH (nolock)" in the SELECT statement)
> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing an
> update, so that maybe it takes longer, but it only locks 1 row or page at a
> time?
>
>|||Forgive my ignorance, but how do I get my statistics up to date? I am not
experienced in SQL Server administration.
I am using the pubs database in a new installation of SQL Server.
When I issue:
UPDATE authors set au_lname = 'a'
and then jump over to Enterprise Manager and right-click on "Current
Activity" under Management and select Refresh, then I go to Locks/Object and
pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
Lock Type "PAG".
If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
but less PAG locks.
(I inserted thousands more rows into authors than it comes with to give me
time to go to Ent. Mgr and select Refresh while the update is running)
There is a compound index on au_lname and au_fname.
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
> Mike
> The optimiser escalates to a full table lock when it thinks it will be
> more
> efficient way of processing the data than using page locks. There are a
> number of things you can do if you think it should not be performing a
> table
> lock.
> Are your statistics up to date? If you don't regularly update statistics,
> the optimiser is working with at least one hand tied behind it's back. If
> the
> statistics are not up tp date it may be making incorrect choices.
> Do you have suitable indexes? If you have indexes on the data you are
> updating (especially a clustered index) it will be easier for the
> optimiser
> to use page locks rather than table locks, if suitable.
> It may be you are updating enough of the table that a table lock is the
> correct option. In this case you can split up your updates into smaller
> transactions, small enough that the optimiser uses page locks.
> Hope this helps
> John
> "Mike" wrote:
>> It appears that when we issue an UPDATE statement on a table, it locks
>> the
>> WHOLE table so that any other users can not complete SELECT queries on
>> the
>> table (without using "WITH (nolock)" in the SELECT statement)
>> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
>> an
>> update, so that maybe it takes longer, but it only locks 1 row or page at
>> a
>> time?
>>|||I think you see the IX (intent exclusive) lock on the table.
That is not a real lock, it just tells the the engine that there is a execute
or update lock somewhere in the table.
Normally SQLServer will use row locks. The only way you get a real table
lock with this statement is if you have forbidden row and page locks to a
clustered index on the table. I doubt you have been playing with the
sp_indexoption, so that seems unlikely.
Joachim.
On Fri, 21 Jan 2005 11:28:34 -0500, "Mike W" <mikeotown@.nospam.msn.com>
wrote:
>Forgive my ignorance, but how do I get my statistics up to date? I am not
>experienced in SQL Server administration.
>I am using the pubs database in a new installation of SQL Server.
>When I issue:
>UPDATE authors set au_lname = 'a'
>and then jump over to Enterprise Manager and right-click on "Current
>Activity" under Management and select Refresh, then I go to Locks/Object and
>pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
>Lock Type "PAG".
>If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
>but less PAG locks.
>(I inserted thousands more rows into authors than it comes with to give me
>time to go to Ent. Mgr and select Refresh while the update is running)
>There is a compound index on au_lname and au_fname.
>"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
>message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
>> Mike
>> The optimiser escalates to a full table lock when it thinks it will be
>> more
>> efficient way of processing the data than using page locks. There are a
>> number of things you can do if you think it should not be performing a
>> table
>> lock.
>> Are your statistics up to date? If you don't regularly update statistics,
>> the optimiser is working with at least one hand tied behind it's back. If
>> the
>> statistics are not up tp date it may be making incorrect choices.
>> Do you have suitable indexes? If you have indexes on the data you are
>> updating (especially a clustered index) it will be easier for the
>> optimiser
>> to use page locks rather than table locks, if suitable.
>> It may be you are updating enough of the table that a table lock is the
>> correct option. In this case you can split up your updates into smaller
>> transactions, small enough that the optimiser uses page locks.
>> Hope this helps
>> John
>> "Mike" wrote:
>> It appears that when we issue an UPDATE statement on a table, it locks
>> the
>> WHOLE table so that any other users can not complete SELECT queries on
>> the
>> table (without using "WITH (nolock)" in the SELECT statement)
>> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
>> an
>> update, so that maybe it takes longer, but it only locks 1 row or page at
>> a
>> time?
>>
>
--
This post is free post; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.
Joachim Verhagen
http://www.xs4all.nl/~jcdverha/ (Science Jokes)|||Mike
You can update statistics automatically by enabling the auto update
statistics option. You can do this through enterprise manager. Right click on
the database you want to set the option for and choose properties. On the
option page of properties you can turn this option on and off. You can also
turn the option on or off through query analyser using sp_dboption. (See BOL
for details).
You can use dbcc show_statistics to see how up to date your statistics are.
Statistics also get updated when you rebuild a clustered index.
Hope this helps
John
"Mike W" wrote:
> Forgive my ignorance, but how do I get my statistics up to date? I am not
> experienced in SQL Server administration.
> I am using the pubs database in a new installation of SQL Server.
> When I issue:
> UPDATE authors set au_lname = 'a'
> and then jump over to Enterprise Manager and right-click on "Current
> Activity" under Management and select Refresh, then I go to Locks/Object and
> pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
> Lock Type "PAG".
> If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
> but less PAG locks.
> (I inserted thousands more rows into authors than it comes with to give me
> time to go to Ent. Mgr and select Refresh while the update is running)
> There is a compound index on au_lname and au_fname.
> "John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
> message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
> > Mike
> >
> > The optimiser escalates to a full table lock when it thinks it will be
> > more
> > efficient way of processing the data than using page locks. There are a
> > number of things you can do if you think it should not be performing a
> > table
> > lock.
> >
> > Are your statistics up to date? If you don't regularly update statistics,
> > the optimiser is working with at least one hand tied behind it's back. If
> > the
> > statistics are not up tp date it may be making incorrect choices.
> >
> > Do you have suitable indexes? If you have indexes on the data you are
> > updating (especially a clustered index) it will be easier for the
> > optimiser
> > to use page locks rather than table locks, if suitable.
> >
> > It may be you are updating enough of the table that a table lock is the
> > correct option. In this case you can split up your updates into smaller
> > transactions, small enough that the optimiser uses page locks.
> >
> > Hope this helps
> >
> > John
> >
> > "Mike" wrote:
> >
> >> It appears that when we issue an UPDATE statement on a table, it locks
> >> the
> >> WHOLE table so that any other users can not complete SELECT queries on
> >> the
> >> table (without using "WITH (nolock)" in the SELECT statement)
> >>
> >> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
> >> an
> >> update, so that maybe it takes longer, but it only locks 1 row or page at
> >> a
> >> time?
> >>
> >>
> >>
>
>

Locking

Can you change the locking granularity on a single table
at the database level? I want to set up a single table at
table level locking without the programmer having to send
a hint.
Thanks for the help,
JoelThis is a multi-part message in MIME format.
--=_NextPart_000_003D_01C354F8.393E6980
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Check out sp_indexoption in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Joel" <joel.griffin@.teijinmonofil.com> wrote in message =news:00b201c35518$a96b8d80$a101280a@.phx.gbl...
Can you change the locking granularity on a single table at the database level? I want to set up a single table at table level locking without the programmer having to send a hint.
Thanks for the help,
Joel
--=_NextPart_000_003D_01C354F8.393E6980
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Check out sp_indexoption in the =BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Joel" wrote in message news:00b201c35518$a9=6b8d80$a101280a@.phx.gbl...Can you change the locking granularity on a single table at the database =level? I want to set up a single table at table level locking =without the programmer having to send a hint.Thanks for the help,Joel

--=_NextPart_000_003D_01C354F8.393E6980--

locking

How does SS2000 lock by default for a select, update and delete statement.
Is it row level, page, table?
Thanks,
DanIt depends. Default is row level, but optimizer may escalate it to page
or table level locking if it see fit. You can force it to stay at row
level by using query hints. Check query hints in BOL
--
Eric Li
SQL DBA
MCDBA
Dan wrote:
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Eric is correct but I want to make one slight comment. SQL Server never
escalates to a page level lock from a row level. All escalation is always
to table level if it occurs. But it can choose to take out a page or table
level lock in the first place.
--
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:OMU0VgBSEHA.1392@.TK2MSFTNGP09.phx.gbl...
> It depends. Default is row level, but optimizer may escalate it to page
> or table level locking if it see fit. You can force it to stay at row
> level by using query hints. Check query hints in BOL
> --
> Eric Li
> SQL DBA
> MCDBA
> Dan wrote:
> > How does SS2000 lock by default for a select, update and delete
statement.
> > Is it row level, page, table?
> >
> > Thanks,
> >
> > Dan
> >
> >|||It appears to select the type of locking largely based on the number of
records you are processing. If you processing a large number of records,
taking out a page / table lock is more resource friendly than individual row
locks. Having said that, it's possible to force the use of a particular
locking mechanism with hints.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||If I also may add, SQL does not necessarily lock at the row level... One of
the recent improvements in the locking code for SQL is that the lock manager
takes into account how busy the object ( table for instance) has been
lately...
For instance, you are updating all of the rows in a table. If the table has
been very busy lately, SQL will choose a lower level lock ( page or row
level). This will cause your update to run more slowly ( because there will
be many more calls to the lock manager) , but overall concurrency will be
improved, since you will not be locking rows until right before you make the
update.
If the table has not been used lately, then you may get a whole table
lock... your update runs faster (due to the single lock manager call), and
no one else is hurt, since the table has not been used recently.
However in a busy multi user environment, lower level locks are most often
used...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Thanks Eric, Andrew, Peter and Wayne. I understand it better now and I'll do
some more reading.
Dan
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egQfZvJSEHA.1160@.TK2MSFTNGP09.phx.gbl...
> If I also may add, SQL does not necessarily lock at the row level... One
of
> the recent improvements in the locking code for SQL is that the lock
manager
> takes into account how busy the object ( table for instance) has been
> lately...
> For instance, you are updating all of the rows in a table. If the table
has
> been very busy lately, SQL will choose a lower level lock ( page or row
> level). This will cause your update to run more slowly ( because there
will
> be many more calls to the lock manager) , but overall concurrency will be
> improved, since you will not be locking rows until right before you make
the
> update.
> If the table has not been used lately, then you may get a whole table
> lock... your update runs faster (due to the single lock manager call), and
> no one else is hurt, since the table has not been used recently.
> However in a busy multi user environment, lower level locks are most often
> used...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> > How does SS2000 lock by default for a select, update and delete
statement.
> > Is it row level, page, table?
> >
> > Thanks,
> >
> > Dan
> >
> >
>

Locking

Hi all!
Can anyone point me to a doc that describes what the default locking levels
were across different versions of SQL, i.e. row vs. page vs. table, going
all the way back to version 4.2, and what the circumstances would have been
when SQL decides what to use over the other? Guess versions 4.2 and 6.x were
page-locking no matter what if I remember correctly, but want to make sure
before I tell my colleagues rubbish.
Thanks in advance,
Jan Van der Eecken
I don't have any documentation but you didn't have true row level locks
until version 7.0. So before that it was Page and after it is Row.
Andrew J. Kelly SQL MVP
"Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
> Hi all!
> Can anyone point me to a doc that describes what the default locking
> levels were across different versions of SQL, i.e. row vs. page vs. table,
> going all the way back to version 4.2, and what the circumstances would
> have been when SQL decides what to use over the other? Guess versions 4.2
> and 6.x were page-locking no matter what if I remember correctly, but want
> to make sure before I tell my colleagues rubbish.
> Thanks in advance,
> Jan Van der Eecken
>
|||Thanks Andrew. I was almost under the impression that it was page-level in
7.0 as well.
Cheers,
Jan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks
>until version 7.0. So before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>
|||6.5 added some possibility to get some type of row locks for INSERTs. Sorry to be vague, but it was
a long time ago. As I recall, you had to satisfy a number of conditions (probably what indexes you
had) in order to get some concurrency on the page for INSERT operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks until version 7.0. So
>before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>
|||Thanks, Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 6.5 added some possibility to get some type of row locks for INSERTs.
> Sorry to be vague, but it was a long time ago. As I recall, you had to
> satisfy a number of conditions (probably what indexes you had) in order to
> get some concurrency on the page for INSERT operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>

Locking

Wednesday, March 28, 2012

lock timeouts

I have a procedure running which is locking a table. I want to execute another one, knowing fullwell it wants to use the locked table. Can I go home and expect the 2nd proc. to run when the table becomes unlocked or will it time out while waiting and send an error.
-KThe procedure will wait indefinitely unless there is a deadlock or the LOCK_TIMEOUT setting has a value other than -1.

Lock table until transaction finish.

HOw do I write a statement to lock a table until my transactions are finish?

e.g...

declare @.LatestRecordid int
declare @.Value varchar(10)
set @.Value='just a value'

'Lock Syantax here

insert into table_to_be_lock (tablecolumn) values (@.value)
set @.LatestRecordid=(select ident_current('table_to_be_lock')+1
Update summarytable set LatestId=@.LatestRecordid, ItsValue=@.value

'Lock Syantax end

The reason I need to lock it is because at the second line of code, I am afraid another person has run the same query and inserted another record b4 my second line of code is runned, thus I'll get the wrong Recordid pointing to the wrong "ItsValue".

I am pretty sure there is a syantax for it...but I just can't seem to remember...can anybody help?Not that it will happen because SQl Server Default isolation level is "Read Commited". In other words , other user will see only written records and SQL will handle locks internally (shared, update,Row level, Table level ..etc.)|||Use SCOPE_IDENTITY instead of ident_current - you will have your id.

Anyway it is good idea to open transaction before your insert, check for erros during insert and updates (rollback if it needs) and commit transaction.|||okay....wil try to read on scope....

err..btw..this is abit out of the topic...

I was wondering,which is much faster and less taxing on cpu processing?

select ident_current('tablename') vs select max(tableidentitycolumn)

as both gets the same value. I can't test it out as I only have a small table here, can't tell the difference.

can someone actually really test it out at their environment ?

I'm guessing ident_current will be much faster, correct?|||Patrick, ur guess is correct, max will parse through each row in the table. I guess ident_current,@.@.identity is like a global variable which stores the last ident value.|||So many problems would be so much easier if developers used UniqueIdentifiers instead of GUIDs.

Sigh. :(|||hmm..what are the difference??
isit the ones that is being generated usually during replication??
read somewhere that those datatype are bigger in size and may cause slower indexes...|||They may be bigger in size, but you can use them in very creative SQL schemas and avoid much of the overhead of tracking and looking up identity values.

blindman

Lock table row

Hi, all. We've got a SQL Server 2000 database. We need to lock a row so no
one else can make any changes to it; however, we'd like to allow others to
be able to still have read permissions during the lock. How do we accomplish
this? Thanks.What are you trying to do?
SQL Server will handle the locks for you. Althou you can specify by
hints if you want NOLOCK or ROWLOCK and such things.
If you want to lock a row manually i think that what you are looking
for is to either add a table where you can store the locks with a FK
that points to a certain post (PK) in the table you want to lock. Or
maybe just create a bit column to specify wheter the row is "locked" or
not.|||If row-level permissions exist, I've never heard of them. But, you could do
this
CREATE TRIGGER DontChangeThisRow ON [YourTable]
INSTEAD OF UPDATE
AS
BEGIN
UPDATE [YourTable] SET col1 = i.col1, col2 = i.col2 /*repeat as necessary */
FROM inserted i INNER JOIN [YourTable] y
ON y.primary_key_column = i.primary_key_column
WHERE
i.primary_key_column <> 'key value for row you want to preserve'
END
Here is a complete sample:
DROP TABLE yourtable
GO
CREATE TABLE [YourTable] (col1 int not null, col2 char(1))
GO
ALTER TABLE [YourTable] ADD CONSTRAINT PK_YourTable_col1
PRIMARY KEY (col1)
GO
INSERT INTO [YourTable] VALUES (1,'a')
INSERT INTO [YourTable] VALUES (2,'b')
INSERT INTO [YourTable] VALUES (3,'c')
INSERT INTO [YourTable] VALUES (4,'d')
INSERT INTO [YourTable] VALUES (5,'e')
CREATE TRIGGER DontChangeThisRow ON [YourTable]
INSTEAD OF UPDATE
AS
BEGIN
UPDATE [YourTable] SET col1 = i.col1, col2 = i.col2
FROM inserted i INNER JOIN [YourTable] y
ON y.col1 = i.col1
WHERE
i.col1 <> 1 --key value for row you want to preserve
END
UPDATE [YourTable] SET col2 = 'z' WHERE col1 = 4
SELECT col1, col2 FROM [YourTable]
UPDATE [YourTable] SET col2 = 'h' WHERE col1 = 1
SELECT col1, col2 FROM [YourTable]
"curious_Lee" wrote:

> Hi, all. We've got a SQL Server 2000 database. We need to lock a row so no
> one else can make any changes to it; however, we'd like to allow others to
> be able to still have read permissions during the lock. How do we accompli
sh
> this? Thanks.
>
>|||> Hi, all. We've got a SQL Server 2000 database. We need to lock a row so no
> one else can make any changes to it; however, we'd like to allow others to
> be able to still have read permissions during the lock. How do we
> accomplish this? Thanks.
Sounds like optimistic concurrency, no?|||BEGIN TRAN
SELECT @.par =MAX(blblbl) FROM Table WITH(UPDLOCK,HOLDLOCK) WHERE......
UPDATE Table SET col=@.par ......
COMMIT TRAN
It ensures that the data has not changed since you last read it.
"curious_Lee" <oneworld95_NOSPAM@.yahoo.com> wrote in message
news:Oj6K1SqJGHA.1848@.TK2MSFTNGP12.phx.gbl...
> Hi, all. We've got a SQL Server 2000 database. We need to lock a row so no
> one else can make any changes to it; however, we'd like to allow others to
> be able to still have read permissions during the lock. How do we
> accomplish this? Thanks.
>|||Thanks, Patrik, Mark, Aaron, and Uri, for the great solutions. I think
either a transaction or an optimistic concurrency might do the trick.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uGO0W0vJGHA.3696@.TK2MSFTNGP15.phx.gbl...
> BEGIN TRAN
> SELECT @.par =MAX(blblbl) FROM Table WITH(UPDLOCK,HOLDLOCK) WHERE......
> UPDATE Table SET col=@.par ......
> COMMIT TRAN
>
> It ensures that the data has not changed since you last read it.
>
>
> "curious_Lee" <oneworld95_NOSPAM@.yahoo.com> wrote in message
> news:Oj6K1SqJGHA.1848@.TK2MSFTNGP12.phx.gbl...
>

LOCK Table necessary in stored procedure?

Hi,

I have some questions about locking tables in stored procedures. I got some excellent tips from my last post, but since it's sort of a different problem I figured I'd post it separately.

I have a large log table I need to do manual, periodic clean-up process on, which basically is purging unneccessary log-entries. The idea is to select out the 1-3% I need to another table, drop the old table, and rename the new table to the old one.

The problem is that I most likely will need to lock the entire table while I do all the clean-up stuff. If a client manages to add things inbetween this is going on, I could end up loosing data.

The table looks like this:
Logid PK
LogTypeID -- what category
LogValue --
LogTime -- when it occurred

My imaginary stored procedure looks something like this:

CREATE PROCEDURE ShrinkDB AS
-- 1)
"lock table log" -- do I have to do something like this?

-- 2)
select * into log_keep FROM Log where
(
logtypeid <> 2020 AND -- activity played
logtypeid <> 5020 AND -- database connected
-- ...etc et.... about 10 different things I don't need to keep
or logtime > dateadd(d, -1, getdate()) -- keep everything from last 24 hours
)
-- 3)
drop table log

-- 4)
EXEC sp_rename 'log_keep', 'log'
GO

I'm not able to figure out wether I need to run some sort of "Lock" command or not, or if everything inside a stored procedure automatically is locked. If so, I shouldn't worry about loosing any data I guess??

Hopefully it works that way, but if not I assume I'll run into these two problems:

- If a client logs immediately after the Selecet, could data be logged AFTER the select, but BEFORE the drop table-command? In which case I guess I would loose data?

- Immediately after the drop table log in step 3, there's no table named 'log' in my database. 'Log' will be "created" when I run step 4. This means I could perhaps loose data since the client for a brief moment can't log data to the 'log' table?

Hopefully someone can clearify this for me, I've read the documentation, but I don't feel too sure on this subject... :-)Hi,

Why drop the table? why not delete the data you have just taken, then you will never lose any data|||...simply because it takes such an insane amount of time. A regular "delete from log where logtypeId = stuff-I-don't-need" takes forever. :( The complete post is here, if you want the details: http://www.dbforums.com/showthread.php?threadid=979910|||You can open a transaction, and then do SELECT * FROM TBL WITH (TABLOCK) WHERE 1=2 as you first statement. Then, instead of moving 1-3% of data to a different permanent table, you can put it into a temptable (whether # or @., doesn't matter,) then truncate the original (instead of dropping it,) and putting the data from your temptable back into the original.|||Even truncate takes quite a few seconds, allthough I guess that's related to some locking issues and not the truncate process in it self.

The method involving dropping the table takes about 3-5 seconds, and is much faster than select-away-and-truncate. It sort of works, the problem with that solution is that PK, indexes, rights and what not are gone too. This can of course be re-created with a number of SQL sentences, but it's not exactly ideal......

But I cannot understand why deleting takes so much time? I'm assuming the cause of this problem is that about 50 clients are constantly logging into the table while I'm deleting.

I'm not an expert sql-stored procedure maker, anyone who whould take a crack at setting up something for me with locking (and whatever else) that would work? Would help me a lot!! :-)|||I can send you a contract, and upon signing you'll never have to deal with it again :cool:sql

lock table for queries with query WAITFOR

Hi all.
How can I lock table with longtime query?why?

this message is too short.|||I am in need|||I am in need of a large type breakfast, a night cap and 4 more hours in the bed.

What I was aking you, is why do you want to lock a table for the length of the execution of a query. The default locking scheme in sql server is adequate for 99% percent of situations.|||perhaps OP is looking for a way to force timeouts or deadlocks as part of a test plan?|||I am in need of a large type breakfast, a night cap and 4 more hours in the bed.

What I was aking you, is why do you want to lock a table for the length of the execution of a query. The default locking scheme in sql server is adequate for 99% percent of situations.

I am in the need for an eye-opener|||perhaps OP is looking for a way to force timeouts or deadlocks as part of a test plan?

yeah, I do not hand out loaded guns unless there is justification.

I am in the need for an eye-opener

in a couple of hours we can all get back down to the serious business of poisoning our minds and bodies.|||In a couple of HOURS?

Damn, it's gotta be 5:00 somewhere|||Damn, it's gotta be 5:00 somewhere

Hallelujah! :D

Lock SQL table using asp.net

Hi,all:
This problem almost drives me crazy, hope I can get some hints from you guyz!!!
Ok, here is the situation:
I wanna only one users 2 modify the data(update) from my page each time, and if at the same time, there are some other users connecting my database through .aspx page, they can only browse the data until the first users finish updating.
It seems I need to implement locking the database, but I am not sure how I am gonna do that using asp.net!!!
Thanx in advance!This sort of locking is not a great idea. What happens if the first editing user goes to lunch, or looses his connection, or just looses interest?

Better, create a system that will test to ensure that an impending update is in fact updating the same data as it originally got to do the editing on.|||Hi,douglas:
Thanx a lot for ur reply! But I still have some question about this,
would you mind telling me that if the sql server has this kind of functionality?
And if it has, how about the time-out for this function?
Thanx in advance!|||the problem is, you will not have an open connection to the database between page posts. Locking is not viable for an asp.net app.|||Hi, Douglas:
Thanx for the reply, I have one more question :)
Is it possible that I can excecute the "Lock Table..." sql statement in asp.net?
if it's possible, is there any limit time for that "lock"?
Thanx a lot!
Cheers|||Have a look at "isolation levels" and, in you specific case "serializable". But you really need to understand the ramifications otherwise you'll bring the database and/or your application to its knees.

LOCK REQUEST TIME OUT PERIOD EXCEEDED

Got a VB6 program creating records in an SQL 2000 DB. I have 5 client
machines creating 100,000 records each in the same table at the same time.
On a couple of machine I get the error "LOCK REQUEST TIME OUT PERIOD
EXCEEDED". This happened maybe 4 times. Would this be due to a network or
hardware limitation or is it a SQL DB factor maybe ?
The SQL server is only a P4 with 512 ram.
Clients machines vary greatly and run 2000pro and XPpro.
Thanks for any pointers.
Scott.It seems the table is locked by one process for a longer
time, and this has forced other process to throw the
error 1222.
The LOCK_TIMEOUT setting allows an application to set a
maximum time that a statement waits on a blocked
resource. When a statement has waited longer than the
LOCK_TIMEOUT setting, the blocked statement is canceled
automatically, and error message 1222 "Lock request time-
out period exceeded" is returned to the application.
I think if after every 1000 inserts, if you commit the
transaction, then the resource will not be locked for a
longer duration.
regds,
Shrikant Patil,
MCDBA
>--Original Message--
>Got a VB6 program creating records in an SQL 2000 DB. I
have 5 client
>machines creating 100,000 records each in the same table
at the same time.
>On a couple of machine I get the error "LOCK REQUEST
TIME OUT PERIOD
>EXCEEDED". This happened maybe 4 times. Would this be
due to a network or
>hardware limitation or is it a SQL DB factor maybe ?
>The SQL server is only a P4 with 512 ram.
>Clients machines vary greatly and run 2000pro and XPpro.
>Thanks for any pointers.
>Scott.
>
>.
>|||i see.
so the error was recived because one clinet/process was ready to commit 1000
records while another clinet/process was in the process of commiting - hence
the error.
the chances that a clinet machine would recive this with our software in
practice is very remotei guess as they would probablty never create that
many records at the same time to the same table. even if they did the RETRY
option seems to deal with it well anyway.
Is there other locking methods that can be employed rather than the 1000
batch one ?
Thanks for your time
Scott

LOCK REQUEST TIME OUT PERIOD EXCEEDED

It seems the table is locked by one process for a longer
time, and this has forced other process to throw the
error 1222.
The LOCK_TIMEOUT setting allows an application to set a
maximum time that a statement waits on a blocked
resource. When a statement has waited longer than the
LOCK_TIMEOUT setting, the blocked statement is canceled
automatically, and error message 1222 "Lock request time-
out period exceeded" is returned to the application.
I think if after every 1000 inserts, if you commit the
transaction, then the resource will not be locked for a
longer duration.
regds,
Shrikant Patil,
MCDBA

>--Original Message--
>Got a VB6 program creating records in an SQL 2000 DB. I
have 5 client
>machines creating 100,000 records each in the same table
at the same time.
>On a couple of machine I get the error "LOCK REQUEST
TIME OUT PERIOD
>EXCEEDED". This happened maybe 4 times. Would this be
due to a network or
>hardware limitation or is it a SQL DB factor maybe ?
>The SQL server is only a P4 with 512 ram.
>Clients machines vary greatly and run 2000pro and XPpro.
>Thanks for any pointers.
>Scott.
>
>.
>
i see.
so the error was recived because one clinet/process was ready to commit 1000
records while another clinet/process was in the process of commiting - hence
the error.
the chances that a clinet machine would recive this with our software in
practice is very remotei guess as they would probablty never create that
many records at the same time to the same table. even if they did the RETRY
option seems to deal with it well anyway.
Is there other locking methods that can be employed rather than the 1000
batch one ?
Thanks for your time
Scott

Monday, March 26, 2012

lock question

I need help with next problem:
Exist table my_rows with next columns:
id - int identity
name - varchar
is_locked - bit
I need create store procedure which will return every time diferent
row for every request from different processes.
the pseudo code for the procedure :
1 select statement is :
select @.id = selct top 1 id from my_rows where is_locked =0
2 update my_rows set is_locked = 1 where id=@.id
3 return @.id
In different words : if in same time I call this procedure from 2
different connections, it will return 2 different record.
I also want that first call to procedure will not generate lock error
for second call in same time(just second call will wait for finish
first , it is ok.)
ThanksHi
You select and update statement should be contained within one transaction
and you can use the UPDLOCK hint on the select statement to stop others
returning that value
CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
SET NOCOUNT ON
BEGIN TRANSACTION
SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
-- Error checking
UPDATE my_rows SET is_locked = 1 WHERE id=@.id
-- Error checking
COMMIT TRANSACTION
RETURN
Alternatively you could use
UPDATE my_rows
SET is_locked = 1
WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
but you would not know the ID that was updated.
Using this type of locking can potentially cause a bottleneck and poor
performance.
John
"is_vlb50@.hotmail.com" wrote:
> I need help with next problem:
> Exist table my_rows with next columns:
> id - int identity
> name - varchar
> is_locked - bit
> I need create store procedure which will return every time diferent
> row for every request from different processes.
> the pseudo code for the procedure :
> 1 select statement is :
> select @.id = selct top 1 id from my_rows where is_locked =0
> 2 update my_rows set is_locked = 1 where id=@.id
> 3 return @.id
> In different words : if in same time I call this procedure from 2
> different connections, it will return 2 different record.
> I also want that first call to procedure will not generate lock error
> for second call in same time(just second call will wait for finish
> first , it is ok.)
> Thanks
>|||On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> You select and update statement should be contained within one transaction
> and you can use the UPDLOCK hint on the select statement to stop others
> returning that value
> CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> SET NOCOUNT ON
> BEGIN TRANSACTION
> SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
> -- Error checking
> UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> -- Error checking
> COMMIT TRANSACTION
> RETURN
> Alternatively you could use
> UPDATE my_rows
> SET is_locked = 1
> WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
> but you would not know the ID that was updated.
> Using this type of locking can potentially cause a bottleneck and poor
> performance.
> John
>
> "is_vl...@.hotmail.com" wrote:
> > I need help with next problem:
> > Exist table my_rows with next columns:
> > id - int identity
> > name - varchar
> > is_locked - bit
> > I need create store procedure which will return every time diferent
> > row for every request from different processes.
> > the pseudo code for the procedure :
> > 1 select statement is :
> > select @.id = selct top 1 id from my_rows where is_locked =0
> > 2 update my_rows set is_locked = 1 where id=@.id
> > 3 return @.id
> > In different words : if in same time I call this procedure from 2
> > different connections, it will return 2 different record.
> > I also want that first call to procedure will not generate lock error
> > for second call in same time(just second call will wait for finish
> > first , it is ok.)
> > Thanks- Hide quoted text -
> - Show quoted text -
You wroute:
SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
=0 )
-- Error checking
Could you explain what is reason for "Error checking " and what I can
do.
If some body in same time call same procedure , I can get error ?
Thanks|||Hi
Read http://www.sommarskog.se/error-handling-I.html and
http://www.sommarskog.se/error-handling-II.html which gives you a very good
idea of what to do. If you follow Erlands advice you would also have checked
for errors when you commit the transaction and have code to rollback.
John
"is_vlb50@.hotmail.com" wrote:
> On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> >
> > You select and update statement should be contained within one transaction
> > and you can use the UPDLOCK hint on the select statement to stop others
> > returning that value
> >
> > CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> > SET NOCOUNT ON
> > BEGIN TRANSACTION
> > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
> > -- Error checking
> > UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> > -- Error checking
> > COMMIT TRANSACTION
> > RETURN
> >
> > Alternatively you could use
> >
> > UPDATE my_rows
> > SET is_locked = 1
> > WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
> >
> > but you would not know the ID that was updated.
> >
> > Using this type of locking can potentially cause a bottleneck and poor
> > performance.
> >
> > John
> >
> >
> >
> > "is_vl...@.hotmail.com" wrote:
> > > I need help with next problem:
> > > Exist table my_rows with next columns:
> > > id - int identity
> > > name - varchar
> > > is_locked - bit
> >
> > > I need create store procedure which will return every time diferent
> > > row for every request from different processes.
> >
> > > the pseudo code for the procedure :
> > > 1 select statement is :
> > > select @.id = selct top 1 id from my_rows where is_locked =0
> > > 2 update my_rows set is_locked = 1 where id=@.id
> > > 3 return @.id
> >
> > > In different words : if in same time I call this procedure from 2
> > > different connections, it will return 2 different record.
> > > I also want that first call to procedure will not generate lock error
> > > for second call in same time(just second call will wait for finish
> > > first , it is ok.)
> > > Thanks- Hide quoted text -
> >
> > - Show quoted text -
> You wroute:
> SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
> =0 )
> -- Error checking
> Could you explain what is reason for "Error checking " and what I can
> do.
> If some body in same time call same procedure , I can get error ?
> Thanks
>
>
>|||On Sep 26, 6:02 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> Readhttp://www.sommarskog.se/error-handling-I.htmlandhttp://www.sommarskog.se/error-handling-II.htmlwhich gives you a very good
> idea of what to do. If you follow Erlands advice you would also have checked
> for errors when you commit the transaction and have code to rollback.
> John
>
> "is_vl...@.hotmail.com" wrote:
> > On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > > Hi
> > > You select and update statement should be contained within one transaction
> > > and you can use the UPDLOCK hint on the select statement to stop others
> > > returning that value
> > > CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> > > SET NOCOUNT ON
> > > BEGIN TRANSACTION
> > > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
> > > -- Error checking
> > > UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> > > -- Error checking
> > > COMMIT TRANSACTION
> > > RETURN
> > > Alternatively you could use
> > > UPDATE my_rows
> > > SET is_locked = 1
> > > WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
> > > but you would not know the ID that was updated.
> > > Using this type of locking can potentially cause a bottleneck and poor
> > > performance.
> > > John
> > > "is_vl...@.hotmail.com" wrote:
> > > > I need help with next problem:
> > > > Exist table my_rows with next columns:
> > > > id - int identity
> > > > name - varchar
> > > > is_locked - bit
> > > > I need create store procedure which will return every time diferent
> > > > row for every request from different processes.
> > > > the pseudo code for the procedure :
> > > > 1 select statement is :
> > > > select @.id = selct top 1 id from my_rows where is_locked =0
> > > > 2 update my_rows set is_locked = 1 where id=@.id
> > > > 3 return @.id
> > > > In different words : if in same time I call this procedure from 2
> > > > different connections, it will return 2 different record.
> > > > I also want that first call to procedure will not generate lock error
> > > > for second call in same time(just second call will wait for finish
> > > > first , it is ok.)
> > > > Thanks- Hide quoted text -
> > > - Show quoted text -
> > You wroute:
> > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
> > =0 )
> > -- Error checking
> > Could you explain what is reason for "Error checking " and what I can
> > do.
> > If some body in same time call same procedure , I can get error ?
> > Thanks- Hide quoted text -
> - Show quoted text -
Thanks,
it was very helpful. One last question:
if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
call in store procedure can raise error because first call in first SP
still running (still locks the record) or it just will receive another
row?
Thanks|||> if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> call in store procedure can raise error because first call in first SP
> still running (still locks the record) or it just will receive another
> row?
TOP 1 can give you *any* row. If the row decided for is locked already by an incompatible lock, then
you will be blocked. If you want some other row, you might wan to check out the READPAST hint.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<is_vlb50@.hotmail.com> wrote in message news:1190831328.803233.199140@.19g2000hsx.googlegroups.com...
> On Sep 26, 6:02 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
>> Hi
>> Readhttp://www.sommarskog.se/error-handling-I.htmlandhttp://www.sommarskog.se/error-handling-II.htmlwhich
>> gives you a very good
>> idea of what to do. If you follow Erlands advice you would also have checked
>> for errors when you commit the transaction and have code to rollback.
>> John
>>
>> "is_vl...@.hotmail.com" wrote:
>> > On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
>> > > Hi
>> > > You select and update statement should be contained within one transaction
>> > > and you can use the UPDLOCK hint on the select statement to stop others
>> > > returning that value
>> > > CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
>> > > SET NOCOUNT ON
>> > > BEGIN TRANSACTION
>> > > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
>> > > -- Error checking
>> > > UPDATE my_rows SET is_locked = 1 WHERE id=@.id
>> > > -- Error checking
>> > > COMMIT TRANSACTION
>> > > RETURN
>> > > Alternatively you could use
>> > > UPDATE my_rows
>> > > SET is_locked = 1
>> > > WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
>> > > but you would not know the ID that was updated.
>> > > Using this type of locking can potentially cause a bottleneck and poor
>> > > performance.
>> > > John
>> > > "is_vl...@.hotmail.com" wrote:
>> > > > I need help with next problem:
>> > > > Exist table my_rows with next columns:
>> > > > id - int identity
>> > > > name - varchar
>> > > > is_locked - bit
>> > > > I need create store procedure which will return every time diferent
>> > > > row for every request from different processes.
>> > > > the pseudo code for the procedure :
>> > > > 1 select statement is :
>> > > > select @.id = selct top 1 id from my_rows where is_locked =0
>> > > > 2 update my_rows set is_locked = 1 where id=@.id
>> > > > 3 return @.id
>> > > > In different words : if in same time I call this procedure from 2
>> > > > different connections, it will return 2 different record.
>> > > > I also want that first call to procedure will not generate lock error
>> > > > for second call in same time(just second call will wait for finish
>> > > > first , it is ok.)
>> > > > Thanks- Hide quoted text -
>> > > - Show quoted text -
>> > You wroute:
>> > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
>> > =0 )
>> > -- Error checking
>> > Could you explain what is reason for "Error checking " and what I can
>> > do.
>> > If some body in same time call same procedure , I can get error ?
>> > Thanks- Hide quoted text -
>> - Show quoted text -
> Thanks,
> it was very helpful. One last question:
> if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> call in store procedure can raise error because first call in first SP
> still running (still locks the record) or it just will receive another
> row?
> Thanks
>|||On Sep 26, 8:40 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> > call in store procedure can raise error because first call in first SP
> > still running (still locks the record) or it just will receive another
> > row?
> TOP 1 can give you *any* row. If the row decided for is locked already by an incompatible lock, then
> you will be blocked. If you want some other row, you might wan to check out the READPAST hint.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> <is_vl...@.hotmail.com> wrote in messagenews:1190831328.803233.199140@.19g2000hsx.googlegroups.com...
> > On Sep 26, 6:02 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> >> Hi
> >> Readhttp://www.sommarskog.se/error-handling-I.htmlandhttp://www.sommarsko...
> >> gives you a very good
> >> idea of what to do. If you follow Erlands advice you would also have checked
> >> for errors when you commit the transaction and have code to rollback.
> >> John
> >> "is_vl...@.hotmail.com" wrote:
> >> > On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> >> > > Hi
> >> > > You select and update statement should be contained within one transaction
> >> > > and you can use the UPDLOCK hint on the select statement to stop others
> >> > > returning that value
> >> > > CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> >> > > SET NOCOUNT ON
> >> > > BEGIN TRANSACTION
> >> > > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
> >> > > -- Error checking
> >> > > UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> >> > > -- Error checking
> >> > > COMMIT TRANSACTION
> >> > > RETURN
> >> > > Alternatively you could use
> >> > > UPDATE my_rows
> >> > > SET is_locked = 1
> >> > > WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
> >> > > but you would not know the ID that was updated.
> >> > > Using this type of locking can potentially cause a bottleneck and poor
> >> > > performance.
> >> > > John
> >> > > "is_vl...@.hotmail.com" wrote:
> >> > > > I need help with next problem:
> >> > > > Exist table my_rows with next columns:
> >> > > > id - int identity
> >> > > > name - varchar
> >> > > > is_locked - bit
> >> > > > I need create store procedure which will return every time diferent
> >> > > > row for every request from different processes.
> >> > > > the pseudo code for the procedure :
> >> > > > 1 select statement is :
> >> > > > select @.id = selct top 1 id from my_rows where is_locked =0
> >> > > > 2 update my_rows set is_locked = 1 where id=@.id
> >> > > > 3 return @.id
> >> > > > In different words : if in same time I call this procedure from 2
> >> > > > different connections, it will return 2 different record.
> >> > > > I also want that first call to procedure will not generate lock error
> >> > > > for second call in same time(just second call will wait for finish
> >> > > > first , it is ok.)
> >> > > > Thanks- Hide quoted text -
> >> > > - Show quoted text -
> >> > You wroute:
> >> > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
> >> > =0 )
> >> > -- Error checking
> >> > Could you explain what is reason for "Error checking " and what I can
> >> > do.
> >> > If some body in same time call same procedure , I can get error ?
> >> > Thanks- Hide quoted text -
> >> - Show quoted text -
> > Thanks,
> > it was very helpful. One last question:
> > if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> > call in store procedure can raise error because first call in first SP
> > still running (still locks the record) or it just will receive another
> > row?
> > Thanks- Hide quoted text -
> - Show quoted text -
In this case suggestion of John is not valid, because as I described
at start post,I need solution which will in every call to SP in same
time will return back a different row without any block.
Thanks|||On Wed, 26 Sep 2007 18:50:43 -0000, is_vlb50@.hotmail.com wrote:
>In this case suggestion of John is not valid, because as I described
>at start post,I need solution which will in every call to SP in same
>time will return back a different row without any block.
Hi is_vlb50,
As Tibor already mentioned, the READPAST hint can help you achieve what
you need. You'll find all the details in Books Online.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Sep 27, 12:46 am, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Wed, 26 Sep 2007 18:50:43 -0000, is_vl...@.hotmail.com wrote:
> >In this case suggestion of John is not valid, because as I described
> >at start post,I need solution which will in every call to SP in same
> >time will return back a different row without any block.
> Hi is_vlb50,
> As Tibor already mentioned, the READPAST hint can help you achieve what
> you need. You'll find all the details in Books Online.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
the readpast applied only to update, delete, and writetext records.so
if i use
SELECT @.id = ( SELECT top 1 id FROM my_rows (readpast) WHERE
is_locked
=0 ) it will return same records to both simultanously call.
may be i can use it with UPDLOCK hint?
thanks|||Hi
You would be blocked whilst the second transaction finishes, hence the
possibility of a bottleneck. Why do you need to re-use the ids?
John
"is_vlb50@.hotmail.com" wrote:
> On Sep 26, 8:40 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > > if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> > > call in store procedure can raise error because first call in first SP
> > > still running (still locks the record) or it just will receive another
> > > row?
> >
> > TOP 1 can give you *any* row. If the row decided for is locked already by an incompatible lock, then
> > you will be blocked. If you want some other row, you might wan to check out the READPAST hint.
> >
> > --
> > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> >
> >
> >
> > <is_vl...@.hotmail.com> wrote in messagenews:1190831328.803233.199140@.19g2000hsx.googlegroups.com...
> > > On Sep 26, 6:02 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > >> Hi
> >
> > >> Readhttp://www.sommarskog.se/error-handling-I.htmlandhttp://www.sommarsko...
> > >> gives you a very good
> > >> idea of what to do. If you follow Erlands advice you would also have checked
> > >> for errors when you commit the transaction and have code to rollback.
> >
> > >> John
> >
> > >> "is_vl...@.hotmail.com" wrote:
> > >> > On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > >> > > Hi
> >
> > >> > > You select and update statement should be contained within one transaction
> > >> > > and you can use the UPDLOCK hint on the select statement to stop others
> > >> > > returning that value
> >
> > >> > > CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> > >> > > SET NOCOUNT ON
> > >> > > BEGIN TRANSACTION
> > >> > > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
> > >> > > -- Error checking
> > >> > > UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> > >> > > -- Error checking
> > >> > > COMMIT TRANSACTION
> > >> > > RETURN
> >
> > >> > > Alternatively you could use
> >
> > >> > > UPDATE my_rows
> > >> > > SET is_locked = 1
> > >> > > WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
> >
> > >> > > but you would not know the ID that was updated.
> >
> > >> > > Using this type of locking can potentially cause a bottleneck and poor
> > >> > > performance.
> >
> > >> > > John
> >
> > >> > > "is_vl...@.hotmail.com" wrote:
> > >> > > > I need help with next problem:
> > >> > > > Exist table my_rows with next columns:
> > >> > > > id - int identity
> > >> > > > name - varchar
> > >> > > > is_locked - bit
> >
> > >> > > > I need create store procedure which will return every time diferent
> > >> > > > row for every request from different processes.
> >
> > >> > > > the pseudo code for the procedure :
> > >> > > > 1 select statement is :
> > >> > > > select @.id = selct top 1 id from my_rows where is_locked =0
> > >> > > > 2 update my_rows set is_locked = 1 where id=@.id
> > >> > > > 3 return @.id
> >
> > >> > > > In different words : if in same time I call this procedure from 2
> > >> > > > different connections, it will return 2 different record.
> > >> > > > I also want that first call to procedure will not generate lock error
> > >> > > > for second call in same time(just second call will wait for finish
> > >> > > > first , it is ok.)
> > >> > > > Thanks- Hide quoted text -
> >
> > >> > > - Show quoted text -
> >
> > >> > You wroute:
> > >> > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
> > >> > =0 )
> > >> > -- Error checking
> >
> > >> > Could you explain what is reason for "Error checking " and what I can
> > >> > do.
> > >> > If some body in same time call same procedure , I can get error ?
> > >> > Thanks- Hide quoted text -
> >
> > >> - Show quoted text -
> >
> > > Thanks,
> > > it was very helpful. One last question:
> > > if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> > > call in store procedure can raise error because first call in first SP
> > > still running (still locks the record) or it just will receive another
> > > row?
> > > Thanks- Hide quoted text -
> >
> > - Show quoted text -
> In this case suggestion of John is not valid, because as I described
> at start post,I need solution which will in every call to SP in same
> time will return back a different row without any block.
> Thanks
>|||On Wed, 26 Sep 2007 23:38:57 -0700, is_vlb50@.hotmail.com wrote:
>On Sep 27, 12:46 am, Hugo Kornelis
><h...@.perFact.REMOVETHIS.info.INVALID> wrote:
>> On Wed, 26 Sep 2007 18:50:43 -0000, is_vl...@.hotmail.com wrote:
>> >In this case suggestion of John is not valid, because as I described
>> >at start post,I need solution which will in every call to SP in same
>> >time will return back a different row without any block.
>> Hi is_vlb50,
>> As Tibor already mentioned, the READPAST hint can help you achieve what
>> you need. You'll find all the details in Books Online.
>> --
>> Hugo Kornelis, SQL Server MVP
>> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
>the readpast applied only to update, delete, and writetext records.so
>if i use
>SELECT @.id = ( SELECT top 1 id FROM my_rows (readpast) WHERE
>is_locked
>=0 ) it will return same records to both simultanously call.
>may be i can use it with UPDLOCK hint?
>thanks
>
Hi is_vlb50,
Yes, I thought John Bell already covered that.
First, you do a SELECT with UPDLOCK to make sure an exclusive lock is
acquired right away (to prevent two readers getting the same number),
*and* with READPAST to allow it to skip locked rows.
Then (in the same transaction), you do the update. Since you've already
got an exclusive lock, this will neven block or deadlock.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Sep 28, 10:13 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Wed, 26 Sep 2007 23:38:57 -0700, is_vl...@.hotmail.com wrote:
> >On Sep 27, 12:46 am, Hugo Kornelis
> ><h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> >> On Wed, 26 Sep 2007 18:50:43 -0000, is_vl...@.hotmail.com wrote:
> >> >In this case suggestion of John is not valid, because as I described
> >> >at start post,I need solution which will in every call to SP in same
> >> >time will return back a different row without any block.
> >> Hi is_vlb50,
> >> As Tibor already mentioned, the READPAST hint can help you achieve what
> >> you need. You'll find all the details in Books Online.
> >> --
> >> Hugo Kornelis, SQL Server MVP
> >> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
> >the readpast applied only to update, delete, and writetext records.so
> >if i use
> >SELECT @.id = ( SELECT top 1 id FROM my_rows (readpast) WHERE
> >is_locked
> >=0 ) it will return same records to both simultanously call.
> >may be i can use it with UPDLOCK hint?
> >thanks
> Hi is_vlb50,
> Yes, I thought John Bell already covered that.
> First, you do a SELECT with UPDLOCK to make sure an exclusive lock is
> acquired right away (to prevent two readers getting the same number),
> *and* with READPAST to allow it to skip locked rows.
> Then (in the same transaction), you do the update. Since you've already
> got an exclusive lock, this will neven block or deadlock.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -
> - Show quoted text -
just for confirm final solution:
CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
SET NOCOUNT ON
BEGIN TRANSACTION
SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) (READPAST) WHERE
is_locked =0 )
-- Error checking
UPDATE my_rows SET is_locked = 1 WHERE id=@.id
-- Error checking
COMMIT TRANSACTION
RETURN
Thanks|||Hi
From BOL:
WITH ( < table_hint > [ ,...n ] )
Specifies one or more table hints. For more information about table hints,
see FROM.
John
"is_vlb50@.hotmail.com" wrote:
> On Sep 28, 10:13 pm, Hugo Kornelis
> <h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> > On Wed, 26 Sep 2007 23:38:57 -0700, is_vl...@.hotmail.com wrote:
> > >On Sep 27, 12:46 am, Hugo Kornelis
> > ><h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> > >> On Wed, 26 Sep 2007 18:50:43 -0000, is_vl...@.hotmail.com wrote:
> > >> >In this case suggestion of John is not valid, because as I described
> > >> >at start post,I need solution which will in every call to SP in same
> > >> >time will return back a different row without any block.
> >
> > >> Hi is_vlb50,
> >
> > >> As Tibor already mentioned, the READPAST hint can help you achieve what
> > >> you need. You'll find all the details in Books Online.
> >
> > >> --
> > >> Hugo Kornelis, SQL Server MVP
> > >> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
> > >the readpast applied only to update, delete, and writetext records.so
> > >if i use
> > >SELECT @.id = ( SELECT top 1 id FROM my_rows (readpast) WHERE
> > >is_locked
> > >=0 ) it will return same records to both simultanously call.
> > >may be i can use it with UPDLOCK hint?
> > >thanks
> >
> > Hi is_vlb50,
> >
> > Yes, I thought John Bell already covered that.
> >
> > First, you do a SELECT with UPDLOCK to make sure an exclusive lock is
> > acquired right away (to prevent two readers getting the same number),
> > *and* with READPAST to allow it to skip locked rows.
> >
> > Then (in the same transaction), you do the update. Since you've already
> > got an exclusive lock, this will neven block or deadlock.
> >
> > --
> > Hugo Kornelis, SQL Server MVP
> > My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -
> >
> > - Show quoted text -
> just for confirm final solution:
> CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> SET NOCOUNT ON
> BEGIN TRANSACTION
> SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) (READPAST) WHERE
> is_locked =0 )
> -- Error checking
> UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> -- Error checking
> COMMIT TRANSACTION
> RETURN
> Thanks
>

Lock Query

How do you find what user is locking what row in what table in what database?On Thu, 27 Oct 2005 13:17:01 -0700, CLM
<CLM@.discussions.microsoft.com> wrote:
>How do you find what user is locking what row in what table in what database?
exec sp_who2 is a start.
I don't know of any way to map to individual rows, though it would be
nice!
What do you need it for?
J.|||CLM wrote:
> How do you find what user is locking what row in what table in what
> database?
Might not be a row. Could be a page or the entire table.
You can run sp_lock to a get a list of locks on the server. Turn the
dboid into a database name using db_name(dbid). Turn the ObjId into a
name using object_name(ObjId) from the correct database. Turn the spid
into a user by selecting the spid from master..sysprocesses.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I had some informix guys ask me. They can find out exactly what row is being
locked which I know has been useful on the production informix side of the
world. I figured it would be even easier on the Sql Server side. You can
find a lot from sp_lock but not which row...
"jxstern" wrote:
> On Thu, 27 Oct 2005 13:17:01 -0700, CLM
> <CLM@.discussions.microsoft.com> wrote:
> >How do you find what user is locking what row in what table in what database?
> exec sp_who2 is a start.
> I don't know of any way to map to individual rows, though it would be
> nice!
> What do you need it for?
> J.
>|||David Gugick wrote:
> CLM wrote:
>> How do you find what user is locking what row in what table in what
>> database?
> Might not be a row. Could be a page or the entire table.
> You can run sp_lock to a get a list of locks on the server. Turn the
> dboid into a database name using db_name(dbid). Turn the ObjId into a
> name using object_name(ObjId) from the correct database. Turn the spid
> into a user by selecting the spid from master..sysprocesses.
You can also use DBCC OPENTRAN and fn_get_sql to get information about
what is running for the spid that is blocking.
See if this page helps:
http://support.microsoft.com/default.aspx?scid=kb;en-us;224453
--
David Gugick
Quest Software
www.imceda.com
www.quest.comsql

Lock Query

How do you find what user is locking what row in what table in what database
?On Thu, 27 Oct 2005 13:17:01 -0700, CLM
<CLM@.discussions.microsoft.com> wrote:[vbcol=seagreen]
>How do you find what user is locking what row in what table in what database?[/vbco
l]
exec sp_who2 is a start.
I don't know of any way to map to individual rows, though it would be
nice!
What do you need it for?
J.|||CLM wrote:
> How do you find what user is locking what row in what table in what
> database?
Might not be a row. Could be a page or the entire table.
You can run sp_lock to a get a list of locks on the server. Turn the
dboid into a database name using db_name(dbid). Turn the ObjId into a
name using object_name(ObjId) from the correct database. Turn the spid
into a user by selecting the spid from master..sysprocesses.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I had some informix guys ask me. They can find out exactly what row is bein
g
locked which I know has been useful on the production informix side of the
world. I figured it would be even easier on the Sql Server side. You can
find a lot from sp_lock but not which row...
"jxstern" wrote:

> On Thu, 27 Oct 2005 13:17:01 -0700, CLM
> <CLM@.discussions.microsoft.com> wrote:
> exec sp_who2 is a start.
> I don't know of any way to map to individual rows, though it would be
> nice!
> What do you need it for?
> J.
>|||David Gugick wrote:
> CLM wrote:
> Might not be a row. Could be a page or the entire table.
> You can run sp_lock to a get a list of locks on the server. Turn the
> dboid into a database name using db_name(dbid). Turn the ObjId into a
> name using object_name(ObjId) from the correct database. Turn the spid
> into a user by selecting the spid from master..sysprocesses.
You can also use DBCC OPENTRAN and fn_get_sql to get information about
what is running for the spid that is blocking.
See if this page helps:
http://support.microsoft.com/defaul...kb;en-us;224453
David Gugick
Quest Software
www.imceda.com
www.quest.com

Lock Query

How do you find what user is locking what row in what table in what database?
On Thu, 27 Oct 2005 13:17:01 -0700, CLM
<CLM@.discussions.microsoft.com> wrote:
>How do you find what user is locking what row in what table in what database?
exec sp_who2 is a start.
I don't know of any way to map to individual rows, though it would be
nice!
What do you need it for?
J.
|||CLM wrote:
> How do you find what user is locking what row in what table in what
> database?
Might not be a row. Could be a page or the entire table.
You can run sp_lock to a get a list of locks on the server. Turn the
dboid into a database name using db_name(dbid). Turn the ObjId into a
name using object_name(ObjId) from the correct database. Turn the spid
into a user by selecting the spid from master..sysprocesses.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||I had some informix guys ask me. They can find out exactly what row is being
locked which I know has been useful on the production informix side of the
world. I figured it would be even easier on the Sql Server side. You can
find a lot from sp_lock but not which row...
"jxstern" wrote:

> On Thu, 27 Oct 2005 13:17:01 -0700, CLM
> <CLM@.discussions.microsoft.com> wrote:
> exec sp_who2 is a start.
> I don't know of any way to map to individual rows, though it would be
> nice!
> What do you need it for?
> J.
>
|||David Gugick wrote:
> CLM wrote:
> Might not be a row. Could be a page or the entire table.
> You can run sp_lock to a get a list of locks on the server. Turn the
> dboid into a database name using db_name(dbid). Turn the ObjId into a
> name using object_name(ObjId) from the correct database. Turn the spid
> into a user by selecting the spid from master..sysprocesses.
You can also use DBCC OPENTRAN and fn_get_sql to get information about
what is running for the spid that is blocking.
See if this page helps:
http://support.microsoft.com/default...b;en-us;224453
David Gugick
Quest Software
www.imceda.com
www.quest.com

Lock on system table

Hi all,
One of my stored procedure is creating around 7 #tables.
This procedure comes out after 5 mins with error
1222 'Lock request time-out period exceeded'.
My @.@.lock_timeout is -1.
Analysis showed that this stored procedures holds numerous
shared and exclusive locks on sysobjects, sysindexes and
syscolumns on tempdb. It blocks all other processes and
nobody can do anything.
Can anybody suggests why this could be happening and way
out?
Thanks in advance
Himanshu JaniHi Himanshu ,
If your stored procedure uses SELECT ..INTO to create and populate the
temporary tables this can cause locking problems as it will take out a lock
on the system tables to create the temporary table, but will hold this lock
for the duration of the insert. You can solve this problem by creating the
temporary table explicitly with either CREATE TABLE or with SELECT ..
INTO... WHERE 1=0. You then have to insert the data into the temporary table
with a normal insert. If the table creation happens inside a transaction the
locks will be taken for the duration of the transaction, so it makes sense
to create the temporary tables outside a long running transaction. If you
use SQL Server 2000 you can also use table variables instead of temporary
tables, table variables do not participate in transactions, and so you won't
have any locks on the system tables in tempdb.
--
Jacco Schalkwijk
SQL Server MVP
"Himanshu Jani" <himanshu@.ocwen.co.in> wrote in message
news:0a0301c38c94$a10602c0$a401280a@.phx.gbl...
> Hi all,
> One of my stored procedure is creating around 7 #tables.
> This procedure comes out after 5 mins with error
> 1222 'Lock request time-out period exceeded'.
> My @.@.lock_timeout is -1.
> Analysis showed that this stored procedures holds numerous
> shared and exclusive locks on sysobjects, sysindexes and
> syscolumns on tempdb. It blocks all other processes and
> nobody can do anything.
> Can anybody suggests why this could be happening and way
> out?
> Thanks in advance
> Himanshu Jani|||It seems to me that SELECT INTO is just a single command
that combines both the CREATE TABLE and INSERT statements
(with the benefit of minimumally logging the INSERT).
This means that the X-lock(s) on the system tables in
tempdb is held just for the duration of the CREATE and not
for the duration of the entire SELECT INTO statement.
On SQL Server 2000, SP3, I just ran 'SELECT INTO' into a
#temp table from a 12 million record table and was able to
simultaneously run and complete a second SELECT INTO into
another #temp table while the first SELECT INTO was still
running.
Thoughts?
Thanks, -- Brian
>--Original Message--
>Hi Himanshu ,
>If your stored procedure uses SELECT ..INTO to create and
populate the
>temporary tables this can cause locking problems as it
will take out a lock
>on the system tables to create the temporary table, but
will hold this lock
>for the duration of the insert. You can solve this
problem by creating the
>temporary table explicitly with either CREATE TABLE or
with SELECT ..
>INTO... WHERE 1=0. You then have to insert the data into
the temporary table
>with a normal insert. If the table creation happens
inside a transaction the
>locks will be taken for the duration of the transaction,
so it makes sense
>to create the temporary tables outside a long running
transaction. If you
>use SQL Server 2000 you can also use table variables
instead of temporary
>tables, table variables do not participate in
transactions, and so you won't
>have any locks on the system tables in tempdb.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Himanshu Jani" <himanshu@.ocwen.co.in> wrote in message
>news:0a0301c38c94$a10602c0$a401280a@.phx.gbl...
>> Hi all,
>> One of my stored procedure is creating around 7 #tables.
>> This procedure comes out after 5 mins with error
>> 1222 'Lock request time-out period exceeded'.
>> My @.@.lock_timeout is -1.
>> Analysis showed that this stored procedures holds
numerous
>> shared and exclusive locks on sysobjects, sysindexes and
>> syscolumns on tempdb. It blocks all other processes and
>> nobody can do anything.
>> Can anybody suggests why this could be happening and way
>> out?
>> Thanks in advance
>> Himanshu Jani
>
>.
>

lock on a record?

Just wondering if there is a way to put a lock on a record? For example I
have the table below and a stored procedure reads the records in. Another
procedure increments the value in column 2 and then writes a new record with
this incremented value plus a bunch of other user interactions. The problem
is if a second user performs the operations before the first user is
finished, this incremented value gets thrown off. This is a .net web
application.
*******************************
* pri key * string * int * int *
*******************************
* 1 * abc001 * 3 * 6 *
* 2 * cde 002 * 5 * 9 *
user 1 gets cde 002 and with write back abc 003, note the increment.
user 2 also gets cde 002 as the latest record and also writes back abc 003.
this is incorrect. User 2 should write back abc 004.
If user 2 waited until user 1 was finish, this would take care of the proble
m.
Thanks,
--
Paul G
Software engineer.Errors in data concurrency will always be a issue for develoepr (especially
in WebApp as ong as the db-server will tell you that the row updated and you
have to reload the data, Like in SQL Server 2005)
Try reading this to be prepared for impementation.
http://msdn2.microsoft.com/library/y8fyz6xy(en-us,vs.80).aspx
http://msdn.microsoft.com/msdnmag/i.../09/DataPoints/
HTH, Jens Suessmeyer.
"Paul" <Paul@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A1112DF0-41C1-4DB7-8F8B-6AF601C877CB@.microsoft.com...
> Just wondering if there is a way to put a lock on a record? For example I
> have the table below and a stored procedure reads the records in. Another
> procedure increments the value in column 2 and then writes a new record
> with
> this incremented value plus a bunch of other user interactions. The
> problem
> is if a second user performs the operations before the first user is
> finished, this incremented value gets thrown off. This is a .net web
> application.
> *******************************
> * pri key * string * int * int *
> *******************************
> * 1 * abc001 * 3 * 6 *
> * 2 * cde 002 * 5 * 9 *
> user 1 gets cde 002 and with write back abc 003, note the increment.
> user 2 also gets cde 002 as the latest record and also writes back abc
> 003.
> this is incorrect. User 2 should write back abc 004.
> If user 2 waited until user 1 was finish, this would take care of the
> problem.
> Thanks,
> --
> Paul G
> Software engineer.|||Yep this does seem like it would be a common issue. Will take a look at the
article.
"Jens Sü?meyer" wrote:

> Errors in data concurrency will always be a issue for develoepr (especiall
y
> in WebApp as ong as the db-server will tell you that the row updated and y
ou
> have to reload the data, Like in SQL Server 2005)
> Try reading this to be prepared for impementation.
> http://msdn2.microsoft.com/library/y8fyz6xy(en-us,vs.80).aspx
> http://msdn.microsoft.com/msdnmag/i.../09/DataPoints/
>
> HTH, Jens Suessmeyer.
> "Paul" <Paul@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:A1112DF0-41C1-4DB7-8F8B-6AF601C877CB@.microsoft.com...
>
>sql

Friday, March 23, 2012

lock info

hi,
from Activity Monitor, i found that there's a process (suspended) holding
over a thousand locks on a table named tbl_BusinessRecordDetailed.
i have 2 questions:
#1 - is there a way to find out which row it's locking?
#2 - what exactly is causing the locks? since the mode is S, which means
it's read only'
much appreciated.
from activity monitor:
resouce_type is all KEY
request_mode is all S
request_type is all LOCK
request_status is all GRANT
resouce_lock_partition is all 0
request_reference_count is all 0
request_exec_context_id is all 0
request_request_id is all 0
request_lifetime is all 1
request_owner_type is all TRANSACTION
but resource_description are pretty much all different, some examples:
1:210617
1:289260
(8800ee1b2515)
(b000d565e6d8)
(af008d6eefc0)
below is the table definition and the indexes:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tbl_BusinessRecordDetailed](
[recordID] [int] IDENTITY(1,1) NOT NULL,
[rowVersion] [timestamp] NOT NULL,
[name] [varchar](200) NULL,
[address] [varchar](200) NULL,
[crossstreet] [varchar](200) NULL,
[neighborhood] [varchar](200) NULL,
[borough] [varchar](200) NULL,
[city] [varchar](50) NULL,
[zip] [varchar](20) NULL,
[state] [varchar](2) NULL,
[telephone] [varchar](200) NULL,
[fax] [varchar](10) NULL,
[url] [varchar](500) NULL,
[bookingUrl] [varchar](500) NULL,
[bus] [varchar](200) NULL,
[subway] [varchar](300) NULL,
[description] [varchar](max) NULL,
[hours] [varchar](2000) NULL,
[admission] [varchar](2000) NULL,
[otherinfo] [varchar](1000) NULL,
[specialDirection] [varchar](max) NULL,
[sortOrder] [int] NOT NULL CONSTRAINT [DF__tbl_Busin__sortO__7AB
C33CD]
DEFAULT ((0)),
[dateCreated] [datetime] NOT NULL CONSTRAINT
[DF__tbl_Busin__dateC__7BB05806] DEFAULT (getdate()),
[copyright] [varchar](100) NULL,
[mappedVarchar200_1] [varchar](200) NULL,
[mappedVarchar200_2] [varchar](200) NULL,
[mappedVarchar200_3] [varchar](200) NULL,
[mappedVarchar200_4] [varchar](200) NULL,
[mappedVarchar2000_1] [varchar](2000) NULL,
[mappedVarchar2000_2] [varchar](2000) NULL,
[mappedVarcharMax_1] [varchar](max) NULL,
[dateModified] [datetime] NULL,
[email] [varchar](50) NULL,
CONSTRAINT [PK__tbl_BusinessReco__79C80F94] PRIMARY KEY CLUSTERED
(
[recordID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id =
OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name =
N'idx_biz_datecreated')
CREATE NONCLUSTERED INDEX [idx_biz_datecreated] ON
[dbo].[tbl_BusinessRecordDetailed]
(
[dateCreated] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id =
OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name =
N'idx_biz_datemodifed')
CREATE NONCLUSTERED INDEX [idx_biz_datemodifed] ON
[dbo].[tbl_BusinessRecordDetailed]
(
[dateModified] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]Hi
Your lock is a shared lock so it will not be stopping other processes from
reading this information see
http://msdn2.microsoft.com/en-us/library/aa213039(sql.80).aspx
and http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx
Using DBCC INPUTBUFFER on the SID for the process should indicate the last
statement issued by the client.
John
"tin" wrote:

> hi,
> from Activity Monitor, i found that there's a process (suspended) holding
> over a thousand locks on a table named tbl_BusinessRecordDetailed.
> i have 2 questions:
> #1 - is there a way to find out which row it's locking?
> #2 - what exactly is causing the locks? since the mode is S, which means
> it's read only'
> much appreciated.
>
> from activity monitor:
> resouce_type is all KEY
> request_mode is all S
> request_type is all LOCK
> request_status is all GRANT
> resouce_lock_partition is all 0
> request_reference_count is all 0
> request_exec_context_id is all 0
> request_request_id is all 0
> request_lifetime is all 1
> request_owner_type is all TRANSACTION
> but resource_description are pretty much all different, some examples:
> 1:210617
>
> 1:289260
>
> (8800ee1b2515)
>
> (b000d565e6d8)
>
> (af008d6eefc0)
>
>
>
> below is the table definition and the indexes:
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
> OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND type in (N'U'
))
> BEGIN
> CREATE TABLE [dbo].[tbl_BusinessRecordDetailed](
> [recordID] [int] IDENTITY(1,1) NOT NULL,
> [rowVersion] [timestamp] NOT NULL,
> [name] [varchar](200) NULL,
> [address] [varchar](200) NULL,
> [crossstreet] [varchar](200) NULL,
> [neighborhood] [varchar](200) NULL,
> [borough] [varchar](200) NULL,
> [city] [varchar](50) NULL,
> [zip] [varchar](20) NULL,
> [state] [varchar](2) NULL,
> [telephone] [varchar](200) NULL,
> [fax] [varchar](10) NULL,
> [url] [varchar](500) NULL,
> [bookingUrl] [varchar](500) NULL,
> [bus] [varchar](200) NULL,
> [subway] [varchar](300) NULL,
> [description] [varchar](max) NULL,
> [hours] [varchar](2000) NULL,
> [admission] [varchar](2000) NULL,
> [otherinfo] [varchar](1000) NULL,
> [specialDirection] [varchar](max) NULL,
> [sortOrder] [int] NOT NULL CONSTRAINT [DF__tbl_Busin__sortO__
7ABC33CD]
> DEFAULT ((0)),
> [dateCreated] [datetime] NOT NULL CONSTRAINT
> [DF__tbl_Busin__dateC__7BB05806] DEFAULT (getdate()),
> [copyright] [varchar](100) NULL,
> [mappedVarchar200_1] [varchar](200) NULL,
> [mappedVarchar200_2] [varchar](200) NULL,
> [mappedVarchar200_3] [varchar](200) NULL,
> [mappedVarchar200_4] [varchar](200) NULL,
> [mappedVarchar2000_1] [varchar](2000) NULL,
> [mappedVarchar2000_2] [varchar](2000) NULL,
> [mappedVarcharMax_1] [varchar](max) NULL,
> [dateModified] [datetime] NULL,
> [email] [varchar](50) NULL,
> CONSTRAINT [PK__tbl_BusinessReco__79C80F94] PRIMARY KEY CLUSTERED
> (
> [recordID] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> END
> GO
> IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id =
> OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name =
> N'idx_biz_datecreated')
> CREATE NONCLUSTERED INDEX [idx_biz_datecreated] ON
> [dbo].[tbl_BusinessRecordDetailed]
> (
> [dateCreated] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> GO
> IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id =
> OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name =
> N'idx_biz_datemodifed')
> CREATE NONCLUSTERED INDEX [idx_biz_datemodifed] ON
> [dbo].[tbl_BusinessRecordDetailed]
> (
> [dateModified] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
>
>|||hmm, actually, there have been "somthing" that blocking some records in that
table that other process can't update them. as simple as an update like
"update tbl_businessrecorddetailed set name = 'whatever' where recordid =
123" would just hang. just certain records are like that while most others
are fine. but after restarting the db service, it's fine. but the problem
would return after a while.
since the process is suspended, can i just kill it as a quick fix for now?
but i need to find out the cause going forward, any ideas'
thanks.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Your lock is a shared lock so it will not be stopping other processes from
> reading this information see
> http://msdn2.microsoft.com/en-us/library/aa213039(sql.80).aspx
> and http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx
> Using DBCC INPUTBUFFER on the SID for the process should indicate the last
> statement issued by the client.
>
> John
> "tin" wrote:
>|||Hi
sp_who2 has a blk column that may show what spid is blocking a process. You
may also want to look at http://support.microsoft.com/kb/271509 and run the
sp_blocker_pss80 procedure.
John
"tin" wrote:
[vbcol=seagreen]
> hmm, actually, there have been "somthing" that blocking some records in th
at
> table that other process can't update them. as simple as an update like
> "update tbl_businessrecorddetailed set name = 'whatever' where recordid =
> 123" would just hang. just certain records are like that while most other
s
> are fine. but after restarting the db service, it's fine. but the proble
m
> would return after a while.
> since the process is suspended, can i just kill it as a quick fix for now?
> but i need to find out the cause going forward, any ideas'
> thanks.
>
>
> "John Bell" wrote:
>

Friday, March 30, 2012

Locking

Locking

locking

Locking

Locking

Wednesday, March 28, 2012

lock timeouts

Lock table until transaction finish.

Lock table row

LOCK Table necessary in stored procedure?

lock table for queries with query WAITFOR

Lock SQL table using asp.net

LOCK REQUEST TIME OUT PERIOD EXCEEDED

LOCK REQUEST TIME OUT PERIOD EXCEEDED

Monday, March 26, 2012

lock question

Lock Query

Lock Query

Lock Query

Lock on system table

lock on a record?

Friday, March 23, 2012

lock info

sql local data

Blog Archive

About Me