Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Concurrency in Update. Expand / Collapse
Author
Message
Posted Wednesday, March 3, 2010 2:08 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:05 AM
Points: 500, Visits: 725
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
Post #875750
Posted Wednesday, March 3, 2010 3:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 1:28 AM
Points: 2,105, Visits: 5,393
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/
Post #875791
Posted Wednesday, March 3, 2010 4:32 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, May 11, 2014 8:07 PM
Points: 891, Visits: 235
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.






Post #875821
Posted Wednesday, March 3, 2010 4:54 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:05 AM
Points: 500, Visits: 725
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
Post #875833
Posted Wednesday, March 3, 2010 10:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 1:28 AM
Points: 2,105, Visits: 5,393
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/
Post #876135
Posted Wednesday, March 3, 2010 10:10 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:05 AM
Points: 500, Visits: 725
Thks. can u tell me those factors????

Regards,
Saravanan
Post #876542
Posted Thursday, March 4, 2010 1:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 1:28 AM
Points: 2,105, Visits: 5,393
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/
Post #876618
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse