June 11, 2003 at 3:38 am
Can someone explain to me the way in which COM+ (COMplus) transactions map to SQL Server transactions, with a view to answering the following:
If I have a VB6 COM+ (COMplus) object that is marked as using transactions, and that object makes a call via ADO to a stored proc on SQL Server 2K which has the following form:
SELECT * FROM MyTable WITH (UPDLOCK) WHERE myCol = @SomeValue
Would you expect locks to be held on the selected rows after the stored procedure returns back to the VB COM+ object? I thought that COM+ transactions support the notion of ACID so would ensure that the locks would be held, or am I being too naïve? If the locks are not held is there a way I can force them to be held--I need to implement a solution that enables me to lock the rows I am interested in while my COM+ object does a few tasks on the data returned to it by the stored proc before finally updating the selected data via an ADO call to another stored proc.
June 11, 2003 at 8:34 am
Hi
Why dont you do a simple test? 🙂
COM+ uses a isolation mode of serialisable, I would expect the locks to be held in context of the whole transaction. It requires some testing to ensure this is the case though and I would love to hear the results.
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
June 11, 2003 at 9:53 am
Hi Ck,
Well I have done the tests--of course I might have done something wrong, but my tests suggest that the locks are not actually held. The only thing I could think of doing was to put a loop in the VB code just that simply counts upto a large number and writes out to a file as it goes--this code was placed straight after the code that calls the SELECT stored proc with the UPDLOCK. I then ran sp_lock in a query analyser window--I knew when to do this since the CPU goes to 99% on task manager because of the tight VB loop above. Anyway, sp_lock shows that there are no locks against the rows selected. I know that the locks are around just before the stored proc terminates since I put a WAITFOR delay at the stored proc and ran sp_lock again, but these locks are no longer around when execution returns to the VB component. Of course there might be something wrong with the way I have implemented my tests--if so, please let me know.
Regards,
Doobri
June 11, 2003 at 11:29 am
If you want to post the VB code, I can take a look. We have successfully used COM+/MTS components here for a while and have gone through the gammet of weird MTS errors and hangups. Just be sure that you are properly using SetComplete and SetAbort on the ObjectContext and that your error handling is appropriately setting the aforementioned properties...
Edited by - jpipes on 06/11/2003 11:29:49 AM
June 13, 2003 at 2:08 am
Hi
Just ran some tests in COM+.
I wrappered two calls to stored procs, one did the updlock hint over a 10row table and the other proc had the waitfor in it. Inside on the com+ trans I see a single shared intent table lock over the table and thats it. You can also repeat this without com+ in query analyser and setting the isolation level to serialisable, begin tran and going from there.
Using the query analyser method:
serializable
<single shared intent table lock > (as I see also using COM+)
read committed and uncommitted
<exclusive page locks, update row locks>
repeatable read
<intent exclusive page>
now, back to serializable. Open another session, and attempt to query the table, it will become blocked with an intent exclsive table lock (i did a select *). So, dont be folled with the locks shown, its serialisable alright and other trans will be adversely effected.
The locks mentioned "may" differ based on indexes and other DML doing on of course. All requires some investigation.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply