Why is SQL Server 2008 blocking SELECT's on long transaction INSERT's? -


We are trying to keep a transactional table which only includes new records on a regular basis.

This simple table requires us to constantly add new records over time. The amount of transaction is expected to be quite high in this table, and it may be a batch import from the transaction (> 1000) from time to time, which may take several seconds to complete.

With this data, we again set a set of select statements that group different columns to return the values ​​required.

From our initial test, we get a barrier to be related to a SQL Server that blocks our SELECT in the middle of INSERTS transactions. / P>

Below is a simple example that can be run to clarify the problem.

- LOCK_TEST (LOCK_TEST_ID int identity, amount int) for creating ordinary DB table 

  table;

- Run it in 1 query window 

  Start the tran entry in the LOCK_TEST (AMOUNT) values ​​(1); WAITFOR DELAY '00: 00: 15' ---- Insert 15-second delay in LOCK_TEST (AMOUNT) values ​​(1); Commit

- Run it in Parallel in Query 2 

  Select SUM (AMOUNT) from LOCK_TEST;

I hope that query 2 will return immediately, until the 0 to 1 query is completed, and then 2 will show. We do not want to return 1 from 2 queries.

We belong to the selector statement (NOLOCK) of the answer. But it violates the limits of transaction, and the returned information can be financially in nature and we do not want to see any unanswered details in our questions.

My problem seems to the INSERT ...
Why does INSERT block the statement of selection even if it is not modifying any existing data?

Bonus Issue Q: Is this "feature" of SQL Server, or can we find it on other database flavors?

UPDATE I now had time to find a local Oracle database and run the same simple test. Pass this test as I expected.

Ie can run as many queries as I want, and it will return empty until the first transaction, then 2 returns.

Is there any way to work with SQL Server this way? Or do we need to move to Oracle?

This locking behavior is a feature of SQL Server. With 2005 and above, you can use the same result (which is used by default on Oracle); Do not block your selection This puts extra stress on tempdb because tempdb maintains the row level version, so make sure you adjust for this. To run SQL for the way in which you want to do it, run: 

  ALTER DATABASE MYDatabase SET ALLOW_SNAPSHOT_ISOLATION at ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON

Comments

Post a Comment

Popular posts from this blog

python - Overriding the save method in Django ModelForm -

I'm having trouble overriding a ModelForm save method. I get this error I'm: Exception type: TypeErier Exception Value: Save () found an unexpected keyword argument 'committed' My intentions include a form 3 fields Submit several values ​​for, then create an object for each combination of those areas, and to save each object file models.py class collarsult type (models.Model): id = model. AutoField (db_column = 'icontact_result_code_type_id', primary_key = true) callResult = models.ForeignKey ( 'CallResult', db_column = 'icontact_result_code_id') campaign = models.ForeignKey ( 'campaign', db_column = 'icampaign_id') CALLTYPE = models.ForeignKey ( 'CALLTYPE', db_column = 'icall_type_id') agent = models.BooleanField (db_column = 'bagent', default = true) teamLeader = models.BooleanField (db_column = 'bTeamLeader', default = true) active = models.BooleanField (db_column = Django.form...
Read more

html - CSS autoheight, but fit content to height of div -

I have a div in which there are three children- div in the left and middle div: float: left while true: float: true because it is my layout Actually will spoil, I've used ClearFix Hack: .cf {zoom: 1; }. Cf: First,. Cf: {content: ""; Display: Table; }. Cf: After {clear: both; } It still works, but I want to be the right div an indicator. So it should fill 100% of the parent unit of height. How can I complete this? PS is the full code: & Lt; / Div & gt; & Lt; Div class = "mobile_content" & gt; & Lt; H5 & gt; {{Data.titel}} & lt; / H5> & Lt; Table & gt; & Lt; TR & gt; & Lt; Th & gt; Komponist: & lt; / Th & gt; & Lt; TD & gt; {{Data.komponist}} & lt; / TD & gt; & Lt; / TR & gt; & Lt; TR & gt; & Lt; Th & gt; Instrument: & lt; / Th & gt; & Lt; TD & gt; {{Data.instrumente}} & lt; / TD & gt; & Lt; / TR & gt;...
Read more

qt - How to prevent QAudioInput from automatically boosting the master volume to 100%? -

I have been trying to use Qt5 multimedia to record audio with QAudioInput, however, when I see If QAudioInput is started, it increases the master volume of my sound device to 100%. How can I prevent QAudioInput from changing the master volume? My current development platform is Linux with PalsAdio (with flat audio disabled). How can I use QAudioInput : QAudioDeviceInfo device_info = QAudioDeviceInfo :: defaultInputDevice (); QAudio format format; Format.setSampleRate (44100); Format.setChannelCount (1); Format.setSampleSize (16); Format.setCodec ("Audio / PCM"); Format.setSampleType (QAudioFormat :: SignedInt); Format.setByteOrder (QAudioFormat :: LittleEndian); Std :: cout & lt; & Lt; Device_info.deviceName (). ToUtf8 (). ConstData () & lt; & Lt; Std :: endl; QAudioInput * default_device = new QAudioInput (device_info, format); QIODevice * default_io_device = default_device-> Start (); a QAudioInput.setVolume () method is not you I ...
Read more