Sfrgttk

Is there a name for this series?

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

Are all power cords made equal?

Why is it that Bernie Sanders always called a "socialist"?

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

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

bash aliases do not expand even with shopt expand_aliases

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

Disk space full during insert, what happens?

How can I handle players killing my NPC outside of combat?

What sort of grammatical construct is ‘Quod per sortem sternit fortem’?

What is the Buddhist view in Socratic questioning?

How bad is a Computer Science course that doesn't teach Design Patterns?

How can I prep for the Curse of Strahd adventure effectively?

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

How to deal with an underperforming colleague?

What is an explicit bijection in combinatorics?

What does "don't have a baby" imply or mean in this sentence?

How do I narratively explain how in-game circumstances do not mechanically allow a PC to instantly kill an NPC?

Is there a configuration of the 8-puzzle where locking a tile makes it harder?

Why write a book when there's a movie in my head?

How do I add a strong "onion flavor" to the biryani (in restaurant style)?

Sed-Grep-Awk operations

Boss asked me to sign a resignation paper without a date on it along with my new contract

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♦ 9 mins 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♦ 9 mins 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