We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

RowLock and Transaction

zahaby
zahaby asked
on
Medium Priority
1,091 Views
Last Modified: 2012-06-21
I am trying to do the following scenario

1- open 2 instance of the Query Analyzer
2- In one of them I would like to write something like this transaction

use northwind
BEGIN TRAN
insert into emplyees (fristname,lastname) values ('koko','wawa')


2- in the second Query analyzer I would like to write this statment
select * from employees

when I try to do this it hangs till I commit or rollback the first QA trans
how can I do it ?
thanks very much
Comment
Watch Question

Commented:
That's what it is supposed to do, so you don't see data that hasn't been comitted, here are 2 ways around it

Select * FROm employees WITH (READPAST) will skip any rows being used in a transaction

Select * FROm employees WITH (NOLOCK) should read any uncommited data, allowing you to do a dirty read

Author

Commented:
the Idea is I cannt change the select in the other QA

Imagine that I have other connection to the sql server ; with select which I cant change it

what can I do ; to not lock them ?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2004

Commented:
The whole idea of a transaction is you start it and end it as FAST as possible to avoid contention.  If you can't change your select statement to add a hint, you need to change your update to not hold a uncommitted transaction so long...

Commented:
Agree with Arbert, if your transaction is taking so long as so as to block reads, it should be optimized

Author

Commented:
can I use the hint with the insert stament ?

Author

Commented:
dfiala13

I tried
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANS

but also locks :((((
what can I do ?

Commented:
You're correct.  I can repeat the same behavior.  

Also tried with table hint UPDLOCK and ROWLOCK

insert into emplyees with (UPDLOCK, ROWLOCK) (fristname,lastname) values ('koko','wawa')

The problem is that the SELECT statement wants to lock the table and since it cannot obtain an exclusive lock it is blocking until it can

Can make it work by playing with the select statement as discussed above, but cannot do it solely from the the insert side. Sorry.

Author

Commented:
:((((

are you sure that I cannt make anything ?!!!!!!

ok do you think can I change the connection prop. itself to read uncommited

I mean in the second Q_A if I write
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
any select comes after this level will read dirty data .

can I make the connection default level to this level ???!!!!!!!!

Commented:
Not that I know of.  Even if you could find a way to do so, it would be very dangerous as all your reads would be dirty for your entire database.  This could lead to lots and lots of unexpected inconsistencies.  There is a reason that SQL Server has transaction isolation, you should be very, very careful when you attempt to bypass it even at a localized level.

I go back to Arbert's original statement, fix your insert so it doesn't run as long.
Top Expert 2004

Commented:
just curious, why can't you change the insert statement?  Is the insert statement running in  a stored proc?

Author

Commented:
I can change the insert statment

but cannt change the select statment

incase I change it into
insert into emplyees with (UPDLOCK, ROWLOCK) (fristname,lastname) values ('koko','wawa')

it doesnt differ

the select still cannt run
Top Expert 2004

Commented:
oops, I meant the select statement--why can't it be changed?

Author

Commented:
the Idea is I cannt change the select statement

Imagine that I have other connections to the sql server ; with select which I cant change it
(or actuly I dont know it )

:(((
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.