by I could be wrong since I didn't read word for word your entire post. But if
you are saying that SQL Server is issuing a table lock while inserting those
rows then that's perfectly normal. SQL Server will (when it thinks best)
upgrade the locks from a ROW level, to Page level or even Table level. If
you are inserting enough rows into a table it is more efficient to lock the
whole table than each individual row. With no indexes it may even go right
to a table level lock. By the way, there is no need to wrap that insert in a
transaction as it is ATOMIC as is.
--
Andrew J. Kelly
Targitmail.com
> We have interesting problem which can be easely reproduced on our
> configuration NT4 + SP6A/ SQL7+SP3 / MDAC2.6
> Database locks up the table after inserting aproximetly 3000 rows. It
looks
> like a bug in MSSql server.
> This happends if we try to call stored procedure which execute just one
SQL
> statement - inser into a table.
> This table does not have any indexes and consist of the only column:
> MFC programm which calls this stored procedure from threads you can find
in
> attachments
> (you should change connection string - DBTestDlg.cpp:line 18).
> --------------------------------------------------------------------------
--
> --
> create table tst
> (
> c2 varchar(2048) NOT NULL
> )
> --------------------------------------------------------------------------
--
> ----
> Before start the test you should trancate this table and shrink your
> database:
> truncate table tst
> DBCC SHRINKDATABASE ([DBNAME])
> stored proceduri is:
> --------------------------------------------------------------------------
--
> ----
> create procedure tst_sp
> as
> SET DEADLOCK_PRIORITY LOW
> SET LOCK_TIMEOUT 2000
> begin
> BEGIN TRANSACTION
> end
> ROLLBACK TRANSACTION
> ELSE
> COMMIT
> SET NOCOUNT OFF
> --------------------------------------------------------------
> Thread function jast do a loop:
> while(!m_bStop)
> {
> try
> {
> if(!(_con.BeginTrans()))
> {
> pDlg->addMessage("Can not begin transaction");
> }
> _con.ExecuteSQL("{call tst_sp}");
> if(!(_con.CommitTrans()))
> {
> pDlg->addMessage("Can not commit transaction");
> }
> }
> catch(CDBException& e)
> {
> //DebugBreak();
> pDlg->addMessage(e.m_strError);
> if(!(_con.Rollback()))
> {
> pDlg->addMessage("Can not rollback transaction");
> }
> _con.Close();
> _con.OpenEx(CONNECTION_STR,8);
> }
> catch(CDBException* e)
> {
> //DebugBreak();
> pDlg->addMessage(e->m_strError);
> e->Delete();
> if(!(_con.Rollback()))
> {
> pDlg->addMessage("Can not rollback transaction");
> }
> _con.Close();
> _con.OpenEx(CONNECTION_STR,8);
> }
> catch(EXCEPTION_POINTERS& pep)
> {
> //DebugBreak();
> char buffer[100];
> _ltoa(pep.ExceptionRecord->ExceptionCode, buffer, 16);
> pDlg->addMessage(CString("SEH exception: ")+=buffer);
> if(!(_con.Rollback()))
> {
> pDlg->addMessage("Can not rollback transaction");
> }
> _con.Close();
> _con.OpenEx(CONNECTION_STR,8);
> }
> catch(...)
> {
> //DebugBreak();
> pDlg->addMessage(CString("Unknown exception"));
> if(!(_con.Rollback()))
> {
> pDlg->addMessage("Can not rollback transaction");
> }
> _con.Close();
> _con.OpenEx(CONNECTION_STR,8);
> }
> Sleep(10);
> }
> //-----------------------------------------------------------------------