| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2005-08-01 : 11:48:51
|
| If some Insertion (15000 records) are being done into a table and at the same time user(s) are getting some information from that table (SELECT), then what happens to the user(s)? Do they experience slow down in retrieving their data? I don't think there would be any lock involved but do the user(s) have to wait until the insertion is completed? In general, what is the SQL behavior?Canada DBA |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-08-01 : 12:13:23
|
| Inserts cause exclusive locks and Selects cause shared locks. The exclusive locks will block the shared locks (barring the use unadvisable table hints).====================================================Regards,Sean RoussyGENERAL ADVICE FOR EVERYONE: Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-01 : 12:26:59
|
| What will an exclusive lock on an inserted row block - just that [physical] page?Kristen |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-08-01 : 12:46:15
|
| Kristen,you know I am not sure. Perhaps 15K records is not a good example. But I have seen it where inserting a large number of records will hold up select statements unless you use a table hint.I imagine each row being no more than, what is it 8060 KB or 1 page (the numbers may not be exact here), is locked for the time of the insert to complete, and if you are inserting 2 million records each succesive page lock will block the SELECT statement form completing and such an insert can take considerable amount of time depending on your hardware.====================================================Regards,Sean RoussyGENERAL ADVICE FOR EVERYONE: Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own. |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-08-01 : 13:05:47
|
| 1. So, there would be lock on a page and it might impact the SELECTs?2. How about if I use SELECT * FROM MyTable (NOLOCK) WHERE COL1 = 13. What is table hints?Canada DBA |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-08-01 : 13:17:11
|
| 1. yes2. this is a table hint but you will get a "dirty read". Meaning your results will not reflect the result of your insert.3. see #2.SET JOKING ONAll of this of course must meet Krisitns approval because anyone with under 1K posts is a dirty hack.SET JOKING OFF====================================================Regards,Sean RoussyGENERAL ADVICE FOR EVERYONE: Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-08-01 : 13:53:50
|
| SQL will start with row locking, but will quickly escalate to page or even table level locking. This is done automatically, unless you provide a hint to override this feature. This happens for all locking types (even exclusive).as stated before, reads grab a SHARED lock. Inserts/Update/Deletes grab an EXLCUSIVE lock. Multiple reads can grab SHARED locks simultaneously on the same resource (they are compatible). However, mutliple EXCLUSIVE locks cannot exist, only one can. An EXCLUSIVE lock is not compatible with a SHARED lock, so it must wait until the SHARED lock goes away before it is granted. The bottom line to all of this is that reads can block writes, and often do.More information on locking is out there. Here is a good BOL article on it: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_7xde.asp -ec |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-08-01 : 14:51:05
|
Thank you for the complete discussion. But the SELECT’s lock gets a very short time and therefore, we can imagine that the Insert wouldn’t wait long. Am I right?My concern was vice versa, I am wonder about the users that while the records are being Inserted in the middle of the day, do they feel slow down or not? Based on what our friends have mentioned, it could be but not for 15000 rows. quote:
Originally posted by eyechart
... An EXCLUSIVE lock is not compatible with a SHARED lock, so it must wait until the SHARED lock goes away before it is granted. The bottom line to all of this is that reads can block writes, and often do....-ec
Canada DBA |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-08-01 : 17:53:30
|
quote:
Originally posted by farhadr
Thank you for the complete discussion. But the SELECT’s lock gets a very short time and therefore, we can imagine that the Insert wouldn’t wait long. Am I right?My concern was vice versa, I am wonder about the users that while the records are being Inserted in the middle of the day, do they feel slow down or not? Based on what our friends have mentioned, it could be but not for 15000 rows.
limiting the duration and the scope of the locks on a busy system will definitely increase the number of concurrent operations your database can sustain.the point to all of this is that READS block WRITES and WRITES block READS. The fact that READS block WRITES is the really big issue.-ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-02 : 00:29:29
|
"Perhaps 15K records is not a good example"Indeed, what we are on here is the escallation issue I suspect. And that, in turn, probably depends on how many rows there are in the table.I am also unsure what would happen with a CLUSTERED INDEX on the PK if the PK is using "random" keys. Those 15,000 inserts could be all over the table, which is going to cause some severe locking issues I would expect.Whereas inserting them all with increasing keys, such as IDENTITY, will bung them all at the end of the PK index, with probably less impact [unless you wind up with a table lock though ...].farhadr: I'd recommend that if you are worried about this (in particular if the number of inserts might grow in the future) that you build the import tool so that it can do it in batches if that is possible [e.g. doesn't jeopardise the referential integrity of the DB etc.]One answer would be to import them, initially, into a staging table and then insert them from there:DECLARE @intRowCount intSELECT @intRowCount = 1 -- Force first loopWHILE @intRowCount > 0BEGIN SET ROWCOUNT 1000 INSERT INTO MyRealTable SELECT S.* FROM MyStagingTable S LEFT OUTER JOIN MyRealTable T ON T.MyPK = S.MyPK WHERE T.MyPK IS NULL -- an ORDER BY would probably be a good idea - makes it "repeatable" SELECT @intRowCount = @@ROWCOUNT SET ROWCOUNT 0END Kristen |
|
|
|
|
Does Insert affects the Selects?