Menu
I want to do this:
create procedure A as lock table a -- do some stuff unrelated to a to prepare to update a -- update a unlock table a return table b Is something like that possible?
Ultimately I want my SQL server reporting services report to call procedure A, and then only show table a after the procedure has finished. (I'm not able to change procedure A to return table a).
245
In contrast, stored procedures do not acquire table-level locks. All statements executed within stored procedures are written to the binary log, even for statement-based binary logging.
Transaction concepts and locks are different. However, transaction used locks to help it to follow the ACID principles. If you want to the table to prevent others to read/write at the same time point while you are read/write, you need a lock to do this.
A better solution is available: SQL Server provides an application manageable lock mechanism through the sp_getapplock / sp_releaseapplock pair of system stored procedures. They provide a way for application code to use SQL's underlying locking mechanism, without having to lock database rows.
Needed this answer myself and from the link provided by David Moye, decided on this and thought it might be of use to others with the same question:
CREATE PROCEDURE ... AS BEGIN BEGIN TRANSACTION -- lock table "a" till end of transaction SELECT ... FROM a WITH (TABLOCK, HOLDLOCK) WHERE ... -- do some other stuff (including inserting/updating table "a") -- release lock COMMIT TRANSACTION END 
BEGIN TRANSACTION select top 1 * from table1 with (tablock, holdlock) -- You do lots of things here COMMIT This will hold the 'table lock' until the end of your current "transaction".
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
