Implementing Locks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Implementing Locks

Author

Topic

ruan4u
Posting Yak Master

132 Posts

Posted - 2004-06-13 : 20:19:37

In a multi-user application,I would like to lock a particular set of records.Ex..
One user enters a payment record. This record is now ready to be printed.The check printing module picks up this and displays it,as "ready to be printed".But someother user selects the record to edit it.This means that the check should not be printed.
How can this be implemented in SQL/VB

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-13 : 20:32:16

Create a table to hold the locked entities. Put an entry in this table to take a lock and make sure it is checked by all the SPs that need to.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ruan4u
Posting Yak Master

132 Posts

Posted - 2004-06-13 : 20:37:55

So you are talking of softlock by creating a table like htis
create table lockids
(table_name char(),
RowPk ,
locktype,
lockedby,
lockedat datetime)
How about with lock option in select. Is there anything equivalent to select for..update clause of Oracle in SQL 2000

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-13 : 21:21:48

Sure - you can create a transaction and use holdlock hints - but that will probably cause a lot of trouble with dealocks and blocking.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ruan4u
Posting Yak Master

132 Posts

Posted - 2004-06-13 : 22:39:28

quote:
Originally posted by nr

Sure - you can create a transaction and use holdlock hints - but that will probably cause a lot of trouble with dealocks and blocking.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Yes, i tried using with lock on selects and it escalates to page level for some reason.I guess soft is the best option.I wonder though is it compliant with industry standards

t1g312
Posting Yak Master

148 Posts

Posted - 2004-07-05 : 14:02:50

Which would be a better idea:
1. Creating a lock table to hold the id or
2. Adding a timestamp column to the record to see if the record has been updated since last retreival?

Adi

-------------------------
/me sux @sql server

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-05 : 14:08:11

I prefer the timestamp. It actually works; and I consider that somewhat of an advantage.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-07-05 : 14:45:59

The SOFT_LOCK option is a pain if a user initiates a "lock" and then goes to lunch / crashes / gets fired.

Something has to watch-dog that table and kill off the stale records. And applications have to be prepared to handle such killed-off records.

But I don't have a good answer either.

All-ish of our tables have an EditNo column. This is incremented on UPDATE and we do things like:


UPDATE MyTable
SET    MyColumn = @MyNewValue
WHERE      MyPKColumn = @SomePKValue
       AND MyEditColumn = @LastKnownEditColumn
IF @@ROWCOUNT = 0 GOTO VeryNaughtyUserError

Kristen

t1g312
Posting Yak Master

148 Posts

Posted - 2004-07-06 : 00:15:27

EditNo seems to be an interesting option too.

Adi

-------------------------
/me sux @sql server

srinivasanr
Starting Member

15 Posts

Posted - 2004-07-06 : 00:32:58

Timestamp will be a better option for multiuser environment.

t1g312
Posting Yak Master

148 Posts

Posted - 2004-07-06 : 00:55:13

Another question: Do I create a custom error message using EM if table is locked?

Adi

-------------------------
/me sux @sql server

Kristen
Test

22859 Posts

Posted - 2004-07-06 : 05:49:06

> Timestamp will be a better option for multiuser environment.

Isn't there some hassle in comparing TIMESTAMP columns, or am I dreaming?

Can I just do:


CREATE PROCEDURE MySProc
   @MyPKColumn     ...
   @MyTimestamp    TIMESTAMP,
   @MyDataColumn 1 ...
   @MyDataColumn 2 ...
AS

UPDATE MyTable
SET    MyDataColumn1 = @MyDataColumn1,
       MyDataColumn2 = @MyDataColumn2
       ...
WHERE  MYPKColumn = @MYPKColumn
       AND MyTimestamp = @MyTimestamp

IF @@ROWCOUNT=0 ... No update made ...

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-06 : 07:47:57

Another question: Do I create a custom error message using EM if table is locked?

No....you shouldn't do anything using EM. If you want to create a custom error message, do it in Query Analyzer.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources