Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

RowLock and Transaction

Posted on 2004-03-21
14
Medium Priority
?
1,055 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
0
Comment
Question by:zahaby
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 12

Expert Comment

by:dfiala13
ID: 10645170
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

0
 
LVL 3

Author Comment

by:zahaby
ID: 10645228
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 ?
0
 
LVL 12

Accepted Solution

by:
dfiala13 earned 100 total points
ID: 10645247
Didn't say which side you needed to solve. :)

Yes you can begin your transaction differently...

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANS

Note doing either of these things can result in non-repeatable reads.  
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 34

Expert Comment

by:arbert
ID: 10645249
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...
0
 
LVL 12

Expert Comment

by:dfiala13
ID: 10645262
Agree with Arbert, if your transaction is taking so long as so as to block reads, it should be optimized
0
 
LVL 3

Author Comment

by:zahaby
ID: 10645287
can I use the hint with the insert stament ?
0
 
LVL 3

Author Comment

by:zahaby
ID: 10645309
dfiala13

I tried
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANS

but also locks :((((
what can I do ?
0
 
LVL 12

Expert Comment

by:dfiala13
ID: 10645457
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.
0
 
LVL 3

Author Comment

by:zahaby
ID: 10645503
:((((

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 ???!!!!!!!!
0
 
LVL 12

Expert Comment

by:dfiala13
ID: 10645539
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.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10645602
just curious, why can't you change the insert statement?  Is the insert statement running in  a stored proc?
0
 
LVL 3

Author Comment

by:zahaby
ID: 10645609
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
0
 
LVL 34

Expert Comment

by:arbert
ID: 10645682
oops, I meant the select statement--why can't it be changed?
0
 
LVL 3

Author Comment

by:zahaby
ID: 10645692
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 )

:(((
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question