Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 853
  • Last Modified:

Is "REPEATABLE READ" / "SERIALIZABLE" required for INSERT ... SELECT ... WHERE NOT EXISTS (...) ?

Hi all,

I have multiple threads at the very same time trying to insert the same record to one table (say TableA) like this:

INSERT INTO TableA (Col1)
SELECT TOP 1 B.Col1 FROM TableB B
WHERE NOT EXISTS (SELECT * FROM TableA A WHERE A.Col1 = B.Col1)

Currently, I use the default isolation level (READ COMMITTED) and found that more than 1 threads are able to insert the same record from TableB into TableA, why?

Do I need to use REPEATABLE READ or SERIALIZABLE instead?

Thank you very much,
Mr_Peerapol
0
Mr_Peerapol
Asked:
Mr_Peerapol
  • 6
  • 5
  • 3
2 Solutions
 
chapmandewCommented:
>>and found that more than 1 threads are able to insert the same record from TableB into TableA,

an isolation level isn't going to stop this....it is just going to make the other processes wait before they can insert their data.  So, for your instance, why do you have different threads inserting the same data?
0
 
Mr_PeerapolAuthor Commented:
TableB contains available tickets.
TableA contains used tickets (the actual requirement is more complex than this).

Each thread tries to get one available ticket from TableB (by using NOT EXISTS). However during high load, I found that more than 1 thread get the same ticket.

I thought INSERT ... SELECT would be treated as one atomic statement and should not cause the problem I am facing now. Probably, I missed some basic database concept here.

Thank you,
0
 
chapmandewCommented:
OK, I understand now.  You're reading the data then doing something with it.  Your best bet is to use serializable for the transaction.  It may slow things down a bit as it is the most restrictive in terms of concurrency, but it should prevent these types of problems.  Give this a shot:

set transaction isolation level serializable

INSERT INTO TableA (Col1)
SELECT TOP 1 B.Col1 FROM TableB B
WHERE NOT EXISTS (SELECT * FROM TableA A WHERE A.Col1 = B.Col1)



0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Mr_PeerapolAuthor Commented:
Thank you very much. I will try it tomorrow when I am at my office (I guess SERIALIZABLE will do the trick).

Would you mind to explain why READ COMMITTED isn't enough for this case and why REPEATABLE READ isn't too?
0
 
chapmandewCommented:
0
 
Mr_PeerapolAuthor Commented:
Thank you very much for a good article.

Do you have a better idea how to do this without SERIALIZABLE? My only requirement is that I cannot add any new column to TableB.

If you mind, I can open a new question.
0
 
chapmandewCommented:
Do you have any type of column in tableB that you can update when the ticket is reserved?  So, if there is a record there and someone reserves it, mark it so that no other thread can pick that specific record.  Then, you can just take that record and insert into tableA.

Also, using 2000 or 2005?
0
 
Mr_PeerapolAuthor Commented:
I am using SQL Server 2000. Actually TableB has a column "CONFIRM_USER" to indicate who "confirms" the ticket. The workflow is that after a thread get an available ticket, a user will confirm it (my program will update CONFIRM_USER column.

I don't want to use this column when I "reserve" a ticket (update it to something like 'reserved by XXX' for example) because other programs also use this column and they expects it to have a value only when the ticket is confirmed.

If this is just the only option, I will need to trade off between performance and big changes.

Thank you very much again.
0
 
chapmandewCommented:
Its not the only option, but it is an option.

Another would be to put a unique constraint on the col1 table in tableA...at least then there would be no duplicates.
0
 
Scott PletcherSenior DBACommented:
I would think a lock hint on table b during the process would be enough, thus serializing only that table, not all of them in the transaction:


INSERT INTO TableA (Col1)
SELECT TOP 1 B.Col1 FROM TableB B WITH (UPDLOCK, HOLDLOCK)
WHERE NOT EXISTS (SELECT * FROM TableA A WHERE A.Col1 = B.Col1)
0
 
Scott PletcherSenior DBACommented:
>>  I use the default isolation level (READ COMMITTED) and found that more than 1 threads are able to insert the same record from TableB into TableA, why? <<

The dual reads are coming from tableB.  All the data on B for the relevant row could be fully committed, and thus available to be read, and it could still be read by multiple transactions simultaneously trying to insert into tableA.  That is, since the data in tableB is already committed, reading it mutliple times does not violate "READ COMMITTED" locking, since multiple reads of the same row can be successfully shared between different tasks.
0
 
chapmandewCommented:
Yep.  The serializable isolation level is the way to go...but, you could get around it if you just added a unique constraint to your tableA field.
0
 
Mr_PeerapolAuthor Commented:
I could not add Unique Constraint on TableA (TICKET_ID, LOCK_STATUS) because multiple records can have the same TICKET_ID and LOCK_STATUS('N') when user rejected the ticket several times. I need some magic constraint for thencolumn TICKET_ID only when LOCK_STATUS is 'Y' ?

SERIALIZABLE is working  for me now, but it causes many DEADLOCKs during high load (more than 100+ concurrent users).
0
 
Scott PletcherSenior DBACommented:
Which is why you should limit seriablizable to as few tables as necessary.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now