January 21, 2003 at 12:39 pm
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 24, 2003 at 8:00 am
This was removed by the editor as SPAM
January 24, 2003 at 8:35 am
quote:
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.
Could you post some of the sp_who2 results, so we can see who the owner of the spid is...sometimes, internal processes issue table locks on system tables, but this isn't always a bad thing...
January 24, 2003 at 12:35 pm
Hi, I know that it is one of my other servers that is causing the locking because of the hostname and loginame. I have also found out the following information from a person on another forum, I've pasted it below, it definately helps in understanding what is happening.
Thanks
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. 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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply