May 16, 2004 at 1:39 pm
hi,
I have a vb application that runs on a remote machine with respect to
the machine where SQL 2000 is. At times it accesses the sql database
using ActiveX ADO 2.8. When the application run I notice tht wehn i
go to Management--> Current Activity--> Locks/Process ID under the sql
relevant sql instance in SQL server visual interface, I notice that in
the latter folder there are entries testifying to the use of locks.
Thos locks correspond to user views that I have created and they
specify the stored procedure involved in the lock. As far as I can
tell, I made sure that the vb application does not use locks at all
while it runs and the stored procedures are purely select sql
statement that do not embed lock hints. The lock type is DB and the
mode is S.
Can someone explain to me what is going on here -- even though I
ensure that no locks are used neither by the stored procedure nor by
the vb ADO object, there are still indications of locking wherein
stored procedures I have created without any lock hints appear in the
process detail of the lock. Could it be that I overlooked some
variable(s) that place the DB in lock state to the extent that
seemingly 'lockless' stored procedure appears as if it requested a
lock? Can this thing happen?
I would greatly appreciate any feedback on the matter
Avi
May 17, 2004 at 12:14 am
The default isolation level is READ COMMITTED, so if you do not explicitly set this to READ UNCOMMITTED or use NOLOCK hints you will see S locks.
These are generally not a problem, so once you understand why they're ther maybe that's the end. But if you have heavy ins/upd/del activity that is being impeded by the S locks, and you can tolerate the possibility of dirty reads, then consider adding NOLOCK hints.
May 19, 2004 at 9:39 am
Hello Mike,
Thanks for enlightening me about the issue. I appreciate it. Since then. I have read a bit about locks , and I came across an issue that is somewhat unclear -- the issue of lock conversion. Here is my question : under what scenarios, generally speaking, conversion occurs. The obvious scenario that comes to ming is that one statement uses SELECT command and immdetiatly thereafter, if an attempt to update the same record selected follows, then the shared lock needs to convert to exclusive lock. Speaking of that scenarion, what would happen in terms of convesion if between the SELECT and UPDATE commands some other commans not related to the record on whixh shared lock was obtained are inserted. That is, would conversion take place in the event that right after the select statment a DELAY loop will follow and only after the delay the UPDATE statement will follow.
According to the way I understand the issue of locks, the answer on the vagueness of lock conversion I currently have would make it possible for me to stay away from deadlocks -- one scenarion in which deadlocks take place is the convesrion stage where two conncetions try to convert locks from common resources from shared to exclusive.
once again, thanks for your time and effortyou took to answer my questions
Avi
May 19, 2004 at 10:48 am
The lock conversions/escalations are only going to occur within a single transaction. For you to experience this with multiple sql stmts, e.g. SELECT followed by UPDATE they'd all need to be within a tran.
You can have implicit trans turned on via query analyzer option, TSQL SET stmt, or provider (e.g. OpenRowSet() with OLEDB provider). Having implicit trans enabled tends to be very problematic & I'd avoid it--never seen good reason for using this.
Assuming you are not using implicit trans, then only way multiple sql stmts will be in a tran is if you go to the trouble to wrap them in one.
Also, unless you add HOLDLOCK or UPDLOCK hint on your SELECT, even w/in a tran the S lock will vanish after data is obtained, so the lock will not be later converted.
To answer your question (finally): assuming both wrapped inside a tran, and you've used locking hint in your SELECT so the lock persists for the life of the tran, then conversion will not happen on the SELECT, but on the subsequent UPDATE.
I really don't worry about this stuff until I see a problem...most of the BOL discussion here is about internal workings that work great 99% of the time (for me).
May 19, 2004 at 11:31 am
thanks for your propmt response.
what if I have something like this:
BEGIN TRAN
declare (@arbitraydate smalldatetime)
select * from TableOfNAmes where [ss#]='076-54-8734'
set @arbitraydate =getdate()
update TableOfNAmes
set lastname='myMarriageLastName'
END TRAN
I know that the select statement get a shared lock (assuming that the isolation level is READ COMMITTED) and the lock is realeased as soon as possible (that's what literature says). Does ' as soon as possible' means before excution of the 'set @arbitraydate ' line. If so then we would not have any conversion, right? Please correct me if I'm wrong and describe to me what happens in terms of conversion from one type of lock to another.
thank you in adavance
Avi
May 19, 2004 at 11:38 am
S lock is released before the SET stmt.
You can see this for yourself. Within tran, at any point where you'd like to check what locks exist add this code:
exec sp_lock @@spid
PS - Lecturer Kim Tripp showed me this trick earlier this year, very simple & obvious once you know it, but I've been DBA for years & never thought of it.
May 19, 2004 at 12:00 pm
Thus I can make a general observation saying that the shared lock on the select statement with respect to the above example is released as soon as SQL finishes to read ( i.e, through select statement) the record in question, is that right?
Having said that, I read that " If you use a shared lock in a resource lock, and other connection gets another shared lock in the same resource, you can get deadlock situation if both connections tried to convert their shared lock into exclusive locks" (quoted fom SQL server 2000 QUE book).
In light of the quote above and the assertion about when a lock is released (assuming it's true), I would say, and please correct me if I'm wrong, that if we took the example above, the deadlock situtaion will occur if and only if we OMMIT the set @arbitrarydate statement and run the transaction from TWO different connections such that both connections will exceute the select stament at the SAME TIME and try to convert the shared lock at the SAME TIME.
the code will loke like this:
BEGIN TRAN
declare (@arbitraydate smalldatetime)
select * from TableOfNAmes where [ss#]='076-54-8734'
update TableOfNAmes
set lastname='myMarriageLastName'
END TRAN
P.S: sorry if i sound as if I'm being reluctant to get the idea through my mind, but there are some fine details that can not be answered esilt by books.
thanks
Avi
May 19, 2004 at 12:29 pm
In your scenario, there is no lock conversion. S lock is obtained, then released when SELECT is done. UPDATE brings its own new locks. But there is no conversion, no deadlock going to arise from this.
Sitting here thinking about it, I can't come up with any scenario where S lock is converted to X. Perhaps your book is wrong, or uses term "conversion" differently. I may be wrong, if so time for somebody else to step in... I'm just a working DBA, not real expert on locking.
I urge you to try this for yourself, just use pubs & simple stmts, and sprinkle in the "exec sp_lock @@spid" commands, you'll see all the locks.
May 19, 2004 at 12:55 pm
Once again , big thank you for following up on my burning issues, for being a kind person and for the time you took to write back to me and think about the problems.
Avi
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply