pure032398
asked on
How to RowLock in a Stored Proc?
Hi All.
i have some C# code that basically calls two stored procs.
pseudo code
objThing.Get() -> this calls stored proc usp_Thing_Get, which is a basic SELECT *
objThing.Money = 100 + intNewAmount;
objThing.Set() -> this calls stored proc usp_THING_Set, which UPDATES a row.
now, i need to lock the row in the GET, and then UNLOCK IT after i do a set ....
any suggestions please? i have NO idea how this can be done :(
thanks in advance.
i have some C# code that basically calls two stored procs.
pseudo code
objThing.Get() -> this calls stored proc usp_Thing_Get, which is a basic SELECT *
objThing.Money = 100 + intNewAmount;
objThing.Set() -> this calls stored proc usp_THING_Set, which UPDATES a row.
now, i need to lock the row in the GET, and then UNLOCK IT after i do a set ....
any suggestions please? i have NO idea how this can be done :(
thanks in advance.
objThing.BeginTransaction(
...
objThing.Commit()
...
objThing.Commit()
Indeed, you could use a Transaction for your problem. When starting a Transaction (BeginTransaction) you can specify the Isolationlevel. So, here you set who can view/change recordfields of a LOCKED record ...
Using Transactions is very simple, ... transactions offer the developer the ability to enforce data integrity by making sure multiple operations can be treated by the engine as an "all or nothing" proposition, thereby never allowing the database to end up in an inconsistent state.
Some code?
I think you have here a nice story:
http://www.c-sharpcorner.com/asp/Code/TransactionsInASPNETDPL.asp
Look also in the MSDN ...
You could also do it on SQL-Server level, with the <table_hint> part in the FROM clause:
Table Hint Syntax
SYNTAX
[ FROM { < table_source > } [ ,...n ] ]
< table_source > ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
< table_hint > ::=
{ INDEX ( index_val [ ,...n ] )
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
}
There are fifteen hints defined in this syntax listing. Thirteen of these hints (HOLDLOCK, NOLOCK, PAGLOCK, READCOMMITTED, READPAST, READUNCOMMITTED, REPEATABLEREAD, ROWLOCK, SERIALIZABLE, TABLOCK, TABLOCKX, UPDLOCK, XLOCK) are considered table-level.
More info: http://www.sql-server-performance.com/rd_table_hints.asp
But, because you call two SPs seperately? I don"t know if this second part will work?
Why not building ONE stored procedure with all the logic and passing parameters ...? It makes things more simple!
Using Transactions is very simple, ... transactions offer the developer the ability to enforce data integrity by making sure multiple operations can be treated by the engine as an "all or nothing" proposition, thereby never allowing the database to end up in an inconsistent state.
Some code?
I think you have here a nice story:
http://www.c-sharpcorner.com/asp/Code/TransactionsInASPNETDPL.asp
Look also in the MSDN ...
You could also do it on SQL-Server level, with the <table_hint> part in the FROM clause:
Table Hint Syntax
SYNTAX
[ FROM { < table_source > } [ ,...n ] ]
< table_source > ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
< table_hint > ::=
{ INDEX ( index_val [ ,...n ] )
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
}
There are fifteen hints defined in this syntax listing. Thirteen of these hints (HOLDLOCK, NOLOCK, PAGLOCK, READCOMMITTED, READPAST, READUNCOMMITTED, REPEATABLEREAD, ROWLOCK, SERIALIZABLE, TABLOCK, TABLOCKX, UPDLOCK, XLOCK) are considered table-level.
More info: http://www.sql-server-performance.com/rd_table_hints.asp
But, because you call two SPs seperately? I don"t know if this second part will work?
Why not building ONE stored procedure with all the logic and passing parameters ...? It makes things more simple!
ASKER
the reason i have two stored procs, is becuase i could do multiple calculations between the GET (select *) and the SET (update ).
i do not wish to pass the calcualtions to the SP becuase under certain curcumstances, it could be very complex - i need to keep the business logic on one Tier, and the Data on another.
the second reason i didn't mention transactions was becuase i do not know much about them -AND- i wasn't sure that if begin a transaction, this ROW LOCKS the data i am after.
why am i so interested in this row locking?
i have a multithreaded game which needs to make sure the data is always consisent.
i know that if i can LOCK a row when i read it (becuase i'm about to change the data), then i'm 100% sure than any other threads that wish to access this row will wait (or timeout) until it's unlocked.
i do not want to have two threads read the same data, and update the same row with different data. I also do not wish to do some timestamp comparrison on the table / row, becuase then i need to do MORE work to re-read the data, then re-calc, then update ... and i could get could in an infinite loop as many threads are all fighting for the same row, etc.
i do not wish to pass the calcualtions to the SP becuase under certain curcumstances, it could be very complex - i need to keep the business logic on one Tier, and the Data on another.
the second reason i didn't mention transactions was becuase i do not know much about them -AND- i wasn't sure that if begin a transaction, this ROW LOCKS the data i am after.
why am i so interested in this row locking?
i have a multithreaded game which needs to make sure the data is always consisent.
i know that if i can LOCK a row when i read it (becuase i'm about to change the data), then i'm 100% sure than any other threads that wish to access this row will wait (or timeout) until it's unlocked.
i do not want to have two threads read the same data, and update the same row with different data. I also do not wish to do some timestamp comparrison on the table / row, becuase then i need to do MORE work to re-read the data, then re-calc, then update ... and i could get could in an infinite loop as many threads are all fighting for the same row, etc.
ASKER
a further question relating to a comment above...
if i use transactions, could that row lock between the start and the commit?
eg.
// Somehow knows to lock row #10 (for example)
objThing.BeginTransaction(
// some business logic / calculations here ...
eg. intHealth += 10; // very simple example, but can be very complex here...
objThing.Commit()
// Row now updated _AND_ unlocked.
??? is this the idea?
if i use transactions, could that row lock between the start and the commit?
eg.
// Somehow knows to lock row #10 (for example)
objThing.BeginTransaction(
// some business logic / calculations here ...
eg. intHealth += 10; // very simple example, but can be very complex here...
objThing.Commit()
// Row now updated _AND_ unlocked.
??? is this the idea?
Please maintain these old open questions:
1 07/28/2003 60 How to Serialize two (different) instanc... Open C#
2 07/20/2003 450 How to maintain Data Integrity with our ... Open C#
Thanks,
Anthony
1 07/28/2003 60 How to Serialize two (different) instanc... Open C#
2 07/20/2003 450 How to maintain Data Integrity with our ... Open C#
Thanks,
Anthony
ASKER
Anthony - those previous posts/open questions are maintained / updated.
now back to the topic ....
can stored_procs manually lock and unlock a row, or does this have to be asked elsewhere?
now back to the topic ....
can stored_procs manually lock and unlock a row, or does this have to be asked elsewhere?
>> those previous posts/open questions are maintained / updated.<<
Thanks, I appreciate it.
Anthony
Thanks, I appreciate it.
Anthony
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As I and others have said. Perform a transaction. If you dont want to make this one stored proc (which is the sensible thing) then at least put a transaction on connection object!
ASKER
I figured this out last night .. but yeah, the XLOCK was the answer.
becuase i'm not using one stored proc, because my business logic has to be handled in my C# code, using an ADO.Net Transaction works perfectly.
becuase i'm not using one stored proc, because my business logic has to be handled in my C# code, using an ADO.Net Transaction works perfectly.
ASKER
One other thing relating to the Accepted Answer -> i found the XLOCK solution becuase i saw some documentation for 'WITH UPDATE" in some Sql doco..
how does WITH UPDATE differ from XLOCK?
how does WITH UPDATE differ from XLOCK?
Assuming that "pseudo code" is actually pretty much what you want to do, then what you ort to be doing is encapsulating all of this functionality in a single stored procuedre wrapped in a transaction.