LOCK/Wait on Begin Tran and Commit

Question

Hi,

We have a script with insert statements in between BEGIN TRANSACTION and COMMIT.The data from those insert statements should populate few tables. When two of us run the script at the same time. Some of my data and some of other persons data are getting populated. I would like to know the best option to get rid of this issue. Should we use NOLOCK or Wait or any other approach that is the best.Can somebody suggest?

Thanks

---

BRP

Answer 1

How about

insert into table with (TABLOCK) select * from anothertable

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

Hello ,

You cannot use NOLOCK query hint with DML and merge statement.

You can think of using TABLE LOCK in query but need to test it first.

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 3

oh. Yes. But I have 8 tables getting inserted one after the other. So, do u suggest using tablock on each insert statement. Or do u think we should use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

---

BRP

Answer 4

• When two of us run the script at the same time. Some of my data and some of other persons data are getting populated. I would like to know the best option to get rid of this issue.

What issue?  Can you provide sample code and explain what you are trying to achieve?

Two concurrent transactions can typically insert data into the same tables.  Are you trying to prevent that?

David

---

David http://blogs.msdn.com/b/dbrowne/