Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>An <code>UPDATE</code> in a SQL Server will <strong>always</strong> have to take an exclusive lock - no way / no trick / no hack around this. </p> <p>But this exclusive lock is just on the single row that's being updated - and it will be released after the transaction is done.</p> <p>However: if you update more than 5000 rows in single transaction, SQL Server will escalate the row-level exclusive locks to a table-level lock - essentially locking the entire table until the transaction is completed.</p> <p>I see two options here:</p> <ul> <li><p>if you can live with this approach, you could break up your import into chunks of e.g. 2000 rows in a single transaction; this way, you could avoid the table-level exclusive locks, and your system would remain more responsive during import. The downside would be: it might take a bit longer, and you might end up with some groups of rows being imported, while others fail - you're not getting a clean "all or nothing" import for the whole file anymore</p></li> <li><p>there are some trace flags / switches you can enable that would turn off this lock escalation mechanism, thus your entire import of 40000 rows would be handled by row-level locks. This on the other hand puts a lot of stress and extra work on the lock management system, so that might require more time and/or more RAM to handle all those exclusive locks, and queries running during import might be slowed down (if 39999 individual exclusive locks have to be checked)</p></li> </ul>
    singulars
    1. This table or related slice is empty.
    1. PODataset Update without Locking SQL Server 40,000+ Rows
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USmarc_s
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PODataset Update without Locking SQL Server 40,000+ Rows
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COGuys thanks for your answers. Unfortunately I am using a online SQL Server running 2008 and an in house VB.NET to update records. Since I do not own the database a batch import is out, which would really make this simple to massage the data to an import file and send it up. I am using update statements and in one case submitting a table to update, which as Marc says locks the table until done. We are small and this works at 2-3 am so we can live with it for now. We are hoping MS Azure provides an answer if we can get past the VPN router issues. @marc
      singulars
      1. PO
      1. USshack
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload