VB.net 2005 & SqlServer & Locks & dm_tran_locks

[Ph0b0s]

Hi all. I was thinking you you guys could give me a help... i'm developing and Vb.net 2005 program, wich will be multi-user. I'm working either with SQL Server 2005 and actually working out in the locks. basicly i wanna lock in my program a row from a table, and only let anyone access it again, when it's not locked anymore. I've achieved this in the sql server 2005 query editor:

If you run

select sys.dm_tran_locks.* from sys.dm_tran_locks

(dm_tran_locks give you the current locks) you get all the locks aganist the database. I've done more:

select sys.dm_tran_locks.* from sys.dm_tran_locks where resource_type = 'Key' and resource_description = '(010086470766)'

This way i get only the Key locks on the specific resource_description. If i get rows returned, the row from the resource_description (010086470766) is locked. If not, it's not locked. Cool till here.

The problem is knowing the resource_description within Visual Basic. This way i know the resource_description when i attemp to lock some row, and i can use the query to see the row's avaliability.

Any suggestions finding the resource_description?

[szlamany]

Why are you trying to determine if a row is locked manually like this?

[JAKSupport]

The proper way to do this is to actually perform this mechanism yourself. Add a field of type bit initially default to false. The bit is set to true when the record is locked and back to false when the record is "checked back in". This is how a lot of systems in the real-world work. Users check in / check out documents or check in / check out procedures from source control all the time.

Then your code becomes more robust and flexible...you simply can use ExecuteScalar to return a specific value telling you whether your row is locked or not.

[szlamany]

I'm not sure I agree...

We let the locking mechanism in the database handle locks for us. That's what it's designed for.

Setting a bit might work - but can lead to false locks that never get cleared up.

Checking a system table for a lock has a timing lag in it that won't catch all locks.

[Ph0b0s]

JAKSupport, i understand your poit of view, but the program is already structured and to do this you would need:

- Edit row (bit 1)
- Save Row (So the other read it as locked)
- Edit row again (and put bit 0 in the end)
- Save row.

And imagine if the program block and the row stay like 1 when it should be 0

I really would like to understand how sqlserver gets this resource_description, as it's allways the same to the same resource and unique in the database.

[szlamany]

Have you looked around the sysobjects table to see if you can find a resource value in that table?

[Ph0b0s]

In the beggining we let, but we have cases where it's not possible and 2 pc's running the same option pressing the Update button at the same time, causes an error.

Cases like: you have a table with holds an count number, wich is incremented programaticly every time you grab it, put it in a row, update the row then update the count to +1. If we read the count number, and do not lock the same row, anothe user can read the same count, generating an duplicated index key error.

[Ph0b0s]

yeah, being all the day trying to understand this views and find if some give me the resource_description

[szlamany]

We usually try to come up with clever tricks to insure things like "getting the next transaction number" in a multi-user environment.

Here's a snippet from a STORED PROCEDURE that's getting the next LEDGER TRANSACTION NUMBER for use in an INSERT statement.

RedoB:
	Set @DataValue=(select DataValue from Control_T
			where FiscalYr=@FiscalYr and Item='LAST BATCH')
	Set @NewDataValue=Right('000000'+Cast(Cast(@DataValue as int)+1 as VarChar(6)),6)
	
	Update Control_T
	Set DataValue=@NewDataValue,Tdate=@Tdate
	Where FiscalYr=@FiscalYr and Item='LAST BATCH' and DataValue=@DataValue
	
	If @@RowCount<>1 Goto RedoB

That where CLAUSE makes sure that no other user has grabbed the same value. If they did then we loop around again for another try.

Not sure if this will help - but I thought I might offer alternatives to looking for locks.

[Ph0b0s]

Can help in specific cases! Ty, but cant help in cases like: if i'm editing this row, noone else can edit it =\ Imagine like editing a User Profile and noone else can edit it.

[GaryMazzone]

For editing you could incluled a DateTimeModified column in the DB as DateTime field. When you retrieve a row alwas include that column. When you go to update check the DateTimeModified field against the stored (retreived value) if they are not the same then some has modified the data since you last got it, make them retrieve the data again and look at edit now. When you save an update always update the DateTimeModifed value with the current date and time either using the SQL statement of a trigger on the table.

[szlamany]

Think of how ENTERPRISE MANAGER handles several users OPENING a table and changing rows at the same time...

If it detects that any column in that row is changed it won't allow the UPDATE.

That's accomplished by:

UPDATE SOMETABLE SET...
   WHERE COL1=ORIGCOL1 AND COL2=ORIGCOL2...

Seems like a lot of coding but it completely releases the database to never hold a lock but still allows the user side to detect a change was made to a row.

That can be done as Gary mentioned with a date/time stamp in code as well.

There is also a philosophy that says "why does it matter?" - last user to UPDATE gets there data in the table.

[Ph0b0s]

Found an way to know if any table in my database is locked! And just with a simple query! Works great, but do not solve the problem for the rows. I still can't know wich rows are locked. If anyone wanna the code, let me know.

Thanks guys for the feedback, but i must get around it without any new collumn or with checking if each original field has been modified (this could be accomplished with openoptimistic, wich retrieves an error when updating if any field has been changed).

I'll strugle a bit more. Anyway i can get this working by changing the row to a value and put the new value again. I i can accomplish this, means the row is availabble and will be till i update it. If it gives an error, means someone is editing it. The problem is the timeout of sqlserver. Bascily it waits like 30 seconds to update the data. If not, gives an timeout error.

[RajeshIndia123]

Please send me the database query to know if any table in my database is locked

[szlamany]

Welcome to the forum

You might notice that this thread is two years old - I'm sure the OP doesn't need assistance any longer.

Are you looking for help yourself? What is your backend DB? MS SQL Server?

[RajeshIndia123]

Database is MS SQL server 2005

[RajeshIndia123]

also tell me for MS SQL Server 2000