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
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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,
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)
An article I wrote on it:
http://articles.techrepubl
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.
>> 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.
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).
Business Accounts
Answer for Membership
by: chapmandewPosted on 2008-06-24 at 11:10:24ID: 21858533
>>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?