Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
juicyapple
Posting Yak Master
176 Posts |
 Posted - 2008-11-27 : 03:47:20
|
| I have one problem when multiple jobs save records into tableA. Before insert record, it will check if there is one record with QUEUE status, it will sleep for 2 seconds and try again. But I found that two records can be inserted at the same time. How to prevent this? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-27 : 03:49:19
|
Apply some kind of locking mechanism. Maybe TABLOCK query hint?
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-27 : 03:49:49
|
Or use SERIALIZABLE transaction isolation level?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
juicyapple
Posting Yak Master
176 Posts |
Posted - 2008-11-29 : 10:25:53
|
If the query return 0, the job will insert a queue status record into the table, but what will happen if two jobs submit the command at the same time?SELECT COUNT(*) FROM tbl_queue_status WITH (TABLOCK, HOLDLOCK)WHERE Status = 'Queue'quote:
Originally posted by Peso
Or use SERIALIZABLE transaction isolation level?
E 12°55'05.63"N 56°04'39.26"
|
|
|
|
tmitch
Yak Posting Veteran
60 Posts |
Posted - 2008-11-29 : 11:27:47
|
| If you are using a higher transaction isolation level (REPEATABLE READ should work for this), you wouldn't have both records in the table at the same because that range would be locked for the duration of the transaction. Books Online has a good explanation of the different isolation levels and how they work, if you need more information.hth,Tim---------------------Tim Mitchellwww.BucketOfBits.com |
|
|
|
juicyapple
Posting Yak Master
176 Posts |
Posted - 2008-11-30 : 02:07:24
|
Thanks for your reply. I am currently looking at the info about the locking hints. So far what I know is I can set the isolation levelin table level using the 'WITH (HOLDLOCK)'. Appreciate if anyone can share good articles or links.quote:
Originally posted by tmitch
If you are using a higher transaction isolation level (REPEATABLE READ should work for this), you wouldn't have both records in the table at the same because that range would be locked for the duration of the transaction. Books Online has a good explanation of the different isolation levels and how they work, if you need more information.hth,Tim---------------------Tim Mitchellwww.BucketOfBits.com
|
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-11-30 : 04:00:14
|
| What you are doing sounds questionable. Care to tell us what you are trying to achieve (not the how but the what)? There may be better ways. This way could lock your database up quite badly. Generally picking the isolation level will do it as others have said. |
|
|
|
|
|
|
|
|
Records saved at the same time