March 3, 2010 at 2:08 am
Im updating a row1 in a table , say in session 1 using begin tran but i dint committed or rolledback.
Im updating row2 which exist in different page compared with row1 in session 2. Even though im updating a different row with different page in session 2, Why session 2 is waiting for session1 to complete???Is this the concurrency rule ??? I guess default lock is page lock for update???
Regards,
Saravanan
March 3, 2010 at 3:36 am
Could be few reasons. If your table has no index that it can use to locate the record, then it needs to do a table scan. In that case the locked record from the unclosed transaction should block the table scan. Another scenario is that you are updating the column that the index that is used to locate the record is based on it. Could also be because of none default isolation level. Good chance that other will come up with other reasons that are valid.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 3, 2010 at 4:32 am
Hi
If the transaction is open, It wont allow you to even select records (Which requires shared lock only)from that table from another session. Update is out of question.
create table tabb1 (c1 int, c2 int)
insert into tabb1 values (1,1)
insert into tabb1 values (2,2)
begin transaction tr1
update tabb1 set c2=11 where c1=1
Try it out.
March 3, 2010 at 4:54 am
Hi ,
My question is can i update the same table like below in another session....without commiting or rollbacking the session 1.
update tabb1 set c2=22 where c1=2
Just imagine the rows are in different page!!
Regards,
Saravanan
March 3, 2010 at 10:06 am
Saravanan T (3/3/2010)
Hi ,My question is can i update the same table like below in another session....without commiting or rollbacking the session 1.
update tabb1 set c2=22 where c1=2
Just imagine the rows are in different page!!
In my previous answer I wrote you few things that can cause an open transaction to block an update statement on the same table but on different rows that are located on different pages. From that you can understand that it can happen. Of course it doesn’t mean that each open transaction will block every operation that effects different rows on different pages, it just mean that it can happen that it depends on few factors.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 3, 2010 at 10:10 pm
Thks. can u tell me those factors????
Regards,
Saravanan
March 4, 2010 at 1:05 am
Just read my first post again. If you have any question about it, I’ll be glad to answer them.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply