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.
| Author |
Topic |
|
adlo
Posting Yak Master
108 Posts |
 Posted - 2006-10-19 : 05:51:23
|
| I need to lock a table in a SP.I have the problem that previously the front end application I used handled locking but the application was changed to a new application which doesn't support application locking of tables. Here is the basic logic:1. Lock Table X on application level so that application locks out access to that table. 2. Run stored procedure Importing3. SP: Begin Transaction4. SP: Bunch of select and insert statements on different tables including inserts in Table X.5. SP: commit transaction6. SP release lock on table X on application levelPoint 1 and 6 which was handled by the previous application needs to be handled by SQL server so that the application will pick up an access denied error when trying to access that table.Now point 1 and 6 has to change to database level to lock tableX except for the inserts on table X in point 4.I can't go into full depth on the reason why this unattractive method is needed. I now the database is badly designed if i have to lock a whole table over many statements but this is due to table relationships (not SQL Server controlled) that i may not change.Please don't respond on why do I want to do this. I have no choice in the matter. Also don't tell me it's inefficient or slows the databse down as I just need a solution for due to the application change i have to do the locking inside the stored proc but i may not change the rest. Please assist.I appreciate your non cynical responses. |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2006-10-19 : 07:21:52
|
| Move steps 1 and 2 into the first SP as the first steps. Move step 6 into your SP right after the COMMIT or ROLLBACK in your SP.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
 |
|
|
adlo
Posting Yak Master
108 Posts |
Posted - 2006-10-19 : 07:35:54
|
| My problem is I want to know how to lock a table in SQL Server.OK I am allowed to lock only 1 row in tableX on its primary key value e.g. '12345678' (Step1) and release the lock in step6 on that row.Now my question is how do I lock one row in a table. (while multiple other sql statements are being run) |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2006-10-19 : 12:28:39
|
| look up "locking, table-level" or "Locking Hints" in BOL.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
|
|
|
Need to lock a whole table in SQL Server 2000