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 |
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
 Posted - 2003-10-07 : 11:52:57
|
| Hello all. I have a large stored procedure that will be gathering in thousands of records and setting a date column on each of them. My question is this, if I wrap this stored procedure in a Transaction, will the table locking happen automatically, or do I need to actually dump in a line of code to prevent writing and reading from this table while this transaction is taking place? (holy run-on sentence Batman!)Thanks all!Aj |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-07 : 12:38:52
|
| The table locking will happen automatically. I would suggest processing the records in batches so that you don't block the rest of the application from running. You can do this in batches using SET ROWCOUNT.Tara |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-07 : 12:40:46
|
| breathe...breathe...It depends (I love that answer)...How long is the transaction btw...And this gathering you talk about...it's a series of cursors...right?And while you're doing the hunter/gathering thing...do you want all the data locked?Meaning you've shut everyone out?And why is setting a date on each row not a set process?Brett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2003-10-07 : 14:18:49
|
| This process is being done in an accounting piece of our software. Basically, it is taking all the services that a client has received (about 12,000 per month combined) and is setting a Financial Posting Month value (someone with accounting knowledge should know what that means) to it so that it can be reported on and used in general ledgering. (man, another run-on)The process has to look at services with a NULL financial posting month, then it has to remove those whose deposit date is outside the range specified by the user, and then it has to look for a particular service status.All in all, not a ton of processing. I would anticipate the entire process taking less than a couple of minutes. We would probably set this up to run at a specific time in the early hours. BTW, we will probably just use a temp table. Cursors would not be necessary since we are doing a bulk update on the records. We just need to have the table locked while we are doing this so that records cannot be updated/inserted/deleted while this process is happening. Thanks!Aj |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-07 : 14:29:14
|
Are you afraid something will "sneak in" and change something before you can change it?Data, by nature is fluid (well except in a warehouse)And you will hold a lock (row, page or table) depending on what's happening.You could always revoke authority to make sure you're the only process going in (unless sa is used all over the place)anyway...it seems like a simple SET UPDATE...quote:
The process has to look at services with a NULL financial posting month
WHERE FIN_POST_MTH IS NULLquote:
then it has to remove those whose deposit date is outside the range specified by the user,
AND DEP_DATE > USER_DT AND DEP_DATE <= USER_DTquote:
and then it has to look for a particular service status.
AND SERV_STAT = 'x'Brett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-07 : 14:39:27
|
| Ah, you've explained more now. You can include the table lock hint in order to block anything from happening.UPDATE Table1 WITH TABLOCKXSET ...Have a look at TABLOCKX in BOL for more information. Or figure out if Brett's solution would work for you.Tara |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2003-10-07 : 16:27:29
|
| Thanks all. We have the logic down, we just did not know for certain whether we needed to specify more locking information or not.Thanks again!Aj |
|
|
|
|
|
|
|
|
Table Locks