Ygthkb

How do I make my single-minded character more interested in the main story?

Can a Way of Shadow Monk use Shadow Step to teleport to a dark ceiling and then body slam another creature?

Why don't you get burned by the wood benches in a sauna?

Have any astronauts or cosmonauts died in space?

Why can all solutions to the simple harmonic motion equation be written in terms of sines and cosines?

Checking if an integer permutation is cyclic in Java

Is it possible to narrate a novel in a faux-historical style without alienating the reader?

How does holding onto an active but un-used credit card affect your ability to get a loan?

Can I do anything else with aspersions other than cast them?

How to transport 10,000 terrestrial trolls across ocean fast?

Separation of mixed plasmid DNA sequences post whole-plasmid sequencing

Python to write multiple dataframes and highlight rows inside an excel file

Determinant of 3x3 matrix by cofactor expansion

Proving the Borel-Cantelli Lemma

Sets which are both Sum-free and Product-free.

Multiple null checks in Java 8

How can guns be countered by melee combat without raw-ability or exceptional explanations?

I am a loser when it comes to jobs, what possibilities do I have?

typeof generic and casted type

How to run unit tests?

Isn't a semicolon (';') needed after a function declaration in C++?

How to purchase a drop bar bike that will be converted to flat bar?

Have the UK Conservatives lost the working majority and if so, what does this mean?

What happens if both players misunderstand the game state until it's too late?

Handle concurrency when insertion depends on reading

Concurrency with Select-conditional Insert/Update/Delete - PostgreSQLHandling concurrent updates, inserting if and only if no record already existsHow to handle too many inserts?Azure SQL Database “Login failed for user” in application, but works fine in SSMSconcurrent insertion in mutually exclusive tables in oracleBest practice regarding concurrency for INSERT into a table with composite primary key?Azuredb The database 'tempdb' has reached its size quotaSQL Server Object ConcurrencyInserts and updates request an Sch-M lockHistory table implementation: “Tuple-versioning” vs Effective Date

3

0

[Short]

I have the following situation: user A attempts to insert data DA into the database. To check whether user A is allowed to insert DA, I need to run a query and do some computation. The problem I'm running into is that while I do the computation, another user (B) also attempts to insert data into the database. Now, suppose both users read the information needed for the computation before new data is inserted, then they might both get cleared for insertion whilst data from user A would forbid user B from inserting, thus leaving the database in a inconsistent state.

How can I solve this kind of concurrency in Azure SQL Database V12?

[Detailed]

The data the user is inserting is the beginning and end of a time interval, such as start: 6:00, end: 7:00.
The requirement is that there must be no time interval overlaps. This means that intervals start: 6:00, end: 9:00 and start: 5:00, end: 6:00 can't both exist.
Currently what I'm doing is checking whether there are any rows that overlap the new interval the user is trying to insert using the following query:

SELECT COUNT(*) FROM [Table1] WHERE Start <= attempEnd && End >= attemptStart

Now, the problem is that multiple users might be trying to insert an interval and these new intervals might overlap each other. However, this information might not be available at the time the query above runs, which causes overlapping intervals being inserted.

How can I solve this kind of concurrency in Azure SQL Database V12?

sql-server concurrency azure-sql-database-v12

share|improve this question

edited Nov 9 '16 at 7:16

Andriy M

16.1k63373

asked Nov 9 '16 at 0:21

victorvictor

1438

bumped to the homepage by Community♦ 1 min ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  There's a constraint-only solution for storing intervals of time with no overlaps by Alex Kuznetsov, if you'd like to take a look.
  
  – Andriy M
  Nov 9 '16 at 7:26
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Wow @AndriyM, that is a bit complicated but sort of elegant. For the case at hand it might make inserting rows tricky if you're trying to insert into a hole in the range.
  
  – mendosi
  Nov 9 '16 at 10:40
  

  
  
  
  

add a comment | 

3

0

[Short]

I have the following situation: user A attempts to insert data DA into the database. To check whether user A is allowed to insert DA, I need to run a query and do some computation. The problem I'm running into is that while I do the computation, another user (B) also attempts to insert data into the database. Now, suppose both users read the information needed for the computation before new data is inserted, then they might both get cleared for insertion whilst data from user A would forbid user B from inserting, thus leaving the database in a inconsistent state.

How can I solve this kind of concurrency in Azure SQL Database V12?

[Detailed]

The data the user is inserting is the beginning and end of a time interval, such as start: 6:00, end: 7:00.
The requirement is that there must be no time interval overlaps. This means that intervals start: 6:00, end: 9:00 and start: 5:00, end: 6:00 can't both exist.
Currently what I'm doing is checking whether there are any rows that overlap the new interval the user is trying to insert using the following query:

SELECT COUNT(*) FROM [Table1] WHERE Start <= attempEnd && End >= attemptStart

Now, the problem is that multiple users might be trying to insert an interval and these new intervals might overlap each other. However, this information might not be available at the time the query above runs, which causes overlapping intervals being inserted.

How can I solve this kind of concurrency in Azure SQL Database V12?

sql-server concurrency azure-sql-database-v12

share|improve this question

edited Nov 9 '16 at 7:16

Andriy M

16.1k63373

asked Nov 9 '16 at 0:21

victorvictor

1438

bumped to the homepage by Community♦ 1 min ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  There's a constraint-only solution for storing intervals of time with no overlaps by Alex Kuznetsov, if you'd like to take a look.
  
  – Andriy M
  Nov 9 '16 at 7:26
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Wow @AndriyM, that is a bit complicated but sort of elegant. For the case at hand it might make inserting rows tricky if you're trying to insert into a hole in the range.
  
  – mendosi
  Nov 9 '16 at 10:40
  

  
  
  
  

add a comment | 

[Short]

I have the following situation: user A attempts to insert data DA into the database. To check whether user A is allowed to insert DA, I need to run a query and do some computation. The problem I'm running into is that while I do the computation, another user (B) also attempts to insert data into the database. Now, suppose both users read the information needed for the computation before new data is inserted, then they might both get cleared for insertion whilst data from user A would forbid user B from inserting, thus leaving the database in a inconsistent state.

How can I solve this kind of concurrency in Azure SQL Database V12?

[Detailed]

The data the user is inserting is the beginning and end of a time interval, such as start: 6:00, end: 7:00.
The requirement is that there must be no time interval overlaps. This means that intervals start: 6:00, end: 9:00 and start: 5:00, end: 6:00 can't both exist.
Currently what I'm doing is checking whether there are any rows that overlap the new interval the user is trying to insert using the following query:

SELECT COUNT(*) FROM [Table1] WHERE Start <= attempEnd && End >= attemptStart

Now, the problem is that multiple users might be trying to insert an interval and these new intervals might overlap each other. However, this information might not be available at the time the query above runs, which causes overlapping intervals being inserted.

How can I solve this kind of concurrency in Azure SQL Database V12?

sql-server concurrency azure-sql-database-v12

share|improve this question

edited Nov 9 '16 at 7:16

Andriy M

16.1k63373

asked Nov 9 '16 at 0:21

victorvictor

1438

SELECT COUNT(*) FROM [Table1] WHERE Start <= attempEnd && End >= attemptStart

share|improve this question

edited Nov 9 '16 at 7:16

Andriy M

16.1k63373

asked Nov 9 '16 at 0:21

victorvictor

1438

share|improve this question

edited Nov 9 '16 at 7:16

Andriy M

16.1k63373

asked Nov 9 '16 at 0:21

victorvictor

1438

edited Nov 9 '16 at 7:16

Andriy M

16.1k63373

edited Nov 9 '16 at 7:16

Andriy M

16.1k63373

asked Nov 9 '16 at 0:21

victorvictor

1438

asked Nov 9 '16 at 0:21

victorvictor

1438

2 Answers
2

active

oldest

votes

0

If I understand correctly, you are doing two separate statements. First check to see if insert is okay, then second do the insert.

I wonder if a trigger might be a solution to your woes. I haven't any experience with creating triggers specifically in Azure, but try something like this:

Create Trigger tr_insertTimeRange On [Table1] For Insert As

Begin

    If 1 < (

        Select Count(*)

          From [Table1] As t

          Join Inserted As i

            On t.attemptEnd >= i.attemptStart

            Or t.attemptStart <= i.attemptEnd)

    Begin

        Raiserror ('Error!', 12, 1);

        Rollback Transaction;

    End

End

Go

After the data is inserted, it counts how many rows in the table overlap the time range of the inserted row. If there are more than 1 (remember, the row has already been inserted so it will overlap) then the transaction is rolled back.

share|improve this answer

answered Nov 9 '16 at 5:10

mendosimendosi

1,974520

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What guarantees that the trigger query wont suffer the same concurrency problems?
  
  – victor
  Nov 9 '16 at 20:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @victor This would prevent inconsistent data, put it wouldn't prevent multiple users from attempting to modify the data at the same time. One of them will fail and should know that they failed.
  
  – mendosi
  Nov 9 '16 at 20:44
  
  
  

  
  
  
  

add a comment | 

0

You can always put in a unique index (clustered or not) on the table to prevent the overlap. This would enforce the business logic by ensuring that only one can succeed.

Another option would be to set the transaction level to serializable which will prevent the problem but probably excessively bottleneck the app.

share|improve this answer

edited Nov 9 '16 at 6:49

answered Nov 9 '16 at 6:04

ErikErik

3,97931954

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I thought about a check constraint too, but how would you design this check constraint to get the desired effect?
  
  – mendosi
  Nov 9 '16 at 6:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @mendosi Good point I should have said a unique index clustered or not. It is late and I'm answering on my phone while watching the US election results
  
  – Erik
  Nov 9 '16 at 6:48
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How woul you add a unique constraint to something that is computed? Remember that this is not a simple comparison of the start and end of the interval. How can I store the interval itself so that I can make it unique
  
  – victor
  Nov 9 '16 at 12:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @victor An indexed view might be able to do the trick. I would need to have the table schema to try and write the view though and have a chance to ingest some caffeine. This whole plan might be unworkable though. I'm not at my best while watching politics late at night
  
  – Erik
  Nov 9 '16 at 13:59
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f154664%2fhandle-concurrency-when-insertion-depends-on-reading%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

If I understand correctly, you are doing two separate statements. First check to see if insert is okay, then second do the insert.

I wonder if a trigger might be a solution to your woes. I haven't any experience with creating triggers specifically in Azure, but try something like this:

Create Trigger tr_insertTimeRange On [Table1] For Insert As

Begin

    If 1 < (

        Select Count(*)

          From [Table1] As t

          Join Inserted As i

            On t.attemptEnd >= i.attemptStart

            Or t.attemptStart <= i.attemptEnd)

    Begin

        Raiserror ('Error!', 12, 1);

        Rollback Transaction;

    End

End

Go

After the data is inserted, it counts how many rows in the table overlap the time range of the inserted row. If there are more than 1 (remember, the row has already been inserted so it will overlap) then the transaction is rolled back.

share|improve this answer

answered Nov 9 '16 at 5:10

mendosimendosi

1,974520

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What guarantees that the trigger query wont suffer the same concurrency problems?
  
  – victor
  Nov 9 '16 at 20:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @victor This would prevent inconsistent data, put it wouldn't prevent multiple users from attempting to modify the data at the same time. One of them will fail and should know that they failed.
  
  – mendosi
  Nov 9 '16 at 20:44
  
  
  

  
  
  
  

add a comment | 

0

If I understand correctly, you are doing two separate statements. First check to see if insert is okay, then second do the insert.

I wonder if a trigger might be a solution to your woes. I haven't any experience with creating triggers specifically in Azure, but try something like this:

Create Trigger tr_insertTimeRange On [Table1] For Insert As

Begin

    If 1 < (

        Select Count(*)

          From [Table1] As t

          Join Inserted As i

            On t.attemptEnd >= i.attemptStart

            Or t.attemptStart <= i.attemptEnd)

    Begin

        Raiserror ('Error!', 12, 1);

        Rollback Transaction;

    End

End

Go

After the data is inserted, it counts how many rows in the table overlap the time range of the inserted row. If there are more than 1 (remember, the row has already been inserted so it will overlap) then the transaction is rolled back.

share|improve this answer

answered Nov 9 '16 at 5:10

mendosimendosi

1,974520

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What guarantees that the trigger query wont suffer the same concurrency problems?
  
  – victor
  Nov 9 '16 at 20:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @victor This would prevent inconsistent data, put it wouldn't prevent multiple users from attempting to modify the data at the same time. One of them will fail and should know that they failed.
  
  – mendosi
  Nov 9 '16 at 20:44
  
  
  

  
  
  
  

add a comment | 

If I understand correctly, you are doing two separate statements. First check to see if insert is okay, then second do the insert.

I wonder if a trigger might be a solution to your woes. I haven't any experience with creating triggers specifically in Azure, but try something like this:

Create Trigger tr_insertTimeRange On [Table1] For Insert As

Begin

    If 1 < (

        Select Count(*)

          From [Table1] As t

          Join Inserted As i

            On t.attemptEnd >= i.attemptStart

            Or t.attemptStart <= i.attemptEnd)

    Begin

        Raiserror ('Error!', 12, 1);

        Rollback Transaction;

    End

End

Go

After the data is inserted, it counts how many rows in the table overlap the time range of the inserted row. If there are more than 1 (remember, the row has already been inserted so it will overlap) then the transaction is rolled back.

share|improve this answer

answered Nov 9 '16 at 5:10

mendosimendosi

1,974520

Create Trigger tr_insertTimeRange On [Table1] For Insert As

Begin

    If 1 < (

        Select Count(*)

          From [Table1] As t

          Join Inserted As i

            On t.attemptEnd >= i.attemptStart

            Or t.attemptStart <= i.attemptEnd)

    Begin

        Raiserror ('Error!', 12, 1);

        Rollback Transaction;

    End

End

Go

share|improve this answer

answered Nov 9 '16 at 5:10

mendosimendosi

1,974520

answered Nov 9 '16 at 5:10

mendosimendosi

1,974520

answered Nov 9 '16 at 5:10

mendosimendosi

1,974520

0

You can always put in a unique index (clustered or not) on the table to prevent the overlap. This would enforce the business logic by ensuring that only one can succeed.

Another option would be to set the transaction level to serializable which will prevent the problem but probably excessively bottleneck the app.

share|improve this answer

edited Nov 9 '16 at 6:49

answered Nov 9 '16 at 6:04

ErikErik

3,97931954

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I thought about a check constraint too, but how would you design this check constraint to get the desired effect?
  
  – mendosi
  Nov 9 '16 at 6:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @mendosi Good point I should have said a unique index clustered or not. It is late and I'm answering on my phone while watching the US election results
  
  – Erik
  Nov 9 '16 at 6:48
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How woul you add a unique constraint to something that is computed? Remember that this is not a simple comparison of the start and end of the interval. How can I store the interval itself so that I can make it unique
  
  – victor
  Nov 9 '16 at 12:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @victor An indexed view might be able to do the trick. I would need to have the table schema to try and write the view though and have a chance to ingest some caffeine. This whole plan might be unworkable though. I'm not at my best while watching politics late at night
  
  – Erik
  Nov 9 '16 at 13:59
  

  
  
  
  

add a comment | 

0

You can always put in a unique index (clustered or not) on the table to prevent the overlap. This would enforce the business logic by ensuring that only one can succeed.

Another option would be to set the transaction level to serializable which will prevent the problem but probably excessively bottleneck the app.

share|improve this answer

edited Nov 9 '16 at 6:49

answered Nov 9 '16 at 6:04

ErikErik

3,97931954

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I thought about a check constraint too, but how would you design this check constraint to get the desired effect?
  
  – mendosi
  Nov 9 '16 at 6:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @mendosi Good point I should have said a unique index clustered or not. It is late and I'm answering on my phone while watching the US election results
  
  – Erik
  Nov 9 '16 at 6:48
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How woul you add a unique constraint to something that is computed? Remember that this is not a simple comparison of the start and end of the interval. How can I store the interval itself so that I can make it unique
  
  – victor
  Nov 9 '16 at 12:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @victor An indexed view might be able to do the trick. I would need to have the table schema to try and write the view though and have a chance to ingest some caffeine. This whole plan might be unworkable though. I'm not at my best while watching politics late at night
  
  – Erik
  Nov 9 '16 at 13:59
  

  
  
  
  

add a comment | 

You can always put in a unique index (clustered or not) on the table to prevent the overlap. This would enforce the business logic by ensuring that only one can succeed.

Another option would be to set the transaction level to serializable which will prevent the problem but probably excessively bottleneck the app.

share|improve this answer

edited Nov 9 '16 at 6:49

answered Nov 9 '16 at 6:04

ErikErik

3,97931954

share|improve this answer

edited Nov 9 '16 at 6:49

answered Nov 9 '16 at 6:04

ErikErik

3,97931954

answered Nov 9 '16 at 6:04

ErikErik

3,97931954

answered Nov 9 '16 at 6:04

ErikErik

3,97931954