January 21, 2003 at 10:32 am
Hi, I have been monitoring locking on my Production database, it is a SQL Server 7.0 db. I am noticing in syslockinfo that there are spids with lock type of TABLE and a mode of IS (Intent Shared). I find this sort of confusing, can anyone explain to me what this really means, are the rows (ref count) in the table locked or because of the intent shared, are they visible to another transaction.
January 23, 2003 at 11:44 pm
Can you provode more info on what you are trying? This is a very ope ended question
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
January 23, 2003 at 11:44 pm
Can you provode more info on what you are trying? This is a very open ended question
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
January 24, 2003 at 3:42 am
quote:
An intent lock indicates that SQL Server wants to acquire a shared or exclusive lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the table level means that a transaction intends on placing shared locks on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive lock on the table containing that page. Intent locks improve performance because SQL Server needs to examine intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.
from BOL.
Basically you see even if you are only reading rows of data (SELECT) you need to lock each row while its being read so that it can;t be modified by another transaction mid read. Now if another transaction wants to update your table it woulde have to check each row it need to see if it was 'Free' for updating. This would be a waste of time it instead it could just check for an Intent lock at the table level.
You see your transaction is basically telling other transactions that it is reading rows from that table and they can read as well but they can't update or delete until your finished. I hope that has pointed you in the direction of some comprehension. I have tried to explain it as simply as possioble but I'll never make a good teacher!
Nigel Moore
======================
January 24, 2003 at 7:14 am
quote:
quote:
An intent lock indicates that SQL Server wants to acquire a shared or exclusive lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the table level means that a transaction intends on placing shared locks on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive lock on the table containing that page. Intent locks improve performance because SQL Server needs to examine intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.from BOL.
Basically you see even if you are only reading rows of data (SELECT) you need to lock each row while its being read so that it can;t be modified by another transaction mid read. Now if another transaction wants to update your table it woulde have to check each row it need to see if it was 'Free' for updating. This would be a waste of time it instead it could just check for an Intent lock at the table level.
You see your transaction is basically telling other transactions that it is reading rows from that table and they can read as well but they can't update or delete until your finished. I hope that has pointed you in the direction of some comprehension. I have tried to explain it as simply as possioble but I'll never make a good teacher!
Yes, Thank you, this is starting to make some sense. You see this is what is happening. I have one transaction that is updating rows in the table and it has taken out a lock_type = 'table' and a req_mode = 'IS Intent Shared'. This transaction's ref_count = 118 , I'm guessing it is attemptin to update 118 rows. Then another transaction attempts to read (SELECT) 2 of these rows, this transaction is being blocked. Does that make sense?
Thanks
E...
January 24, 2003 at 7:18 am
If you select is for a report or something else that just needs to unrepeatable reas at thae point in time then use a NOLOCK hint in the select statment and it will run on anayway even if the other transaction is performing unpdates.
Nigel Moore
======================
January 24, 2003 at 8:19 am
Unfortunately once the select is done the 2 rows have to be updated.
So, just to make sure I understand this. The transaction that has the table lock with intent shared is physically locking the 118 rows it is going to update from being selected by another tanaction.
Thanks...
January 24, 2003 at 8:41 am
Not quite the intent shared prevents updates or deletes by another transaction but you can still read the rows.
Nigel Moore
======================
January 24, 2003 at 8:48 am
Unfortunately, nmoore is correct. About the only thing you can do, if the results of the select statement are being used in an update statement, is to concentrate efforts on alleviating the burden of the transaction which is locking the table in the first place. Look for ways to a) reduce the amount of time the table lock is issued, or b) not issuing the lock until the very last second...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply