Recordset not updatable

I use MS Access Project to connect to MS-SQL 2000. Even though I login as owner of the database, I can't update my table with message 'Recordset is not updatable'
I have no problem updating through Enterprise Manager. Someone out there. Please help meAccess needs unique index to update a table. When linking SQL tables Access may ask you to select index columns. Select the number of columns needed for an "unique index"

Recordset not updatable

I use MS Access Project to connect to MS-SQL 2000. Even though I login as owner of the database, I can't update my table with message 'Recordset is not updatable'.
I have no problem updating through Enterprise Manager. Someone out there. Please help me
Access needs unique index to update a table. When linking SQL tables Access may ask you to select index columns. Select the number of columns needed for an "unique index"

Recordset not updatable

I use MS Access Project to connect to MS-SQL 2000. Even though I login as ow
ner of the database, I can't update my table with message 'Recordset is not
updatable'.
I have no problem updating through Enterprise Manager. Someone out there. Pl
ease help meAccess needs unique index to update a table. When linking SQL tables Access
may ask you to select index columns. Select the number of columns needed for
an "unique index"

recordset

Friends,

Is there any thing like a recordset concept in sql server,
where i could loop through and update each rows

I want to update a null column of a table with some values.

I already made a cursor(Shown below)

Instead of this can I make any recordset/resultset and update each rows?

Tks..

Declare @.RpID int
Declare @.JobID int
declare @.ID int
declare @.cnt int

set @.cnt = 0
set @.ID = 0

declare c1 cursor for

select Reprint_ID, Job_ID from ReprintInfo order by Job_ID
open c1
fetch next from c1 into @.RpID, @.JobID
while @.@.Fetch_Status = 0
begin

if (@.ID = @.JobID)
set @.cnt = @.cnt + 1
else
set @.cnt = 1

update reprintinfo set Reprintcount = @.cnt
where Reprint_ID = @.RpID

set @.ID = @.JobID

fetch next from c1 into @.RpID,@.JobID

end

close c1
deallocate c1

Thanks,

MG

A CURSOR is the closest thing to a 'recordset'. However, it is usually NOT the best way to do things in SQL Server. SQL Server handles SET based operations very efficiently.

It seems that you are consecutively numbering the rows for each JoB_ID. That can most likely be done using a SET based approach.

What version of SQL Server are you using?

|||

One way to perform the update if you have an index based on JOB_ID is to use the Transact SQL update extensions in something like:

create table dbo.reprintInfo
( Reprint_ID integer,
Job_ID integer,
reprintCount integer
)
go

create index reprintInfo_updNdx
on reprintInfo (job_id)
go

insert into reprintInfo
select 1, 1, null union all
select 2, 1, null union all
select 3, 2, null union all
select 4, 3, null union all
select 5, 3, null union all
select 6, 3, null union all
select 7, 3, null union all
select 8, 4, null
select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 NULL
-- 2 1 NULL
-- 3 2 NULL
-- 4 3 NULL
-- 5 3 NULL
-- 6 3 NULL
-- 7 3 NULL
-- 8 4 NULL

declare @.count integer
declare @.lastJobId integer
set @.count = 1

update reprintInfo
set reprintCount
= case when @.lastJobId = job_id
then @.count
else 1
end,
@.count = case when @.lastJobId is null
or @.lastJobId <> job_id
then 1
else @.count + 1
end,
@.lastJobId = job_Id
from reprintInfo (index=reprintInfo_updNdx)

select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 1
-- 2 1 2
-- 3 2 1
-- 4 3 1
-- 5 3 2
-- 6 3 3
-- 7 3 4
-- 8 4 1

recordset

Friends,

Is there any thing like a recordset concept in sql server,
where i could loop through and update each rows

I want to update a null column of a table with some values.

I already made a cursor(Shown below)

Instead of this can I make any recordset/resultset and update each rows?

Tks..

Declare @.RpID int
Declare @.JobID int
declare @.ID int
declare @.cnt int

set @.cnt = 0
set @.ID = 0

declare c1 cursor for

select Reprint_ID, Job_ID from ReprintInfo order by Job_ID
open c1
fetch next from c1 into @.RpID, @.JobID
while @.@.Fetch_Status = 0
begin

if (@.ID = @.JobID)
set @.cnt = @.cnt + 1
else
set @.cnt = 1

update reprintinfo set Reprintcount = @.cnt
where Reprint_ID = @.RpID

set @.ID = @.JobID

fetch next from c1 into @.RpID,@.JobID

end

close c1
deallocate c1

Thanks,

MG

A CURSOR is the closest thing to a 'recordset'. However, it is usually NOT the best way to do things in SQL Server. SQL Server handles SET based operations very efficiently.

It seems that you are consecutively numbering the rows for each JoB_ID. That can most likely be done using a SET based approach.

What version of SQL Server are you using?

|||

One way to perform the update if you have an index based on JOB_ID is to use the Transact SQL update extensions in something like:

create table dbo.reprintInfo
( Reprint_ID integer,
Job_ID integer,
reprintCount integer
)
go

create index reprintInfo_updNdx
on reprintInfo (job_id)
go

insert into reprintInfo
select 1, 1, null union all
select 2, 1, null union all
select 3, 2, null union all
select 4, 3, null union all
select 5, 3, null union all
select 6, 3, null union all
select 7, 3, null union all
select 8, 4, null
select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 NULL
-- 2 1 NULL
-- 3 2 NULL
-- 4 3 NULL
-- 5 3 NULL
-- 6 3 NULL
-- 7 3 NULL
-- 8 4 NULL

declare @.count integer
declare @.lastJobId integer
set @.count = 1

update reprintInfo
set reprintCount
= case when @.lastJobId = job_id
then @.count
else 1
end,
@.count = case when @.lastJobId is null
or @.lastJobId <> job_id
then 1
else @.count + 1
end,
@.lastJobId = job_Id
from reprintInfo (index=reprintInfo_updNdx)

select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 1
-- 2 1 2
-- 3 2 1
-- 4 3 1
-- 5 3 2
-- 6 3 3
-- 7 3 4
-- 8 4 1

Record set update

Hi

I am new to visual studio and I am attempting to edit records held in mysql, the code below runs and throws no errors "strAdd" is underlined and says that it is used before it has been given a value! But If I debug.print(strAdd) I get the expected string returned. What do I need to do to get the updated records saved?

' code

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim StrAdd as string

cn = New ADODB.Connection

cn.ConnectionString = "Provider=SQLNCLI;" _

& "Server=(local);" _

& "Database=customerlink;" _

& "Integrated Security=SSPI;" _

& "DataTypeCompatibility=80;" _

& "MARS Connection=True;"

Dim mySQL As String

mySQL = "SELECT*" & _

" FROM tblvsol" & _

" Where RevStatus = " & 0 & _

" And GeoCodeStatus = " & 1

cn.Open()

rs = New ADODB.Recordset

With rs

.ActiveConnection = cn

.CursorLocation = ADODB.CursorLocationEnum.adUseClient

.CursorType = ADODB.CursorTypeEnum.adOpenDynamic

.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic

.Open(mySQL)

End With

Do While Not rs.EOF

Code here finds the value for the string variable ‘atrAdd’

strAdd = New Value

If Not strAdd Is Nothing Then

rs.Fields("location").Value = strAdd

rs.Fields("RevStatus").Value = 1

rs.Update()

else

end if

loop

Regards

Joe

Hi Experts

Have I posted in the wrong forum?

Regards

|||Can you add some code on how you're assigning new value to strAdd?|||Actually, yes you posted in the wrong forum, but nevertheless we will help you. Do you enter the Is Nothing part and the appropiate update or does the execution never touch this portion of the code ? Are you able to send a reproducable class to us ? as from the information you posted the code should be Ok. Did you start the profiler to see wheter the command is executed against the database or could you make sure through debugging that it never enters portion of the code ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Experts

Thanks for your response, The variable StrAdd Is a street address which is returned from a mappoint recordset:

StrAdd = objResults.Item(1).Location.StreetAddress.Value

This is returned as a string.

The update portion of the code is only entered If a string value is returned from the mappoint recordset.

Regards,

joe

|||Ok, this is now a bit clearer to me, but did you check the value during debugging time or did you run the profiler ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens K. Suessmeyer.

Thanks for your response, I had checked the value during debugging. I have had a reply posted on another forum which has provided me with the answer, I appreciate your efforts on my behalf and would not like to waste your time. I have reproduced the reply below, again..

Thank you,

Joe

instead of opening the recordset like so:

rcdSet.Open strSQL, ADO, adOpenForwardOnly, adLockReadOnly, -1

Use this method:

rcdSet.Open strSQL, ADO, adOpenDynamic, adLockBatchOptimistic, -1

Now, after looping through your recordset and changing your values, call:

rcdSet.UpdateBatch

|||Or as an alternative if you want to use Update method and not UpdateBatch - then you can use adLockOptimistic for lock type.

Record lost with UPDATE statements

Hi,
I am using MS SQL Server 7.0 SP2 in Windows 2000 server SP4.

I have one-to-many tables (TABLE_HEAD and TABLE_DETAILS)which I am
going to update by using a stored procedure with UPDATE statements.

But somehow ,ONCE IN A WHILE, when executing the stored procedure
with about 1000 rows updated, I lost 10-20 records from TABLE_HEAD
(seems like 10-20 records were deleted) , and all data rows in
TABLE_DETAILS were updated correctly (even details of lost rows of
TABLE_HEAD).

In update procedure, I update both part of primary key and other
columns with having WHERE condition.

Please help , I really don't know why this happens.

Thanks in advance
Nipon WongtrakulHave you checked if there are any triggers on the table you are updating?
Test any trigger code to see if it is handling updates of the primary key
columns correctly.

--
David Portas
SQL Server MVP
--|||Hi

Using a surrogate key will remove the need to update the details table.
Posting DDL (Create table etc) and example data (as insert statements) along
with the statements you are using may help to highlight other problems.

Also once using profiler may show something being missed.

John

"Nipon" <niponw@.yahoo.com> wrote in message
news:4c537316.0406141729.6e3cee68@.posting.google.c om...
> Hi,
> I am using MS SQL Server 7.0 SP2 in Windows 2000 server SP4.
> I have one-to-many tables (TABLE_HEAD and TABLE_DETAILS)which I am
> going to update by using a stored procedure with UPDATE statements.
> But somehow ,ONCE IN A WHILE, when executing the stored procedure
> with about 1000 rows updated, I lost 10-20 records from TABLE_HEAD
> (seems like 10-20 records were deleted) , and all data rows in
> TABLE_DETAILS were updated correctly (even details of lost rows of
> TABLE_HEAD).
> In update procedure, I update both part of primary key and other
> columns with having WHERE condition.
> Please help , I really don't know why this happens.
> Thanks in advance
> Nipon Wongtrakul|||>> I have one-to-many tables (TABLE_HEAD and TABLE_DETAILS)which I am
going to update by using a stored procedure with UPDATE statements. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

>> But somehow ,ONCE IN A WHILE, when executing the stored procedure
with about 1000 rows updated, I lost 10-20 records [sic] from
TABLE_HEAD (seems like 10-20 records [sic] were deleted), and all data
rows in TABLE_DETAILS were updated correctly (even details of lost
rows of TABLE_HEAD). <<

If there is no header for a set of details, then the ON DELETE CASCADE
should have removed them for you. Likewise, the ON UPDATE CASCADE
action should have done some of the work for you between the PK-FK.

>> In update procedure, I update both part of primary key and other
columns with having WHERE condition. <<

We need to see code to debug it. It could be:

1) If you use a locator like IDENTITY as a key, and then update the
natural key, you can get the relationships out of synch.

2) There is a TRIGGER doing strange things.

3) The updates are not in the same transaction

4) The UPDATE has a FROM or other proprietary clause that does strange
things.

5) Something else.

Record locking within a stored procedure

Hi
I'd like to be able to lock a record in a table, row locking, update some
fields, then release the lock when finished. I'd also like to be able to
attempt the row lock for a specified amount of time, if, for example, anothe
r
session is already locking this record. The session would only be locking th
e
record for a minute amount of time, however I need to ensure that no
conflicts occur.
If it helps, I'm running the stored procedures via VFP9 so I know a
reasonable amount of SQL syntax, parsing etc.
What's the SQL syntax to complete something like this?
RegardsG18LLO (G18LLO@.discussions.microsoft.com) writes:
> I'd like to be able to lock a record in a table, row locking, update
> some fields, then release the lock when finished. I'd also like to be
> able to attempt the row lock for a specified amount of time, if, for
> example, another session is already locking this record. The session
> would only be locking the record for a minute amount of time, however I
> need to ensure that no conflicts occur.
> If it helps, I'm running the stored procedures via VFP9 so I know a
> reasonable amount of SQL syntax, parsing etc.
> What's the SQL syntax to complete something like this?
There is not really explicit syntax for this. The locking that SQL Server
uses for it's purposes is not intended for application use, nor is it
suitable for it.
There are a couple of ways to go. One is to add a column to the table
saying that it is locked. Such a column should probably have some sort
of a time stamp, and some rules to tell whether the lock can considered
to still be valid or to be stale.
Another way is to use application locks. In this case you are using
the lock manager in SQL Server, but you are not interferring with SQL
Server's internal business. An application is lock on a named resource.
Assuming the the table is called Widgets and has a numeric id as its
primary key, you could create an application lock on the resource
"Widget16" to lock the row with WidgetId = 16.
One thing to consider here is that you should not run too long transactions.
For instance, if the record is locked, because a user is about to update
it, you should have a transaction while waiting for user input. This
does not rule out application lock, as they can be on session level.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Thu, 29 Dec 2005 11:03:07 -0800, G18LLO
<G18LLO@.discussions.microsoft.com> wrote:
>I'd like to be able to lock a record in a table, row locking, update some
>fields, then release the lock when finished. I'd also like to be able to
>attempt the row lock for a specified amount of time, if, for example, anoth
er
>session is already locking this record. The session would only be locking t
he
>record for a minute amount of time, however I need to ensure that no
>conflicts occur.
>If it helps, I'm running the stored procedures via VFP9 so I know a
>reasonable amount of SQL syntax, parsing etc.
>What's the SQL syntax to complete something like this?
It's politicallly incorrect to do this "pessimistic locking" in
SQLServer, although it is possible and works reasonably well, if you
know what you're doing.
Something like:
begin transaction
-- lock record from other writers, they can still read
select <anyfield> from <yourtable> with (updlock)
-- with modification and default isolation levels,
-- others will probably not be able to read, either
update <yourtable> set <fields>
commit transaction
-- now changes are made and record(s) unlocked
Actually, you may not even need the select, unless you want to lock in
advance of the update.
Also look at lock_timeout, if you play with pessimistic locking you're
going to need it!
Note that in SQLServer2005 it will become a little bit less
politically incorrect because of the new "look-aside" isolation level
(I forget the Microsoft name for it ...)
Good luck.
Josh|||Josh with all due respect, SQL Server will not block until there is an updat
e
that occurs in a database transaction. A select with holdlock or updlock,
will allow another user to also select the same data with or without a
holdlock or updlock. IMO, SQL Server shouldn't do that but it does.
If you want users to line up, single file in a queue, the following code
will cause it to happen.
begin transaction
--basically a bogus update by setting a column to itself
--this causes a lock on that row
--any other T-SQL code that tries to update the same row will wait in line
--effectively creating a queue
update MyControlTable
set <column> = <column>
where <condition>
<do your work>
commit transaction
If there is only one situation, MyControlTable can be a single column,
single row table. In my case, I had a multiple column, multipler row table
so I could block users based on company id and functional area. For example
,
company 1 and loading customer data to ensure two different people didn't tr
y
to run a data load of customer information for company 1 at the same time as
the program code and business rules did not support concurrent data loading.
Another example, a person could queue up the loading of sales data for
different periods without having to wait for one to finish before starting
the next one. SQL Server became the traffic cop.
NOTE: This does not address the waiting for a specified amount of time. You
should be able to use the connection's timeout property.
Just my two cents,
Joe
"jxstern" wrote:

> On Thu, 29 Dec 2005 11:03:07 -0800, G18LLO
> <G18LLO@.discussions.microsoft.com> wrote:
> It's politicallly incorrect to do this "pessimistic locking" in
> SQLServer, although it is possible and works reasonably well, if you
> know what you're doing.
> Something like:
> begin transaction
> -- lock record from other writers, they can still read
> select <anyfield> from <yourtable> with (updlock)
> -- with modification and default isolation levels,
> -- others will probably not be able to read, either
> update <yourtable> set <fields>
> commit transaction
> -- now changes are made and record(s) unlocked
> Actually, you may not even need the select, unless you want to lock in
> advance of the update.
> Also look at lock_timeout, if you play with pessimistic locking you're
> going to need it!
> Note that in SQLServer2005 it will become a little bit less
> politically incorrect because of the new "look-aside" isolation level
> (I forget the Microsoft name for it ...)
> Good luck.
> Josh
>|||Joe from WI (JoefromWI@.discussions.microsoft.com) writes:
> Josh with all due respect, SQL Server will not block until there is an
> update that occurs in a database transaction. A select with holdlock or
> updlock, will allow another user to also select the same data with or
> without a holdlock or updlock. IMO, SQL Server shouldn't do that but it
> does.
Then you have misunderstood the meaning of these hints.
HOLDLOCK simply means "use serializable isolation level". That is, ensure
that if I run this SELECT in the same transaction, that it will return
the same result. No rows modified, deleted or added.
UPDLOCK means "I am about to update this row". UPDLOCK is a shared lock,
in so far that it does not prevent other readers, but only one process
can have an UPDLOCK on a resource.

> If you want users to line up, single file in a queue, the following code
> will cause it to happen.
> begin transaction
> --basically a bogus update by setting a column to itself
> --this causes a lock on that row
> --any other T-SQL code that tries to update the same row will wait in line
> --effectively creating a queue
> update MyControlTable
> set <column> = <column>
> where <condition>
><do your work>
That is a poor solution. (Not the least since the DB engine may outsmart
you, and not take out a lock, since nothing was changed.) There are at least
two that are better.
One is to use the XLOCK hint to get an exclusive lock.
But the best in my opinion is to use application locks, as then you have
more control over the resources you lock.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> One is to use the XLOCK hint to get an exclusive lock.
Just be careful with the XLOCK hint. There's an optimization where SQL Serve
r doesn't respect a row
level XLOCK if the row hasn't been modified since the earliest open transact
ion (or something to
that effect):
--Connection 1
USE pubs
BEGIN TRAN
SELECT *
FROM authors (xlock)
WHERE au_lname = 'White'
--Connection 2
USE pubs
EXEC sp_lock
SELECT *
FROM authors
WHERE au_lname = 'White'
--Query is not blocked
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973E924F3F64Yazorman@.127.0.0.1...
> Joe from WI (JoefromWI@.discussions.microsoft.com) writes:
> Then you have misunderstood the meaning of these hints.
> HOLDLOCK simply means "use serializable isolation level". That is, ensure
> that if I run this SELECT in the same transaction, that it will return
> the same result. No rows modified, deleted or added.
> UPDLOCK means "I am about to update this row". UPDLOCK is a shared lock,
> in so far that it does not prevent other readers, but only one process
> can have an UPDLOCK on a resource.
>
> That is a poor solution. (Not the least since the DB engine may outsmart
> you, and not take out a lock, since nothing was changed.) There are at lea
st
> two that are better.
> One is to use the XLOCK hint to get an exclusive lock.
> But the best in my opinion is to use application locks, as then you have
> more control over the resources you lock.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Tibor Karaszi (tibor_please.no.email_karaszi@.hotmail.nomail.com) writes:
> Just be careful with the XLOCK hint. There's an optimization where SQL
> Server doesn't respect a row level XLOCK if the row hasn't been modified
> since the earliest open transaction (or something to that effect):
> --Connection 1
> USE pubs
> BEGIN TRAN
> SELECT *
> FROM authors (xlock)
> WHERE au_lname = 'White'
>
> --Connection 2
> USE pubs
> EXEC sp_lock
> SELECT *
> FROM authors
> WHERE au_lname = 'White'
> --Query is not blocked
Thanks, Tibor.
Just stresses my point that you should use application locks for this
purpose.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Please excuse my ignorance for locking hints but if you have two transaction
s
issuing a select with a xlock, updlock, or holdlock on the same row of data,
how can SQL Server guarntee that the transaction will be able to repeat the
read? The only combination that worked in my testing is the holdlock when
tran1 selected data with a holdlock, tran2 could select the data with a
holdlock but it could not update it until tran1 completed. If tran1 had a
updlock or xlock, tran2 was able to read and update the data ignoring tran1'
s
lock.
So I'd recommend having a datetime column so that there is a real update
just in case the optimizer gets too smart. ;) Optionally, add connection
information.
begin transaction
update MyControlTable
set LastLock = getdate(), SPID = @.@.SPID, Username = SYSTEM_USER,
ApplicationName = APP_NAME, Workstation = HOST_NAME ( ) , DBUser = USER_NAME
()
where <condition>
<do other work here>
commit transaction
Personally, I would NOT use an application lock such as updating a column on
the data row indicating that it is locked. Because sooner or later, there
will be an application error, dropped connection, or whatever and you're
stuck with a logical lock on the row. And using a datetime to deterimine
whether the lock is stale can be dangerous, in my opinion. How long do you
let other users wait--seconds? minutes? hours? days? Sooner or later,
someone will come along with a longer-than-expected job and the logical lock
s
become worthless.
With my solution, as soon as the connection drops one way or another the
lock is released automatically (either through a commit or a rollback) and
the next user has immediate access.
Just my two cents,
Joe
"Erland Sommarskog" wrote:

> Tibor Karaszi (tibor_please.no.email_karaszi@.hotmail.nomail.com) writes:
> Thanks, Tibor.
> Just stresses my point that you should use application locks for this
> purpose.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||See my post about XLOCK hint being essentially useless.
UPDLOCK work, but both connections need to use UPDLOCK. Update lock doesn't
block shared locks.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
news:6E32CDD2-ECB5-44F4-A2E6-249794010E06@.microsoft.com...
> Please excuse my ignorance for locking hints but if you have two transacti
ons
> issuing a select with a xlock, updlock, or holdlock on the same row of dat
a,
> how can SQL Server guarntee that the transaction will be able to repeat th
e
> read? The only combination that worked in my testing is the holdlock when
> tran1 selected data with a holdlock, tran2 could select the data with a
> holdlock but it could not update it until tran1 completed. If tran1 had a
> updlock or xlock, tran2 was able to read and update the data ignoring tran
1's
> lock.
> So I'd recommend having a datetime column so that there is a real update
> just in case the optimizer gets too smart. ;) Optionally, add connection
> information.
> begin transaction
> update MyControlTable
> set LastLock = getdate(), SPID = @.@.SPID, Username = SYSTEM_USER,
> ApplicationName = APP_NAME, Workstation = HOST_NAME ( ) , DBUser = USER_NA
ME()
> where <condition>
> <do other work here>
> commit transaction
> Personally, I would NOT use an application lock such as updating a column
on
> the data row indicating that it is locked. Because sooner or later, there
> will be an application error, dropped connection, or whatever and you're
> stuck with a logical lock on the row. And using a datetime to deterimine
> whether the lock is stale can be dangerous, in my opinion. How long do yo
u
> let other users wait--seconds? minutes? hours? days? Sooner or later,
> someone will come along with a longer-than-expected job and the logical lo
cks
> become worthless.
> With my solution, as soon as the connection drops one way or another the
> lock is released automatically (either through a commit or a rollback) and
> the next user has immediate access.
> Just my two cents,
> Joe
> "Erland Sommarskog" wrote:
>|||Joe from WI (JoefromWI@.discussions.microsoft.com) writes:
> Personally, I would NOT use an application lock such as updating a
> column on the data row indicating that it is locked. Because sooner or
> later, there will be an application error, dropped connection, or
> whatever and you're stuck with a logical lock on the row.
No, an application lock is handled by lock manager in SQL Server.
Application locks on either be on transaction level or session level.
Application locks on transaction level are releasd when the transaction
is committed or rolled back. Session-level locks are released when
the process disconnects. (There is a bug in SQL 2005 RTM, though, so
that a session application lock survives the reuse of a connnection
from the connection pool. I expect this bug to be fixed in SP1 of SQL 2005.
For more info, see sp_setapplock in Books Online.

> And using a datetime to deterimine whether the lock is stale can be
> dangerous, in my opinion. How long do you let other users
> wait--seconds? minutes? hours? days? Sooner or later, someone will come
> along with a longer-than-expected job and the logical locks become
> worthless.
Using a column to mark a row as lock is also a viable technique.
Particularly, this solution is necessary if the row is to be
locked while waiting for user input. Locking resources while waiting
for user input is simply admissible. What if user goes to lunch? Or
for holidays in two ws.
For how long to wait before such a lock is defined stale, is a
business decision, but maybe 30 minutes is reasonable. Of course
the application must be able to handle if the user presses Save after
40 minutes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx