why 2 session locks each other, when they update separate rows in tab

[ismotuononen]

is it possibly to execute next scenarion with sqlserver?

and if it is possible, how?

I have tried "Read commited snapshot", nolock, readpast and all kind of combinations, but nothing seems to work properly.

both sessions runs same time with separed programs and both session have "autocommit off" mode

What I want to do:


SESSION 1:

create table a(a varchar(2));
insert into a values('1');
insert into a values('2');
commit;
select * from a;

A
-
1
2

2 row(s)

SESSION 2:

select * from a;

A
-
1
2

2 row(s)

SESSION 1:

update a set a='3' where a='1';

Processed: 1

SESSION 2:

update a set a='4' where a='2';

Processed: 1

SESSION 1:

select * from a;

A
-
3
2

2 row(s)

SESSION 2:
select * from a;

A
-
1
4

2 row(s)

# so both session saw only own updates and committed data.
# and both sessions can do those updates, they don't lock each other, because they don't handle same rows.

SESSION 2:

commit;

SESSION 1:

select * from a;

A
-
3
4

2 row(s)

SESSION 2:

select * from a;

A
-
1
4

2 row(s)

SESSION 1:

commit;

SESSION 2:

select * from a;

A
-
3
4

2 row(s)

# so only after COMMIT data chances.
but befoce commit both sessions can read committed data and own updates.

BUT if sessions try to update same row, then session must lock (or give exception)

the real problem aren't this simple, but this scenario shows whats is the problem. that scenario works all other databases, but I can't get it working in sqlserver.

I am using:

Microsoft SQL Server 9.00.1406
Using Microsoft SQL Server 2005 JDBC Driver 1.2.2828.100

Ismo

 

[danvlas]

1. As far as I know, you can't get 'commited' data from an uncommited transaction. You can get the current values with nolock or ignore not commited by readpast.

2. When you do a 'select * from a' you actually place shared locks on all records from a, so you can prevent inserts, updates and deletes until the select has been done.

Hope this helps


Daniel Vlas
Systems Consultant

 

[ismotuononen]

Don't help.

because I don't neet those reads, those are there just to show what is in database.

sessions locks even if I say just:

create table a(a varchar(2));
insert into a values('1');
insert into a values('2');
commit;

SESSION 1:

update a set a='3' where a='1';

Processed: 1

SESSION 2:

update a set a='4' where a='2';

# now this is locked, it don't do anything before SESSION 1 is committed.

if I use readpast SESSION 2 goes thru, but problem is that if SESSION 2 says:

update a set a='8' where a='1;

it don't get any line, even when there is line where a='1' because SESSION 1 have not committed.

real problem is much more complex, and there update is different column than "search" column, but the problem is same: sessions lock each other even when they are not updateing same rows. and I can't use readpast. because sessions are sometimes updating same rows.

Ismo

 

[ismotuononen]

this example is perhaps better:

create table a(a varchar(2));
insert into a values('1');
insert into a values('2');
commit;

SESSION 1:

update a set a='3' where a='1';

Processed: 1

SESSION 2:

update a set a='4' where a='2';

Processed: 1

SESSION 3:

update a set a='4' where a='1';

# this prosess stops, because it update same row as prosess 1.

So I need to know how prosesses can update different rows in same table whitout locking eachother, but if prosesses update same rows, those must lock each other.

Ismo

 

[mrdenny]

Correct SQL Server will block updates even if different rows are updated.

By default SQL Server takes locks at the page level. Because of this if two different records are stored within the same data page the second session will be locked until the first session has completed its transaction.

You can override this by forcing row level locking by using the ROWLOCK table hint.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[ismotuononen]

And how force sqlserver to use rowlock?

both sessions have autocommit off, and both have said commit before next scenarion

SESSION 1:

Sql> update a with (rowlock) set a='3' where a='1';

Processed: 1
Sql>

SESSION 2:

Sql> update a with (rowlock) set a='4' where a='2';

# and this session stops here until session 1 commits.

Is it possible to set such parameters? hints? ???? that that scenario is possible?

 

[danvlas]

Not tested, but you may try aliasing in the update:

Update a1
Set a='3'
From a with (rowlock) where a='1'

Your scenario looks like almost forcing a deadlock and I repeat: I don't think you can read the initial values from an uncommited transaction that modified those values






Daniel Vlas
Systems Consultant

 

[ismotuononen]

don't work. it locks.

and I get :
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'a1'.
Clause: Update a1 Set a='3' From a with (rowlock) where a='1'

but: Update a1 Set a='3' From a as a1 with (rowlock) where a='1';
is ok, is that what You mean? but this locks.

And there are no possibility to deadlocks, because those prosesses update only 1 row in that table

and I can read the uncommitted values, no problem for that.

I have next in database:
ALTER DATABASE pubs
SET READ_COMMITTED_SNAPSHOT ON