| Author |
Topic |
|
issammansour
Yak Posting Veteran
51 Posts |
 Posted - 2007-11-10 : 13:52:07
|
| Hi,I know that if you want to unlock all records should issue a rollback or commit statement. But what about to unlock one records from a list of records and keep the other records locked. I used the following statement to make the lock for the records:-SET TRANSACTION ISOLATION LEVEL SERIALIZABLEbegin transaction select item_no,itemdesc from ims.item [rowlock] with (xlock)where item_no = '0100100001'begin transaction select item_no,itemdesc from ims.item [rowlock] with (xlock)where item_no = '0100100002'Thank you for any help |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-10 : 14:08:53
|
| use the "with (nolock)" hint. but note that this can give you dirty reads._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
issammansour
Yak Posting Veteran
51 Posts |
Posted - 2007-11-10 : 14:38:54
|
| i need to unlock the record after the lock made with begin transaction. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-10 : 22:58:45
|
| You can't unless exclude that row from your select statement. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-11-11 : 23:23:31
|
| I think you are missing the point. Locks are a mechanism to implement consistentency and concurrency. They are an implementation detail, not something you should be playing around with like this. What are you trying to do? |
|
|
|
issammansour
Yak Posting Veteran
51 Posts |
Posted - 2007-11-12 : 15:14:49
|
| LoztInSpace,I will try to explain what we do. We are converting our inventory system to use ms sql instead of vfp. The system is a direct telephone sales system, the old system is build with a record lock and we must keep that, when salesman try to sale a products for the customer he lock the records until he finish from the sales order or unlock the record. The sales screen has a grid used to enter the products number and the system show the products details like the price and the qty-on-hand available. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-11-12 : 17:07:15
|
| Hi,What you describe is different to a database lock. You need to implement that yourself in your application. As I said, the database uses locks to keep data consistent while transactions are in progress. If you locked and unlocked a record like this while someone was on the phone then you'd have queries taking as long as phonecalls. You can do this by having an 'in use by' column or something. Don't forget to provide an admin function to unlock as well - trust me you'll need it. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-12 : 17:12:36
|
http://www.setfocus.com/technicalarticles/kgoff/sql-server-2005-newsnapshotisolationlevel.aspx
E 12°55'05.25"N 56°04'39.16" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-11-13 : 18:34:50
|
| With respect to Peso & Spirit1, the isolation level stuff, while great, is still just an implementation of consistency. I do not beleive it solves your business problem of allowing users to 'grab hold of' a customer record, change it and then put it back all interactively. If my understanding is correctt, yhat calls for state in the application as well as transactions to manage that state. |
|
|
|
|
Unlock a record