Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supported

Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supportedsql

Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supported

Adding Table to Dataset for SQL Server Mobile causes VS 2005 to lock up.

I am running VS 2005 Professional Edition

Windows XP profession with Service Pack 2

SQL Server 2005 Developer Edition.

WHAT I HAVE DONE:

I have a database running in an instance of SQL Server.

I set this up for merge publication and then set up a SQL Server Mobile Edition Subscription to that publication. After a few oversights I got everything working. The Mobile database replicated just fine. I went back verified all data was there. Can make queries to it.

PROBLEM:

I set up a new dataset to use tables from the SQL Server Mobile database. If I drag one of the tables to the dataset, VS 2005 simply stops responding. It is not using any processor. I click any place on the application and I get the Microsoft Visual Studio Delay Notification saying:

Microsoft Visual Studio is Busy.

Microsoft Visual Studio is waiting for an internal operation to complete. If you regularly encounter this delay during normal usage, please report this problem to Microsoft.

Well... It is more than just a delay. The environment is not using any processor its just sitting here. And I left it running for 2.5 hours... so now this is becoming a big source of pain for me because I need to get that dataset working to finish my business logic. The only option I have is to Kill the process.

Hopefully someone out there can help.

Additional Services running:

IIS (Whatever version comes with Windows XP Pro. I think 5.1)

SQL Server Agent, SQL Server Integration Services, SQL Server Broswer and SQL Server FullTextSearch

UPDATE: I am editing this post with an update.

I noticed that my other tables get added to the dataset just fine. It is when I add one particular table that the entire visual studio simply stops and starts giving the delay notification. I have no idea why this happens... nor do I see any noticeable difference between this table and the rest of them. I went back and made sure that all columns types where directly supported by SQL Mobile Edition and they are.

Kevin,

I've seen this in a couple of circumstances:

1. you had a pre-release version of VS2005 (Whidbey) on your dev machine at some point , then installed the RTM, and something is disconnected in the feature that does the automatic BindingSource generation when you add a new database datasource to your project. this, unfortunately, requires a cleanup and reinstall ofVS2005

2. you are accidentally trying to use a SQL CE 2.0 database as the data source

Darren

|||

Darren,

This is a clean install of Visual Studio 2005. No pre release or beta versions have been on this machine.

I created the database a new SQL Server Mobile Edition database and then filled that database via replication of a SQL Server 2005 database.

I am connecting to it via as SQL Server Mobile Edition Data Source.

I did update the first post to add that it is in fact only 1 table that locks up the solution. The other 48 tables load just fine. I don't see any differences in this table from the others except that it has the most columns. It has 52 columns of types int, nvarchar, rowguid, bigint.

Is there anything else you can think of for me to try or check out?

|||

Kevin,

Would you be willing to email me a copy of your sdf file and I can try it in my VS2005 install and maybe see what it is about that one table that can't be modeled into a BindingSource? You can contact me through my blog (just Google me).

-Darren Shaffer

|||

Darren,

I took the database over to another developers PC who is working on another project. He does not have SQL Server installed but does have Visual Studio 2005 Professional running.

We got the exact same results. All tables could be added to the dataset designer by dragging and dropping but the one table caused the Microsot Delay Notification.

We then decided to try to add the table to the designer by right clicking and saying add table. Then added the table via the query statements. It added just fine that way.

So the problem seems to rear its head only during the Drag -N-Drop method of the Data Set Designer. I am still scratching my head as to why this could be.

I'll will send you a copy of this database to checkout for yourself.

|||

Kevin,

Just to give you an update, I did send a copy of your database to Laxmi - we're hoping to get a fix into SP1 of VS2005.

thanks,

Darren

|||

Darren,

Thank you for the update.

Can you please eloborate a little as to what the problem was?

|||

Kevin,

I got the same behavior you did - you try to add the Vehicles table to a DataSet in VS2005 and it sits forever. I tried repairing the database first, and even tried removing the merge subscription from the SQL Mobile database first. Same behavior. The only thing I think might be involved here is that you have several BIGINT columns in that table and these translate to an INT64 in .NET. I'm wondering if the VSD (Visual Studio for Devices) team anticipated the need to support INT64 in the dataset designer for SQL Mobile data sources.

I'll let you know when I hear back from Laxmi on his investigation of the issue.

Darren

|||

Hi Kevin & Darren,

My team mate Mr. Mohit Khullar who was working on this issue has reproduced the problem and found the root cause too. This bug is in the communication of data between desktop designer and device.

Thanks for pointing out the bug. We would try to make this fix into Whidbey SP1.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Ev, Microsoft Corporation

|||I wonder if it's possible to be a little more specific...? I found this thread just doing a Google search for "Microsoft Visual Studio .NET 2005 'Delay Notification'" - Since I seem to get this message every now and then and haven't pinned it down to anything specific that causes it. My co-worker just walked up and I showed it to him and asked if he's ever seen it, and he said Yup, lots... Also, it happens on different machines... My home PC, my 2 work PCs, and my VMs.

Sometimes it comes back after a bit.. Sometimes (like now) it doesn't! (well, I've waited about 20 minutes.. I figure that's long enough!) ;) Nothing else is open and the CPU is more or less totally idle according to the Task Manager.
It would be nice to know what's causing it, so I can try to avoid certain behaviours.
I have to say that it's Excessively Lame that it says "If you regularly encounter this delay during normal usage please report this prolem to Microsoft." -- But it doesn't say to who, or what email, or what phone number I should report this to! Making me search for something that should have been known to whoever wrote that "Delay Notification" dialog is, as I say.. Lame. :(

Visual Studio 2005 in General ROCKS. This is just one particular very annoying (to me) thing.
Also, the PC is not directly connected to the Internet, so even though when I force-kill it with Task Manager and it asks me to "Please tell Microsoft about this problem." - I can't send it, and it doesn't give me any other way to do so! - That also is another thing about MS products that I find Particularly annoying!

Thanks
- Andrew

PS - Am I EVER glad I selected all and copied before I hit Post! - It just refreshed the screen or something and I lost my entire message! :( Try again... (edit: at least it worked the 2nd time!)
|||

Hi Laxmi,

I thought I would let you know that this problem is NOT reserved just for mobile server connections. This problem is very common for straight Windows Applications. I receive this message VERY often. I thought it was due to my converting a VS2003 solution across to VS2005, but that is not the case either. Although, to clarify, converting a VS2003 solution to VS2005 does seem to cause this message to be raised more often than from a brand new creation of a solution.

I hope this helps identify the bug more specifically.

cheers,

Mark Chimes

|||

I think there has been lot of confusion about the context of this thread. There is a particular table schema and when this table is added to DataSet, VS is locked up. There can be other cases of VS locking up which are in no way related to this thread. So, please just dont take this thread as just "VS Locking up" case rather take it as "VS Locking up for a particular SQL Mobile Table Schema".

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere Edition, Microsoft Corporation

|||no confusion occuring except when you say the error is restricted to '...a particular table schema'. i am experiencing the exact same problem in a web app i am busy with. i drag a dataset from the toolbox which then creates a default datatable...WITH an adapter ...et voila! i cant say for sure where the designer is trying to link the table adapter to but the result is a dead-end for vs.|||

Are you still facing the same problem with Whidbey SP1 or Whidbey SP1 Beta?

Thanks,

Laxmi

Adding Table to Dataset for SQL Server Mobile causes VS 2005 to lock up.

I am running VS 2005 Professional Edition

Windows XP profession with Service Pack 2

SQL Server 2005 Developer Edition.

WHAT I HAVE DONE:

I have a database running in an instance of SQL Server.

I set this up for merge publication and then set up a SQL Server Mobile Edition Subscription to that publication. After a few oversights I got everything working. The Mobile database replicated just fine. I went back verified all data was there. Can make queries to it.

PROBLEM:

I set up a new dataset to use tables from the SQL Server Mobile database. If I drag one of the tables to the dataset, VS 2005 simply stops responding. It is not using any processor. I click any place on the application and I get the Microsoft Visual Studio Delay Notification saying:

Microsoft Visual Studio is Busy.

Microsoft Visual Studio is waiting for an internal operation to complete. If you regularly encounter this delay during normal usage, please report this problem to Microsoft.

Well... It is more than just a delay. The environment is not using any processor its just sitting here. And I left it running for 2.5 hours... so now this is becoming a big source of pain for me because I need to get that dataset working to finish my business logic. The only option I have is to Kill the process.

Hopefully someone out there can help.

Additional Services running:

IIS (Whatever version comes with Windows XP Pro. I think 5.1)

SQL Server Agent, SQL Server Integration Services, SQL Server Broswer and SQL Server FullTextSearch

UPDATE: I am editing this post with an update.

I noticed that my other tables get added to the dataset just fine. It is when I add one particular table that the entire visual studio simply stops and starts giving the delay notification. I have no idea why this happens... nor do I see any noticeable difference between this table and the rest of them. I went back and made sure that all columns types where directly supported by SQL Mobile Edition and they are.

Kevin,

I've seen this in a couple of circumstances:

1. you had a pre-release version of VS2005 (Whidbey) on your dev machine at some point , then installed the RTM, and something is disconnected in the feature that does the automatic BindingSource generation when you add a new database datasource to your project. this, unfortunately, requires a cleanup and reinstall ofVS2005

2. you are accidentally trying to use a SQL CE 2.0 database as the data source

Darren

|||

Darren,

This is a clean install of Visual Studio 2005. No pre release or beta versions have been on this machine.

I created the database a new SQL Server Mobile Edition database and then filled that database via replication of a SQL Server 2005 database.

I am connecting to it via as SQL Server Mobile Edition Data Source.

I did update the first post to add that it is in fact only 1 table that locks up the solution. The other 48 tables load just fine. I don't see any differences in this table from the others except that it has the most columns. It has 52 columns of types int, nvarchar, rowguid, bigint.

Is there anything else you can think of for me to try or check out?

|||

Kevin,

Would you be willing to email me a copy of your sdf file and I can try it in my VS2005 install and maybe see what it is about that one table that can't be modeled into a BindingSource? You can contact me through my blog (just Google me).

-Darren Shaffer

|||

Darren,

I took the database over to another developers PC who is working on another project. He does not have SQL Server installed but does have Visual Studio 2005 Professional running.

We got the exact same results. All tables could be added to the dataset designer by dragging and dropping but the one table caused the Microsot Delay Notification.

We then decided to try to add the table to the designer by right clicking and saying add table. Then added the table via the query statements. It added just fine that way.

So the problem seems to rear its head only during the Drag -N-Drop method of the Data Set Designer. I am still scratching my head as to why this could be.

I'll will send you a copy of this database to checkout for yourself.

|||

Kevin,

Just to give you an update, I did send a copy of your database to Laxmi - we're hoping to get a fix into SP1 of VS2005.

thanks,

Darren

|||

Darren,

Thank you for the update.

Can you please eloborate a little as to what the problem was?

|||

Kevin,

I got the same behavior you did - you try to add the Vehicles table to a DataSet in VS2005 and it sits forever. I tried repairing the database first, and even tried removing the merge subscription from the SQL Mobile database first. Same behavior. The only thing I think might be involved here is that you have several BIGINT columns in that table and these translate to an INT64 in .NET. I'm wondering if the VSD (Visual Studio for Devices) team anticipated the need to support INT64 in the dataset designer for SQL Mobile data sources.

I'll let you know when I hear back from Laxmi on his investigation of the issue.

Darren

|||

Hi Kevin & Darren,

My team mate Mr. Mohit Khullar who was working on this issue has reproduced the problem and found the root cause too. This bug is in the communication of data between desktop designer and device.

Thanks for pointing out the bug. We would try to make this fix into Whidbey SP1.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Ev, Microsoft Corporation

|||I wonder if it's possible to be a little more specific...? I found this thread just doing a Google search for "Microsoft Visual Studio .NET 2005 'Delay Notification'" - Since I seem to get this message every now and then and haven't pinned it down to anything specific that causes it. My co-worker just walked up and I showed it to him and asked if he's ever seen it, and he said Yup, lots... Also, it happens on different machines... My home PC, my 2 work PCs, and my VMs.

Sometimes it comes back after a bit.. Sometimes (like now) it doesn't! (well, I've waited about 20 minutes.. I figure that's long enough!) ;) Nothing else is open and the CPU is more or less totally idle according to the Task Manager.
It would be nice to know what's causing it, so I can try to avoid certain behaviours.
I have to say that it's Excessively Lame that it says "If you regularly encounter this delay during normal usage please report this prolem to Microsoft." -- But it doesn't say to who, or what email, or what phone number I should report this to! Making me search for something that should have been known to whoever wrote that "Delay Notification" dialog is, as I say.. Lame. :(

Visual Studio 2005 in General ROCKS. This is just one particular very annoying (to me) thing.
Also, the PC is not directly connected to the Internet, so even though when I force-kill it with Task Manager and it asks me to "Please tell Microsoft about this problem." - I can't send it, and it doesn't give me any other way to do so! - That also is another thing about MS products that I find Particularly annoying!

Thanks
- Andrew

PS - Am I EVER glad I selected all and copied before I hit Post! - It just refreshed the screen or something and I lost my entire message! :( Try again... (edit: at least it worked the 2nd time!)
|||

Hi Laxmi,

I thought I would let you know that this problem is NOT reserved just for mobile server connections. This problem is very common for straight Windows Applications. I receive this message VERY often. I thought it was due to my converting a VS2003 solution across to VS2005, but that is not the case either. Although, to clarify, converting a VS2003 solution to VS2005 does seem to cause this message to be raised more often than from a brand new creation of a solution.

I hope this helps identify the bug more specifically.

cheers,

Mark Chimes

|||

I think there has been lot of confusion about the context of this thread. There is a particular table schema and when this table is added to DataSet, VS is locked up. There can be other cases of VS locking up which are in no way related to this thread. So, please just dont take this thread as just "VS Locking up" case rather take it as "VS Locking up for a particular SQL Mobile Table Schema".

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere Edition, Microsoft Corporation

|||no confusion occuring except when you say the error is restricted to '...a particular table schema'. i am experiencing the exact same problem in a web app i am busy with. i drag a dataset from the toolbox which then creates a default datatable...WITH an adapter ...et voila! i cant say for sure where the designer is trying to link the table adapter to but the result is a dead-end for vs.|||

Are you still facing the same problem with Whidbey SP1 or Whidbey SP1 Beta?

Thanks,

Laxmi

Adding INDEX causes DEADLOCKS?

I have a very small table (50 rows, 50 columns).
I have 4 servers using the table:
1 server updates the table ("UPDATE table SET col1='', col2='',
LastUpdate=GetDate()").. about 20 rows updated a second.
3 servers pull data ("SELECT * FROM table Where
LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
times a second each.
Prior to last week, i did not have the lastupdate column. but it
obviously made sense to me to add it (why pull everything every 100ms
when you can only pull what you need via the LastUpdate, maybe 3-4
rows at a time). so i add the lastupdate column and make an index on
it.
now as soon as i run it, every 2-4 minutes i get a deadlock (!).
execution path is showing a bookmark lookup with index seek because i
have two conditions in the where clause: "lastupdate>(time
oflastrequest) and COL3>0". i have tried making an index based upon
both lastUpdate,Col3 but the results are the same (lots of deadlocks)
I have removed the index, kept the lastupdate column, same queries,
and everything works fine -- except, of course, that it is doing a
table scan for every lookup. the table will be growing soon and i
will be in trouble. so i need some help here, any ideas?
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
You update the whole table every time? Why?
|||"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.co m...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
Easiest way to solve these problems is with more locking. I know, it sounds
weird, but more locking will separate the deadlocking processes and properly
serialize them. I suggest in your Update statement add a TABLOCKX hint.
That will exclusively lock the entire table for the duration of the update.
This will also give you more predictable results as the table will only be
read when it is in a completely consistent state between updates.
David
|||The deadlock is due to that for the 3 server pulling data, it needs to get
page/row lock on the non-clustered index first, then get page/row lock on
the base table(while holding the lock on the non-clustered index), while the
update gets lock on base table first, then the lock on page/row in the
non-clustered index. To break the circle, you can either create a clustered
index on the LastUpdate field instead of a non-clustered index, or either
specify TABLOCKX hint for the update or specify readpast lock hint for the
reader.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.co m...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
|||"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:u4Gn6SuQEHA.2408@.tk2msftngp13.phx.gbl...
> The deadlock is due to that for the 3 server pulling data, it needs to get
> page/row lock on the non-clustered index first, then get page/row lock on
> the base table(while holding the lock on the non-clustered index), while
the
> update gets lock on base table first, then the lock on page/row in the
> non-clustered index. To break the circle, you can either create a
clustered
> index on the LastUpdate field instead of a non-clustered index, or either
> specify TABLOCKX hint for the update or specify readpast lock hint for the
> reader.
> --
> Gang He
> SQL Server Storage Engine Development
Good info. Thanks for the post Gang.
David

Adding INDEX causes DEADLOCKS?

I have a very small table (50 rows, 50 columns).
I have 4 servers using the table:
1 server updates the table ("UPDATE table SET col1='', col2='',
LastUpdate=GetDate()").. about 20 rows updated a second.
3 servers pull data ("SELECT * FROM table Where
LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
times a second each.
Prior to last week, i did not have the lastupdate column. but it
obviously made sense to me to add it (why pull everything every 100ms
when you can only pull what you need via the LastUpdate, maybe 3-4
rows at a time). so i add the lastupdate column and make an index on
it.
now as soon as i run it, every 2-4 minutes i get a deadlock (!).
execution path is showing a bookmark lookup with index seek because i
have two conditions in the where clause: "lastupdate>(time
oflastrequest) and COL3>0". i have tried making an index based upon
both lastUpdate,Col3 but the results are the same (lots of deadlocks)
I have removed the index, kept the lastupdate column, same queries,
and everything works fine -- except, of course, that it is doing a
table scan for every lookup. the table will be growing soon and i
will be in trouble. so i need some help here, any ideas?> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
You update the whole table every time? Why?|||"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.com...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
Easiest way to solve these problems is with more locking. I know, it sounds
weird, but more locking will separate the deadlocking processes and properly
serialize them. I suggest in your Update statement add a TABLOCKX hint.
That will exclusively lock the entire table for the duration of the update.
This will also give you more predictable results as the table will only be
read when it is in a completely consistent state between updates.
David|||The deadlock is due to that for the 3 server pulling data, it needs to get
page/row lock on the non-clustered index first, then get page/row lock on
the base table(while holding the lock on the non-clustered index), while the
update gets lock on base table first, then the lock on page/row in the
non-clustered index. To break the circle, you can either create a clustered
index on the LastUpdate field instead of a non-clustered index, or either
specify TABLOCKX hint for the update or specify readpast lock hint for the
reader.
--
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.com...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?|||"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:u4Gn6SuQEHA.2408@.tk2msftngp13.phx.gbl...
> The deadlock is due to that for the 3 server pulling data, it needs to get
> page/row lock on the non-clustered index first, then get page/row lock on
> the base table(while holding the lock on the non-clustered index), while
the
> update gets lock on base table first, then the lock on page/row in the
> non-clustered index. To break the circle, you can either create a
clustered
> index on the LastUpdate field instead of a non-clustered index, or either
> specify TABLOCKX hint for the update or specify readpast lock hint for the
> reader.
> --
> Gang He
> SQL Server Storage Engine Development
Good info. Thanks for the post Gang.
David

Adding INDEX causes DEADLOCKS?

I have a very small table (50 rows, 50 columns).
I have 4 servers using the table:
1 server updates the table ("UPDATE table SET col1='', col2='',
LastUpdate=GetDate()").. about 20 rows updated a second.
3 servers pull data ("SELECT * FROM table Where
LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
times a second each.
Prior to last week, i did not have the lastupdate column. but it
obviously made sense to me to add it (why pull everything every 100ms
when you can only pull what you need via the LastUpdate, maybe 3-4
rows at a time). so i add the lastupdate column and make an index on
it.
now as soon as i run it, every 2-4 minutes i get a deadlock (!).
execution path is showing a bookmark lookup with index seek because i
have two conditions in the where clause: "lastupdate>(time
oflastrequest) and COL3>0". i have tried making an index based upon
both lastUpdate,Col3 but the results are the same (lots of deadlocks)
I have removed the index, kept the lastupdate column, same queries,
and everything works fine -- except, of course, that it is doing a
table scan for every lookup. the table will be growing soon and i
will be in trouble. so i need some help here, any ideas?> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
You update the whole table every time? Why?|||"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.com...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
Easiest way to solve these problems is with more locking. I know, it sounds
weird, but more locking will separate the deadlocking processes and properly
serialize them. I suggest in your Update statement add a TABLOCKX hint.
That will exclusively lock the entire table for the duration of the update.
This will also give you more predictable results as the table will only be
read when it is in a completely consistent state between updates.
David|||The deadlock is due to that for the 3 server pulling data, it needs to get
page/row lock on the non-clustered index first, then get page/row lock on
the base table(while holding the lock on the non-clustered index), while the
update gets lock on base table first, then the lock on page/row in the
non-clustered index. To break the circle, you can either create a clustered
index on the LastUpdate field instead of a non-clustered index, or either
specify TABLOCKX hint for the update or specify readpast lock hint for the
reader.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.com...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?|||"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:u4Gn6SuQEHA.2408@.tk2msftngp13.phx.gbl...
> The deadlock is due to that for the 3 server pulling data, it needs to get
> page/row lock on the non-clustered index first, then get page/row lock on
> the base table(while holding the lock on the non-clustered index), while
the
> update gets lock on base table first, then the lock on page/row in the
> non-clustered index. To break the circle, you can either create a
clustered
> index on the LastUpdate field instead of a non-clustered index, or either
> specify TABLOCKX hint for the update or specify readpast lock hint for the
> reader.
> --
> Gang He
> SQL Server Storage Engine Development
Good info. Thanks for the post Gang.
David