Locking tables - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Locking tables

Author

Topic

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-05-02 : 13:12:15

I've written a C# program which (1) Calls a SQL stored procedure which, among other things, updates a table with email information and then (2) sends the email via Outlook. This is ultimately going to be on a production server where the C# email program can be called by a number of processes.

If more than one instance of the email program is invoked at the same time, problems could occur. For instance, after the first instance updated the table it would then begin the process of creating and sending the email, but if a second instance is called at that time,it could be updating the SQL table which I think would screw up the email the first instance was creating. As a solution, I've been looking into locking tables. What I'd like to do is lock the table after it's been updated and unlock it after the email is sent. I know I can use the SLEEP function in C# so if it tries to access the table and it's locked, to wait 20 seconds or so and try again.

So how can you lock (and unlock?) a table in SQL? I'm reading about using WITH (TABLOCK) in the UPDATE query, but I'm not sure that will solve my problem. From what I can tell, TABLOCK automatically "unlocks" when the update is done. That still wouldn't resolve the issue of instance 2 updating the SQL table after instance 1 had updated it, but BEFORE instance 1 has completing creating the email (based off the data in the table).

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-03 : 20:48:30

Don't need lock hint as long as you put update and send mail in single transaction.