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 |
|
anupkeskar24
Starting Member
2 Posts |
 Posted - 2009-06-15 : 03:03:29
|
| I need to maintain TRANSACTION for multiple insert queries, if one of goes fail, TRANSACTION should ROLLBACK.at the same time my table(s) for i have written queries should not get locked for other to fetch data.What is the methode to do this in SQL Server 2005?Anup Keskar |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-06-15 : 04:23:56
|
| There is no way to prevent locking from happening inside a transaction or when doing updates to a table. What you can do though is to have proper indexes so that the query optimizer doesn't lock more rows than it actually needs to. You can also provide locking hints to your queries but they are not guaranteed to be followed...that's why they are called "hints". Also try to keep your transactions as small and efficient as possible...- Lumbago |
 |
|
|
anupkeskar24
Starting Member
2 Posts |
Posted - 2009-06-15 : 04:51:04
|
| But it block access to even other SQL server client application for those tables...??? until the Transaction gets COMMITED/ROLLBACKED.Anup Keskar |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-06-15 : 04:59:01
|
| Then you either update too many rows so the query optimizer escalates to a full table lock or your update statements are not using an index. If you don't know very much about indexes/query optimization you can post your queries and table structures and we'll help you tuning them.- Lumbago |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-15 : 06:03:01
|
Read about SNAPSHOT TRANSACTION. It will very much do what you ask for.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
Transaction Maintaining