Locking the table

Question

I have the below code

SELECT
    CustomerDimId
INTO #tempCustomer
FROM tblCustomer
WHERE RowOut IS NULL
AND State= 'IL'

BEGIN TRANSACTION
     
     UPDATE a
     SET Rowout = @Now
     FROM tblCustomer a
     WHERE CustomerDimId in ( Select CustomerDimId from tblCustomer)
     
     INSERT INTO tblCustomer (CustomerId,State,Status,RowIn,Rowout)
     SELECT CustomerId,State,Status,@Now,NULL FROM #tempCustomer
     
    IF @@error <> 0
        Rollback

COMMIT TRANSACTION

I have two questions:

1. How do I lock the table tblCustomer( I didn't find resource..)
2. whether really I need to lock the table to do the transaction?
 (Since ETL process will select * FROM tblCustomer where rowin >@lastrun and rowout is nuLL) and load data to warehouse. While I am doing this transaction I don't want anyone to select from the table..

What is the best way to do it.

Answer 1

You can use the TABLOCK hint to create a shared lock on the table, however. In this case, the data is not being changed, so why would you want to prevent access to it. Also, I can't think of a way to block access to a table while doing a SELECT, whether inside a transaction or with using the LOCK.

---

Abdallah El-Chal, PMP, ITIL, MCTS

Answer 2

Thanks.

Where to use the TABLOCK and how?

Other process will select the table WITHOUT USING [with (NOLOCK)]. My assumption is sql server will suspend other process  until the lock is released for the table, am I right?

Answer 3

The following will create a lock on the table and it will be released once the update is done.

UPDATE TableName
WITH(TABLOCK)
SET ColumnName = 'Test1'
WHERE ColumnName = 'Test2'

Even if you specify the lock, SQL Server might choose not to honor it for many reasons.
When you try to select from a table, a Shared lock is acquired, and when you have another select on the same table, it needs another Shared lock, and that's why you can still read because it's a Shared lock.

I honestly don't like to control the behaviour of the locking and blocking and I let the engine does that because SQL Server does a good job with it.

Here is a good article that could help you understand Lock modes a little better, and I know, this is not an easy subject to digest. :)
http://msdn.microsoft.com/en-us/library/ms175519.aspx

---

Abdallah El-Chal, PMP, ITIL, MCTS

Answer 4

Ok Let me put the question this way. Consider

BEGIN TRANSACTION
           STEP 1 : UPDATE (from above)
           STEP 2 : INSERT (from Above)
COMMIT TRANSACTION

Consider step 1 is completed, another process select WITHOUT USING [with (NOLOCK)], So the sql server wiill suspend the another process until my transaction is committed?

Answer 5

If you run the entire above statement including COMMIT TRAN, the SELECT will(if executed in another SPID and without the NOLOCK), will not have access to the table until the COMMIT TRAN is committed or a ROLLBACK is committed if you have one.
If you execute it with NOLOCK, you will have what is called DIRTY READS, meaning you will see data before it's committed or rolledback.

---

Abdallah El-Chal, PMP, ITIL, MCTS

Answer 6

What if the vice verse happens i.e First Another SPID is selecting without NOlOCK, My statement will wait until the statement is completed?

Answer 7

No, a SELECT statement will not block access to the table because there is data modifications.

---

Abdallah El-Chal, PMP, ITIL, MCTS

Answer 8

So here is my case ,consider the  select statement is in process(not completed) which is currently  selected 10 rows of verified(where clause) 50 rows out of 100 rows(total rows)
if my update statement updates and commits in between the select statement process , the select won't verify the rows which is been updated now ?

let me know the question is confusing?.

Answer 9

Hello,

First, you should never use explicit transactions. It will lock all used tables from outside sessions.
Second, you don't need to lock the tables in your example.
Third, it seems your INSERT should execute before your UPDATE.

Adam

---

Dibble and dabble but please don't babble.

Answer 10

OP, the SELECT will not read data that is not committed yet by the UPDATE.

Explicit transactions should be used where needed.

In a bank transaction, if you are moving money from your checking to your saving, and you do the withdrawal first from your checking and something happens, and the withdrawal transaction is committed but not the deposit, you will loose your money.
In this case, you will need to nest your script inside an explicit transaction where either both transactions commit or both fail.

---

Abdallah El-Chal, PMP, ITIL, MCTS

Answer 11

You should fix the issues that cause the transaction to fail and use standard error handling. Explicit transactions are an outdated concept and very poor programming practice. Unfortunately, in a maintenance, you don't always have the opportunity to redesign it, but bad design is something we always have to code around.

SQL Server can handle transactions behind the scenes much more efficiently than a stored procedure or two. Trust me. I've had to prove this more times than I think I needed to...and it never hit home until after most of the database was locked up.

Personally, I have yet to see an example of where explicit transaction were needed and not poor practice.

Adam

---

Dibble and dabble but please don't babble.

Answer 12

Adam,

can you provide any references that say "Explicit transactions are an outdated concept and very poor programming practice" ?
Error handling and Locking are not the same.

Thanks

---

Abdallah El-Chal, PMP, ITIL, MCTS

Answer 13

Abdallah,

What type of references are you looking for? If you can show me an example where you think explicit transactions are the only option or even a considerable, I can help you understand how to code it more efficiently.

For example, in some cases when you're inserting/updating in the same stored procedure, simply using an IF EXISTS after the INSERT using the same criteria as the INSERT in the IF EXISTS(SELECT ...), confirms that it's safe to apply the update. Depending on the severity, you can store the values in a #temp table for a virtual rollback (actual delete) if the update fails @@error.

There are many ways to avoid explicit transactions and all should be considered. Unless you enjoy 3am phone calls or have a cursor to kill all spids, explicit transactions are never a good idea.

In closing, you make a good point on the distinctions between error handling and transactions. This is the often abused an often reversed approach.

Adam

---

Dibble and dabble but please don't babble.

Answer 14

I meant an article, a book, or a forum that says Explicit transactions should be avoided.
Let me know what is the best solution forthe following two scenarios. Errors are not the only thing we face. Loss of power is one thing. Computer crash is another.

DECLARE @t TABLE(EmployeeID INT,FName VARCHAR(20),LName VARCHAR(20))

--I want them both to fail or both succeed
BEGIN TRAN
INSERT INTO @t(EmployeeID,FName,LName) VALUES(1,'John','Smith')
INSERT INTO @t(EmployeeID,FName,LName) VALUES(2,'Mike','Jones')
COMMIT TRAN

SELECT * FROM @t

--Example 2
DECLARE @t2 TABLE(CustomerID INT,Checking DECIMAL(5,2),Saving DECIMAL(5,2))
INSERT INTO @t2(CustomerID,Checking,Saving) VALUES(123,500.22,200)

--I want them both to fail or both succeed
BEGIN TRAN
UPDATE @t2 
SET Checking = Checking + 100

UPDATE @t2
SET Saving = Saving - 100
COMMIT TRAN

SELECT * FROM @t2

---

Abdallah El-Chal, PMP, ITIL, MCTS

Answer 15

Any source that you would deem more reputable than another?

Ask Arnie Rowland Moderator/MVP/Phd

To generally answer your question on those simple examples, you insert rows into a temp table, perform actions on the temp table and insert.

Adam

---

Dibble and dabble but please don't babble.

Answer 16

Also,

Let me elaborate a bit...

In "most" cases, what is viewed as transactional behavior can be implemented inside a trigger through basic logic. The main problem with this is that many companies discourage the use of triggers, in some cases out of fear caused by ignorance, and in other cases to keep a neat stored procedure for maintenance reasons without having to look at a trigger. In either cases, it's lazy programming, and the trade-off is the potential to not only lock the table, but create a domino locking effect.

Much of this is rooted to the set-based mindset that DBA's are taught and practice, when the irony is that a database is an object-oriented environment and should be viewed as such and coded as such. Trying to stuff all of your possessions into one pocket will have its drawbacks.

If you're not satisfied with this, pick one of the 3 samples above and I'll take a few minutes to rewrite it without locking anything, but be aware, it does require a little extra work to acheive the same result but with a lot less risk.

Adam

---

Dibble and dabble but please don't babble.

Answer 17

So I no need lock any thing. I can put my code in between

BEGIN TRAN

....
COMMIT


that enough right?

Answer 18

The way your logic is setup, you want to insert before update not the other way around. Correct?

If you use BEGIN TRAN, you're locking. Get rid of the entire "BEGIN TRAN COMMIT" That is only used in management studio by an analyst not in a stored proc.

Edit: Please use a practical example with real tables.

Adam

---

Dibble and dabble but please don't babble.

Answer 19

Use transactions(BEGIN TRAN COMMIT TRAN or ROLLBACK TRAN) if you are modifying the table and you want all the code to succeed or fail, as the examples I provided above.
When you try to the UPDATE, an exclusive lock is acquired on the resource(table) and that can't be shared with any other lock, so your table is locked until the update is done. Once the update is done, the lock is released the SELECT can access the table.

I hope this is helpful.

---

Abdallah El-Chal, PMP, ITIL, MCTS

Answer 20

SQL Server will lock the table for the update. It has to. There's absolutely no reason to do it explicitly unless you like redundant bad design and permanent table locks that will freeze up your server.

I don't think the OP wants to UPDATE the table first then INSERT. I believe it's backwards.

Abdshall and OP, if you can provide a practical example, I can help you understand how to handle it properly.

Adam

---

Dibble and dabble but please don't babble.