understand if a record is locked

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

Old Forums

CLOSED - General SQL Server

understand if a record is locked

Author

Topic

andreapavia
Starting Member

7 Posts

Posted - 2005-07-25 : 04:03:58

hi people i'm from italy and new here,,,
first of all forgive my bad english....

DATABASE: MSSQL
APPLICATION: WINFORM WRITTEN IN VB DOT.NET

well this is my trouble

i run a select on my table and i have to put a lock on the selected record...

SELECT * FROM dbtest WHERE id_tag='2'

so
i set the isolation level at the transaction object in my code

ODBCtransaction = ODBCconnection.BeginTransaction(IsolationLevel.RepeatableRead)

and this run very good....

only the user that runs this SELECT can UPDATE or DELETE this RECORD
other user can only view this record and they can't update or delete this one....

trouble:

if a user run an UPDATE the application stands waiting until a commit or rollback....

I DON'T WANT THIS

i have to undertand if the record IS LOCKED

HOW CAN I UNDERSTAND WITH A SELECT WHAT ARE THE RECORD LOCKED????

THANK YOU!

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-25 : 08:32:57

>>if a user run an UPDATE the application stands waiting until a commit or rollback....

it is impossible for another user to find out if a record is locked or not. He must be waiting until your transaction is commited or rollbacked.
Anyway, your question is a bit strange, because even if he would be able to get this information, what will he be doing ? :)

by the way, it makes sense that you use not the repeatable read level but just hint "holdlock" or "updlock"

SELECT * FROM dbtest with (holdlock) WHERE id_tag='2'

andreapavia
Starting Member

7 Posts

Posted - 2005-07-25 : 09:11:22

thanks a lot!!!

CREATE TABLE #locks (spid int, dbid int, objid int, objectname sysname NULL,
indid int, type char(4), resource char(15), mode char(10), status char(6))
INSERT #locks (spid, dbid, objid, indid, type, resource, mode, status)
EXEC dbo.sp_lock
SELECT * FROM #locks where type='key'

--DROP TABLE #locks

well... if i run this script i have a table with the records locked but...

i can get the name of the table with

SELECT object_name(objid)

as i get the name of the locked record's table

does anyboby kwow if it's possible ti get the primary key of the record??????

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-25 : 09:18:41

Selecting the records is SharedLock.
If that primary key is not used in any update or delete statements inside the transaction block, you can select it
See more on locks here
http://www.sql-server-performance.com/reducing_locks_hints.asp

Madhivanan

Failing to plan is Planning to fail

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-25 : 10:00:22

>>does anyboby kwow if it's possible ti get the primary key of the record??????

It's possible to run

SET LOCK_TIMEOUT 0

and if you gets error message it meens that row is locked, but do not do it :)

It seems to me that you're going on the wrong way, you application doesn't need to care about all this stuff, it is the SQL Server responsibility to manage locking, all you need to do in your program is just to choose correct isolation level and try to keep your transactions as short as possible.
Remember, architecture of mssql meens that transactions must wait on locks, this is NORMAL. And you must not implement in your application logic like "let's see, if this row is locked, we will not read it" !

andreapavia
Starting Member

7 Posts

Posted - 2005-07-25 : 10:09:50

i know it's wrong...

but my chief want this way...

so i have to what he tell me...

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-25 : 10:21:54

who is programmer - you or your cheef ? :)

if your boss tell you such things - it meens he undestand nothing in programming.
I guess, if you implement such logic - you program will fail to work properly, and your boss after that will say that it is your fault, so don't listen to him !

sorry for my english...

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-07-25 : 15:20:36

Well, I'm NOT suggesting you do it this way, but you could, it would seem, change yuor select's to use the hint (nolock).

that way you should be able to read the row 1 by USER1, do an update, and have USER2 read row 1, even if USER1 has not committed his change.

This *could* be acceptable if the USER2 is drawing information that does not require accuracy , like statistics that allow for a margin of error, etc. Anywhere where accuracy of data is important (which imho is almost everywhere), avoid using (nolock), and in fact, abvoud using index hints.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

andreapavia
Starting Member

7 Posts

Posted - 2005-07-26 : 03:01:48

ok i use no level from code
connessioneODBC.BeginTransaction()

the user that select a record must be able to run an update

i run

select * from dbtest with (holdlock,rowlock) where id_tag='2'

with this query the user that get this record can not meke the update...

and more... nobody could run an update in any record if i set a rowlock.... if the select record is locked....

i have used the level

connessioneODBC.BeginTransaction(IsolationLevel.RepeatableRead)

becouse is the only way i know to run an update to this....

if anybody kowns something i more....

thanks a lot.....

ps

is this a russian site???

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-26 : 07:09:38

>>i run
>>
>>select * from dbtest with (holdlock,rowlock) where id_tag='2'

at first, most likely you don't need hint "rowlock", this hint is used to tell the Server that you do not want escolation of locks to higher objects in hierarchy (like extent, table and so on), obviously it's not what you want.

>>connessioneODBC.BeginTransaction(IsolationLevel.RepeatableRead)
>>
>>becouse is the only way i know to run an update to this....

as I told you, if you use hint like "holdlock" you don't need RepeatableRead Isolation Level. ReadCommitted is enough.

>>with this query the user that get this record can not meke the update...

no, if you run this, you are able to update the row, but another user cannot make update until you commit or rollback your transaction. But he's still able to read it, until you update it (or, if you use "updlock" hint noboby would be able even to read it).

>>and more... nobody could run an update in any record if i set a rowlock.... if the select record is locked....

The fact that nobody could update any other row in table does not connected with hint "rowlock", it's just because you table doesn't has an index. Build it on column id_tag, and other users will be able to update other rows

>>connessioneODBC.BeginTransaction()

it's good idea to use Stored Procedures rather than implementing your transactions in application

andreapavia
Starting Member

7 Posts

Posted - 2005-07-26 : 10:34:37

i tried you solution and it works as you said before... but this is not my trouble 'couse my solution did the same thing....

this is my big trouble

user1 selects record1 for update it
user1 put with the select the lock on record1
user1 keep the record and does what he wants

user2 have to be able to select record1
but must not be able to update it...

and this is ok...

but how can my software say to user2
hey man, this record is locked you can't run an update???

or more

HOW CAN I UNDERSTAND that a record or better more records is/are locked???

if i run some stored_proc (sp_lock, sp_lock2)

here the sql code

use dbtest
exec sp_who
go

i can see the lock but i can in a column called "RESOURCE" and in column called TYPE (KEY).....

thank's a lot your help!!!

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-26 : 11:05:21

I have a suspicion, that you open a transaction, and show the data to user without comitting transaction. I meen, you run

connessioneODBC.BeginTransaction()
select * from dbtest with (holdlock,rowlock) where id_tag='2'

and after that you just send the resultset to user, wait until he/she change something there, and when user press button "OK" - you run

update dbtest set field1='bla-bla-bla' where id_tag='2'
connessioneODBC.CommitTransaction()

Am I right ?

If so, you don't undestand very important thing in programming under SQL Server - you MUST NEVER LEAVE YOUR TRANSACTION OPEN WHILE USER CHANGE INFORMATION.

You should do the following -

connessioneODBC.BeginTransaction()
select * from dbtest with (holdlock,rowlock) where id_tag='2'
connessioneODBC.CommitTransaction()

and send the resultset to user. After user has changed something, you should run -

connessioneODBC.BeginTransaction()
update dbtest set field1='bla-bla-bla' where id_tag='2'
connessioneODBC.CommitTransaction()

andreapavia
Starting Member

7 Posts

Posted - 2005-07-27 : 05:10:42

i do what you said

but how can tell to other usersB that record locked by userA has a lock????

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-27 : 08:03:37

>>i do what you said

So, you don't commit transaction before user has changed something there ? You must not do this, believe me !

>>but how can tell to other usersB that record locked by userA has a lock????

Andrea (I guess, it's your name), I have already told you several times, that there is no official way to get this information.
Although (as I also have said), it is possible to determine the existance of lock by trying to update the row with option LOCK_TIMEOUT having set on.

for example, first user run

begin tran
select * from dbtest with (holdlock) where id_tag='2'

when another user trying to update the row, he run

SET LOCK_TIMEOUT 0
update dbtest set field1='bla-bla-bla' where id_tag='2'

and if this another user gets error message "Lock request time out period exceeded." - this means that the row he's trying to update is locked.
So if you catch this error in your application, you can show messagebox saying that row is locked.

uberman
Posting Yak Master

159 Posts

Posted - 2005-07-27 : 08:51:39

It might help to restate the problem, confirm that is what is required and then try and work out any solutions.

Reading through the thread I think this is what it trying to be achieved.

A user A views a win form, this contains data in the form "SELECT * FROM dbtest WHERE id_tag='2'"

User A then updates the data regarding id_tag = 2

If at ANY point between the "select" and the "update" User B views the same win form they are to be denied access completely OR they can see the information but not have the ability to update it.

If this is the case, then as has been pointed out you will not be able to this at the SQL server level.

You are going to have (re)write the application in such a way that you achieve this result.

Basically when you issue the "SELECT * FROM dbtest WHERE id_tag='2'" YOU (your application) are going to have to record/log that id_tag=2 is locked by User A, and when User B trys to access id_tag=2 YOU are going to have to access this record/log to see if that id is locked.

When the update (whatever) is issued by User A YOU are going to have to update the record/log to release the lock, so if User B trys again they get accesss.

Where/How you do this (add a locked by field to the table you are selecting from that you update after the select (in a transaction!), have a separate table to record user/record lock, do it at the application level by maintaining a list of records locked by who in memory etc.) you are going to have all sorts of problems...

THe most basic of which is, what if user A issues the select, locking the record and then does nothing... how will you release the lock.

Of course, if my understanding of what you are trying to do is wrong, then just ignore everything above!

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-27 : 09:52:43

2 uberman

what you've described is basicly attempt to mimic Locking Manager of SQL Server. As you said (at least, as far as I understand you) if you implement this - you'll end up with big problems, and the problem is not only that user can set a lock and then go home for holiday leaving some information in inaccessable state, imagin that his computer just suddenly shuts down - how would he release lock after that ?

The important thing that each database-programmer must realize is that when you get some information from database - you can not be sure that this information is "fresh", it is possible that just after you got information - another user has changed it (assuming that you do not leave open transaction).

(I'm terribly sorry for my english)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 10:11:04

Andreapavia, have a look at "optimistic concurrency".
(see the .NET documentation)

Hopefully I am not restating anything in this longish thread.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources