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

Lock Issue

I've got a 2000 server (2G usable RAM) where we just
installed SP2. Immediately after installation I began
getting the below error:
The SQL Server cannot obtain a LOCK resource at this time.
Rerun your statement when there are fewer active users or
ask the system administrator to check the SQL Server lock
and memory configuration.
Error: 1204, Severity: 19, State: 1
I've got the default configuration setting for locks of 0
and nothing has changed on this server that I know except
for SP2. Furthermore, I've been monitoring the total
number of locks at any given time and I'm not seeing
anything that should cause problems. The most locks I've
seen is about 40,000.
The last time we got this error msg in the error log, I
had two index defrag jobs going and there was bulk load
and/or delete job running.
Is this simply memory related? Is there anything with SP2
that might cause this (especially if it uses extra memory
for index defrags)?
Any insight would be much appreciated?!Hi Curt.
What is your configuration for locks? Run this to find out:
exec sp_configure 'locks'
Perhaps it got set to 40000 maximum during SP2 installation somehow?
Regards,
Greg Linwood
SQL Server MVP
"CurtM" <cndmoyer@.hotmail.com> wrote in message
news:127e01c391b1$29933ce0$a001280a@.phx.gbl...
> I've got a 2000 server (2G usable RAM) where we just
> installed SP2. Immediately after installation I began
> getting the below error:
> The SQL Server cannot obtain a LOCK resource at this time.
> Rerun your statement when there are fewer active users or
> ask the system administrator to check the SQL Server lock
> and memory configuration.
> Error: 1204, Severity: 19, State: 1
> I've got the default configuration setting for locks of 0
> and nothing has changed on this server that I know except
> for SP2. Furthermore, I've been monitoring the total
> number of locks at any given time and I'm not seeing
> anything that should cause problems. The most locks I've
> seen is about 40,000.
> The last time we got this error msg in the error log, I
> had two index defrag jobs going and there was bulk load
> and/or delete job running.
> Is this simply memory related? Is there anything with SP2
> that might cause this (especially if it uses extra memory
> for index defrags)?
> Any insight would be much appreciated?!
>|||It is set to 0 for run and config value (which I am sure
it was pre-SP2 as well).
>--Original Message--
>Hi Curt.
>What is your configuration for locks? Run this to find
out:
>exec sp_configure 'locks'
>Perhaps it got set to 40000 maximum during SP2
installation somehow?
>Regards,
>Greg Linwood
>SQL Server MVP
>"CurtM" <cndmoyer@.hotmail.com> wrote in message
>news:127e01c391b1$29933ce0$a001280a@.phx.gbl...
>> I've got a 2000 server (2G usable RAM) where we just
>> installed SP2. Immediately after installation I began
>> getting the below error:
>> The SQL Server cannot obtain a LOCK resource at this
time.
>> Rerun your statement when there are fewer active users
or
>> ask the system administrator to check the SQL Server
lock
>> and memory configuration.
>> Error: 1204, Severity: 19, State: 1
>> I've got the default configuration setting for locks of
0
>> and nothing has changed on this server that I know
except
>> for SP2. Furthermore, I've been monitoring the total
>> number of locks at any given time and I'm not seeing
>> anything that should cause problems. The most locks
I've
>> seen is about 40,000.
>> The last time we got this error msg in the error log, I
>> had two index defrag jobs going and there was bulk load
>> and/or delete job running.
>> Is this simply memory related? Is there anything with
SP2
>> that might cause this (especially if it uses extra
memory
>> for index defrags)?
>> Any insight would be much appreciated?!
>
>.
>|||At this point it is just memory issue and you may have a lot of low
granularity locks such as RID or Key. You may want to escalate if possible
using hints for the problematic query
"CurtM" <cndmoyer@.hotmail.com> wrote in message
news:08c201c391c9$77b35000$a301280a@.phx.gbl...
> It is set to 0 for run and config value (which I am sure
> it was pre-SP2 as well).
> >--Original Message--
> >Hi Curt.
> >
> >What is your configuration for locks? Run this to find
> out:
> >
> >exec sp_configure 'locks'
> >
> >Perhaps it got set to 40000 maximum during SP2
> installation somehow?
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >"CurtM" <cndmoyer@.hotmail.com> wrote in message
> >news:127e01c391b1$29933ce0$a001280a@.phx.gbl...
> >> I've got a 2000 server (2G usable RAM) where we just
> >> installed SP2. Immediately after installation I began
> >> getting the below error:
> >>
> >> The SQL Server cannot obtain a LOCK resource at this
> time.
> >> Rerun your statement when there are fewer active users
> or
> >> ask the system administrator to check the SQL Server
> lock
> >> and memory configuration.
> >>
> >> Error: 1204, Severity: 19, State: 1
> >>
> >> I've got the default configuration setting for locks of
> 0
> >> and nothing has changed on this server that I know
> except
> >> for SP2. Furthermore, I've been monitoring the total
> >> number of locks at any given time and I'm not seeing
> >> anything that should cause problems. The most locks
> I've
> >> seen is about 40,000.
> >>
> >> The last time we got this error msg in the error log, I
> >> had two index defrag jobs going and there was bulk load
> >> and/or delete job running.
> >>
> >> Is this simply memory related? Is there anything with
> SP2
> >> that might cause this (especially if it uses extra
> memory
> >> for index defrags)?
> >>
> >> Any insight would be much appreciated?!
> >>
> >
> >
> >.
> >

Lock being acquired in DBCC SHOWCONTIG and sys.dm_db_index_physica

Hi,
Can anyone explain below why the different explanation in terms of S and IS
lock on the table? Or simply an errata in the 2005 BOL or the best practice?
· From SQL 2005 BOL,
Scanning Modes
The mode in which the function is executed determines the level of scanning
performed to obtain the statistical data that is used by the function. mode
is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
page chains for the allocation units that make up the specified partitions of
the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
(S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Shared
(IS) table lock, regardless of the mode that it runs in. For more information
about locking, see Lock Modes.
· From
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
concurrent DML operations.
My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You might want to do a BOL
feedback for this (the link at the bottom of the article).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bill k." <billk@.discussions.microsoft.com> wrote in message
news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
> Hi,
>
> Can anyone explain below why the different explanation in terms of S and IS
> lock on the table? Or simply an errata in the 2005 BOL or the best practice?
>
> · From SQL 2005 BOL,
>
> Scanning Modes
> The mode in which the function is executed determines the level of scanning
> performed to obtain the statistical data that is used by the function. mode
> is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
> page chains for the allocation units that make up the specified partitions of
> the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
> (S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Shared
> (IS) table lock, regardless of the mode that it runs in. For more information
> about locking, see Lock Modes.
> · From
> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
>
> This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
> usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
> concurrent DML operations.
>
>
|||> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
Yes, please do send feedback for the sys.dm_db_index_physical_stats so that
we can get this inaccurate statement corrected.
Thanks,
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HRyInIjHHA.4596@.TK2MSFTNGP05.phx.gbl...
> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bill k." <billk@.discussions.microsoft.com> wrote in message
> news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
>

Lock being acquired in DBCC SHOWCONTIG and sys.dm_db_index_physica

Hi,
Can anyone explain below why the different explanation in terms of S and IS
lock on the table? Or simply an errata in the 2005 BOL or the best practice?
· From SQL 2005 BOL,
Scanning Modes
The mode in which the function is executed determines the level of scanning
performed to obtain the statistical data that is used by the function. mode
is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
page chains for the allocation units that make up the specified partitions of
the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
(S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Shared
(IS) table lock, regardless of the mode that it runs in. For more information
about locking, see Lock Modes.
· From
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
concurrent DML operations.My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You might want to do a BOL
feedback for this (the link at the bottom of the article).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bill k." <billk@.discussions.microsoft.com> wrote in message
news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
> Hi,
>
> Can anyone explain below why the different explanation in terms of S and IS
> lock on the table? Or simply an errata in the 2005 BOL or the best practice?
>
> · From SQL 2005 BOL,
>
> Scanning Modes
> The mode in which the function is executed determines the level of scanning
> performed to obtain the statistical data that is used by the function. mode
> is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
> page chains for the allocation units that make up the specified partitions of
> the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
> (S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Shared
> (IS) table lock, regardless of the mode that it runs in. For more information
> about locking, see Lock Modes.
> · From
> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
>
> This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
> usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
> concurrent DML operations.
>
>|||> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
Yes, please do send feedback for the sys.dm_db_index_physical_stats so that
we can get this inaccurate statement corrected.
Thanks,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HRyInIjHHA.4596@.TK2MSFTNGP05.phx.gbl...
> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bill k." <billk@.discussions.microsoft.com> wrote in message
> news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
>> Hi,
>>
>> Can anyone explain below why the different explanation in terms of S and
>> IS
>> lock on the table? Or simply an errata in the 2005 BOL or the best
>> practice?
>>
>> · From SQL 2005 BOL,
>>
>> Scanning Modes
>> The mode in which the function is executed determines the level of
>> scanning
>> performed to obtain the statistical data that is used by the function.
>> mode
>> is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
>> page chains for the allocation units that make up the specified
>> partitions of
>> the table or index. Unlike DBCC SHOWCONTIG that generally requires a
>> shared
>> (S) table lock, sys.dm_db_index_physical_stats requires only an
>> Intent-Shared
>> (IS) table lock, regardless of the mode that it runs in. For more
>> information
>> about locking, see Lock Modes.
>> · From
>> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
>>
>> This problem has been resolved in SQL Server 2005. In SQL Server 2005,
>> all
>> usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby
>> allowing
>> concurrent DML operations.
>>
>

Lock being acquired in DBCC SHOWCONTIG and sys.dm_db_index_physica

Hi,
Can anyone explain below why the different explanation in terms of S and IS
lock on the table? Or simply an errata in the 2005 BOL or the best practice?
· From SQL 2005 BOL,
Scanning Modes
The mode in which the function is executed determines the level of scanning
performed to obtain the statistical data that is used by the function. mode
is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
page chains for the allocation units that make up the specified partitions o
f
the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
(S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Share
d
(IS) table lock, regardless of the mode that it runs in. For more informatio
n
about locking, see Lock Modes.
· From
http://www.microsoft.com/technet/pr...
ovements.mspx
This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
concurrent DML operations.My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You m
ight want to do a BOL
feedback for this (the link at the bottom of the article).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bill k." <billk@.discussions.microsoft.com> wrote in message
news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
> Hi,
>
> Can anyone explain below why the different explanation in terms of S and I
S
> lock on the table? Or simply an errata in the 2005 BOL or the best practic
e?
>
> · From SQL 2005 BOL,
>
> Scanning Modes
> The mode in which the function is executed determines the level of scannin
g
> performed to obtain the statistical data that is used by the function. mod
e
> is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
> page chains for the allocation units that make up the specified partitions
of
> the table or index. Unlike DBCC SHOWCONTIG that generally requires a share
d
> (S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Sha
red
> (IS) table lock, regardless of the mode that it runs in. For more informat
ion
> about locking, see Lock Modes.
> · From
> http://www.microsoft.com/technet/pr...provements.mspx
>
> This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
> usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowin
g
> concurrent DML operations.
>
>|||> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
Yes, please do send feedback for the sys.dm_db_index_physical_stats so that
we can get this inaccurate statement corrected.
Thanks,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HRyInIjHHA.4596@.TK2MSFTNGP05.phx.gbl...
> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bill k." <billk@.discussions.microsoft.com> wrote in message
> news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
>sql

local variable assignment in CREATE TRIGGER

Hi Guys,

i'm batttling with the below Trigger creation

__________________________________________________ _
CREATE TRIGGER dbo.Fochini_Insert ON dbo.FochiniTable AFTER INSERT AS
BEGIN
DECLARE @.v_object_key VARCHAR(80)
DECLARE @.v_object_name VARCHAR(40)
DECLARE @.v_object_verb VARCHAR(40)
DECLARE @.v_datetime DATETIME

SELECT ins.Cust_Id INTO @.v_object_key FROM inserted ins <-- my problem area!!
SET @.v_object_name = 'FochiniTable'
SET @.v_object_verb = 'Create'
SET @.v_datetime = GETDATE()

IF ( USER <> 'webuser' )
INSERT INTO dbo.xworlds_events (connector_id, object_key, object_name, object_verb, event_priority, event_time, event_status, event_comment)
VALUES ('Fochini', @.v_object_key, @.v_object_name, @.v_object_verb, '1', @.v_datetime,'0', 'Triggered by Customer CREATE')

END
________________________________________________

i'm trying to get the INSERTED variable from table FochiniTable on colomn Cust_Id

and the statement: SELECT ins.Cust_Id INTO @.v_object_key FROM inserted ins - is failing [still a newbie on mssql server 2000]

any help will be appreciated
lehare.solved by modifying the errored line with

SELECT @.v_object_key = ins.Cust_Id FROM inserted ins

thanx any wayz