|
|
You're in the
sql server programming
group:Multiuser Problem
sql server programming:
If I use the pessimistic type, can other users view the record (but not edit it) and return a message telling that another person is editing the record, or does this type lock the record such that it is unavailable until the editor releases it? An explanation of pessimistic and optimistic lock types would be really useful, as would some example code. My project is to maintenance system with multiple users who can all edit every record. However, I need to control the edit function so that nobody edits a record that is in the process of being edited by someone else. Also I Don't want to to use a Database Column for FLAG to Maintain the Edit Mode. My Database is SQL Server with VB with ADO is the Programming TOOL. Any ideas? Thanks Prabhat
vishalsu@online.microsoft.com ISSUE: ======= An explanation of pessimistic and optimistic lock types would be really useful, as would some example code. RESOLUTION =============== In a multiuser environment, there are two models for updating data in a database: optimistic concurrency, and pessimistic concurrency. The DataSet object is designed to encourage the use of optimistic concurrency for long-running activities such as when you are remoting data and when users are interacting with data. Pessimistic concurrency involves locking rows at the data source to prevent users from modifying data in a way that affects other users. In a pessimistic model, when a user performs an action that causes a lock to be applied, other users cannot perform actions that would conflict with the lock until the lock owner releases it. This model is primarily used in environments where there is heavy contention for data, where the cost of protecting data with locks is less than the cost of rolling back transactions if concurrency conflicts occur. Therefore, in a pessimistic currency model, a user who reads a row with the intention of changing it establishes a lock. Until the user has finished the update and released the lock, no one else can change that row. For this reason, pessimistic concurrency is best implemented when lock times will be short, as in programmatic processing of records. Pessimistic concurrency is not a scalable option when users are interacting with data, causing records to be locked for relatively large periods of time. By contrast, users who use optimistic concurrency do not lock a row when reading it. When a user wants to update a row, the application must determine whether another user has changed the row since it was read. Optimistic concurrency is generally used in environments with a low contention for data. This improves performance as no locking of records is required, and locking of records requires additional server resources. Also, in order to maintain record locks, a persistent connection to the database server is required. Because this is not the case in an optimistic concurrency model, connections to the server are free to serve a larger number of clients in less time. In an optimistic concurrency model, a violation is considered to have occurred if, after a user receives a value from the database, another user modifies the value before the first user has attempted to modify it. The following tables follow an example of optimistic concurrency. At 1:00 p.m., User1 reads a row from the database with the following values: CustID LastName FirstName 101 Smith Bob Column name Original value Current value Value in database CustID 101 101 101 LastName Smith Smith Smith FirstName Bob Bob Bob At 1:01 p.m., User2 reads the same row. At 1:03 p.m., User2 changes FirstName from "Bob" to "Robert" and updates the database. Column name Original value Current value Value in database CustID 101 101 101 LastName Smith Smith Smith FirstName Bob Robert Bob The update succeeds because the values in the database at the time of update match the original values that User2 has. At 1:05 p.m., User1 changes Bob's first name to "James" and tries to update the row. Column name Original value Current value Value in database CustID 101 101 101 LastName Smith Smith Smith FirstName Bob James Robert At this point, User1 encounters an optimistic concurrency violation because the values in the database no longer match the original values that User1 was expecting. The decision now needs to be made whether to overwrite the changes supplied by User2 with the changes supplied by User1, or to cancel the changes by User1. Testing for Optimistic Concurrency Violations There are several techniques for testing for an optimistic concurrency violation. One involves including a timestamp column in the table. Databases commonly provide timestamp functionality that can be used to identify the date and time when the record was last updated. Using this technique, a timestamp column is included in the table definition. Whenever the record is updated, the timestamp is updated to reflect the current date and time. In a test for optimistic concurrency violations, the timestamp column is returned with any query of the contents of the table. When an update is attempted, the timestamp value in the database is compared to the original timestamp value contained in the modified row. If they match, the update is performed and the timestamp column is updated with the current time to reflect the update. If they do not match, an optimistic concurrency violation has occurred. Another technique for testing for an optimistic concurrency violation is to verify that all the original column values in a row still match those found in the database. For example, consider the following query: SELECT Col1, Col2, Col3 FROM Table1 To test for an optimistic concurrency violation when updating a row in Table1, you would issue the following UPDATE statement: UPDATE Table1 Set Col1 = @NewCol1Value, Set Col2 = @NewCol2Value, Set Col3 = @NewCol3Value WHERE Col1 = @OldCol1Value AND Col2 = @OldCol2Value AND Col3 = @OldCol3Value As long as the original values match the values in the database, the update is performed. If a value has been modified, the update will not modify the row because the WHERE clause will not find a match. Note that it is recommended to always return a unique primary key value in your query. Otherwise, the preceding UPDATE statement may update more than one row, which might not be your intent. If a column at your data source allows nulls, you may need to extend your WHERE clause to check for a matching null reference in your local table and at the data source. For example, the following UPDATE statement verifies that a null reference in the local row still matches a null reference at the data source, or that the value in the local row still matches the value at the data source. UPDATE Table1 Set Col1 = @NewVal1 WHERE (@OldVal1 IS NULL AND Col1 IS NULL) OR Col1 = @OldVal1 You may also choose to apply less restrictive criteria when using an optimistic concurrency model. For example, using only the primary key columns in the WHERE clause results in the data being overwritten regardless of whether the other columns have been updated since the last query. You can also apply a WHERE clause only to specific columns, resulting in data being overwritten unless particular fields have been updated since they were last queried. The DataAdapter.RowUpdated Event The DataAdapter.RowUpdated event can be used in conjunction with the techniques described earlier, to provide notification to your application
vishalsu@online.microsoft.com Was this information helpul? This posting is provided "AS IS" with no warranties, and confers no rights.
Hi Vishal,
Thanks a LOT. Because I have learnt a LOT from Your Repply. Your Reply not only helped me but it also Gave me a Correct Way to Go with Updates. Can We Write a Trigger (Before Update) in SQL Server so that Before the Row gets updated by any SQL Statement we should be able to check the old value and new value and raise ERROR? Is that Possible. Can U give me some Idea. Thanks Prabhat [quoted text, click to view] "vishal subramaniam" <vishalsu@microsoft.com> wrote in message
news:WgDYODtIEHA.3156@cpmsftngxa06.phx.gbl... > > vishalsu@online.microsoft.com > > ISSUE: > ======= > An explanation of pessimistic and optimistic lock types would be really > useful, as would some example code. > > RESOLUTION > =============== > > In a multiuser environment, there are two models for updating data in a > database: optimistic concurrency, and pessimistic concurrency. The DataSet > object is designed to encourage the use of optimistic concurrency for > long-running activities such as when you are remoting data and when users > are interacting with data. > > Pessimistic concurrency involves locking rows at the data source to prevent > users from modifying data in a way that affects other users. In a > pessimistic model, when a user performs an action that causes a lock to be > applied, other users cannot perform actions that would conflict with the > lock until the lock owner releases it. This model is primarily used in > environments where there is heavy contention for data, where the cost of > protecting data with locks is less than the cost of rolling back > transactions if concurrency conflicts occur. > > Therefore, in a pessimistic currency model, a user who reads a row with the > intention of changing it establishes a lock. Until the user has finished > the update and released the lock, no one else can change that row. For this > reason, pessimistic concurrency is best implemented when lock times will be > short, as in programmatic processing of records. Pessimistic concurrency i s > not a scalable option when users are interacting with data, causing records > to be locked for relatively large periods of time. > > By contrast, users who use optimistic concurrency do not lock a row when > reading it. When a user wants to update a row, the application must > determine whether another user has changed the row since it was read. > Optimistic concurrency is generally used in environments with a low > contention for data. This improves performance as no locking of records is > required, and locking of records requires additional server resources. > Also, in order to maintain record locks, a persistent connection to the > database server is required. Because this is not the case in an optimistic > concurrency model, connections to the server are free to serve a larger > number of clients in less time. > > In an optimistic concurrency model, a violation is considered to have > occurred if, after a user receives a value from the database, another user > modifies the value before the first user has attempted to modify it. > > The following tables follow an example of optimistic concurrency. > > At 1:00 p.m., User1 reads a row from the database with the following values: > > CustID LastName FirstName > > 101 Smith Bob > > Column name Original value Current value Value in database > CustID 101 101 101 > LastName Smith Smith Smith > FirstName Bob Bob Bob > > At 1:01 p.m., User2 reads the same row. > > At 1:03 p.m., User2 changes FirstName from "Bob" to "Robert" and updates > the database. > > Column name Original value Current value Value in database > CustID 101 101 101 > LastName Smith Smith Smith > FirstName Bob Robert Bob > > The update succeeds because the values in the database at the time of > update match the original values that User2 has. > > At 1:05 p.m., User1 changes Bob's first name to "James" and tries to update > the row. > > Column name Original value Current value Value in database > CustID 101 101 101 > LastName Smith Smith Smith > FirstName Bob James Robert > > At this point, User1 encounters an optimistic concurrency violation because > the values in the database no longer match the original values that User1 > was expecting. The decision now needs to be made whether to overwrite the > changes supplied by User2 with the changes supplied by User1, or to cancel > the changes by User1. > > Testing for Optimistic Concurrency Violations > There are several techniques for testing for an optimistic concurrency > violation. One involves including a timestamp column in the table. > Databases commonly provide timestamp functionality that can be used to > identify the date and time when the record was last updated. Using this > technique, a timestamp column is included in the table definition. Whenever > the record is updated, the timestamp is updated to reflect the current date > and time. In a test for optimistic concurrency violations, the timestamp > column is returned with any query of the contents of the table. When an > update is attempted, the timestamp value in the database is compared to the > original timestamp value contained in the modified row. If they match, the > update is performed and the timestamp column is updated with the current > time to reflect the update. If they do not match, an optimistic concurrency > violation has occurred. > > Another technique for testing for an optimistic concurrency violation is to > verify that all the original column values in a row still match those found > in the database. For example, consider the following query: > > SELECT Col1, Col2, Col3 FROM Table1 > To test for an optimistic concurrency violation when updating a row in > Table1, you would issue the following UPDATE statement: > > UPDATE Table1 Set Col1 = @NewCol1Value, > Set Col2 = @NewCol2Value, > Set Col3 = @NewCol3Value > WHERE Col1 = @OldCol1Value AND > Col2 = @OldCol2Value AND > Col3 = @OldCol3Value > As long as the original values match the values in the database, the update > is performed. If a value has been modified, the update will not modify the > row because the WHERE clause will not find a match. > > Note that it is recommended to always return a unique primary key value in > your query. Otherwise, the preceding UPDATE statement may update more than > one row, which might not be your intent. > > If a column at your data source allows nulls, you may need to extend your > WHERE clause to check for a matching null reference in your local table and > at the data source. For example, the following UPDATE statement verifies > that a null reference in the local row still matches a null reference at > the data source, or that the value in the local row still matches the value > at the data source. > > UPDATE Table1 Set Col1 = @NewVal1
Hi Vishal,
Yes. The information was Very Very Useful to me. Thanks Prabhat [quoted text, click to view] "vishal subramaniam" <vishalsu@microsoft.com> wrote in message news:U37WqJ2IEHA.3812@cpmsftngxa06.phx.gbl... > > vishalsu@online.microsoft.com > > Was this information helpul? > > This posting is provided "AS IS" with no warranties, and confers no rights. >
vishalsu@online.microsoft.com ISSUE: ====== Can We Write a Trigger (Before Update) in SQL Server so that Before the Row gets updated by any SQL Statement we should be able to check the old value and new value and raise ERROR? Is that Possible. Can U give me some Idea. RESOLUTION/ LINKS; ===================== Exploring SQL Server Triggers : --------------------------------------------- Triggers are one of the core tools available in relational databases such as SQL Server™ 2000. As one of the mainstays of SQL Server database programming, triggers also happen to be one of the topics that I get most of the questions about. In this month's installment of Data Points, I will explore the different trigger types that SQL Server 2000 makes available along with many of the features that they expose. When used properly, triggers can play a key role in a data model and in implementing enterprise-wide business rules. Triggers can be implemented to enforce business rules or referential data integrity in database applications. There are even types of triggers that open the doors to possibilities such as allowing data modifications to multiple base tables of a view. It is very important to evaluate your options when choosing to employ a trigger. In deciding whether you'll use triggers, the key is to balance functionality, scalability, maintenance, and performance. I'll explore some of these factors and offer some insight on how to weigh them. In addition, I'll explain the foundation of SQL Server triggers and the features that they expose. I will examine the differences between the two types of triggers while demonstrating the places where each can be useful. Then I'll show examples of using AFTER triggers that serve an important role in SQL Server-based applications. I will also walk through the use of triggers to enforce referential integrity and to implement business rule validation at the database level. Before wrapping up, I will discuss performance considerations, features unique to triggers, and some limitations to keep in mind. Trigger Types The first ingredient in properly employing triggers is to understand the differences between AFTER and INSTEAD OF triggers. AFTER triggers are the same type of trigger that is available in previous versions of SQL Server. They are also known as "FOR triggers" or even simply as "triggers" since they were the only type of trigger available prior to SQL Server 2000. Let's first look at FOR triggers. You'll notice that the following trigger is created using the FOR keyword: CREATE TRIGGER tr_Employees_U on Employees FOR UPDATE AS IF UPDATE(lastname) BEGIN RAISERROR ('cannot change lastname', 16, 1) ROLLBACK TRAN RETURN END GO This trigger, tr_Employees_U will execute after an UPDATE statement is run against the Employees table. It will then check to see if the lastname field was modified and if so it will raise an error and undo the changes that the UPDATE statement made. To accomplish this, this code uses three very common features of triggers: UPDATE, RAISERROR, and ROLLBACK TRANS. I will explain these in more detail later. That syntax is also acceptable in older versions of SQL Server. However, now that there are two types of triggers in SQL Server 2000, I prefer to refer to FOR triggers as AFTER triggers. Thus, for the remainder of this article I will refer to either AFTER or INSTEAD OF triggers. AFTER triggers execute following the triggering action, such as an insert, update, or delete. The example trigger you just saw will fire after an UPDATE statement has been executed against the Employees table. Therefore, the trigger does not fire until the row or rows have been inserted and constraints have been checked and passed. Basically, AFTER triggers fire very late in the process. INSTEAD OF triggers, introduced with SQL Server 2000, are intended to be employed in different situations. INSTEAD OF triggers fire in place of the triggering action. For example, if an INSTEAD OF UPDATE trigger exists on the Employees table and an UPDATE statement is executed against the Employees table, the UPDATE statement will not change a row in the Employees table. Instead, the UPDATE statement causes the INSTEAD OF UPDATE trigger to be executed, which may or may not modify data in the Employees table. The following trigger will fire in place of any UPDATE statement made against the Employees table: CREATE TRIGGER tr_Employees_U_insteadof ON Employees INSTEAD OF UPDATE AS IF UPDATE(lastname) BEGIN RAISERROR ('cannot change lastname (source = instead of)', 16, 1) ROLLBACK TRAN RETURN END ELSE -- Go ahead and do the update or some other business rules here GO Like the AFTER trigger you saw earlier, this trigger prevents changes from being made to the lastname field. However, it implements this business rule differently than the previous example. Because the INSTEAD OF trigger fires in place of the UPDATE statement, the INSTEAD OF trigger then evaluates if the business rule test passes or not. If the business rule test passes, in order for the update to occur the INSTEAD OF trigger must explicitly invoke the UPDATE statement again. In this situation, the AFTER trigger would be a more efficient mechanism to enforce this business rule because there is no overwhelming benefit of the INSTEAD OF technique and the AFTER trigger requires less complex code logic. This is especially true if you wanted the INSTEAD OF trigger to perform the original update since it would have to reconstruct the statement. This is possible using the UPDATE function and the inserted and deleted tables, but it is much more complicated than necessary. One question I get frequently is whether you can have multiple triggers hanging off of a single table. There can be several AFTER triggers associated with a single table, even multiple AFTER triggers on the same action query type (UPDATE, INSERT, or DELETE). For example, the Employees table could have two distinct AFTER UPDATE triggers associated with the table, each of which performs a different set of tasks. In fact, there could be three or four, or even more AFTER triggers associated with the same table; they will all fire following the UPDATE statement. (Keep in mind that even though there could be several triggers associated with the same table, they each must have a unique name.) You can even tell SQL Server which order you want the AFTER triggers to fire by using the system stored procedure sp_settriggerorder. However, I have rarely wanted to have more than one trigger hanging from the same table upon the same action query, and even when I did, I didn't care about
Hi, Vishal
Thanks Again. The Information Helped me a Lot. I have Got so much of information and knowledge from you. Do You know Now U R My Inspiration for any DB Programming. Now I am Exploring the Triggers. Please do send me what ever you feel i should learn. As the Main Threed for this Message is now lost I think it is quite difficult to access this threed. But I Will Still keep on searchin your Materials. You can Even send the Details or any other Good Posting like this which will have Details from the Scratch to my Email: nathprabhat@hotmail.com Thanks Prabhat [quoted text, click to view] "vishal subramaniam" <vishalsu@microsoft.com> wrote in message
news:HicttjqJEHA.3088@cpmsftngxa10.phx.gbl... > > vishalsu@online.microsoft.com > > ISSUE: > ====== > Can We Write a Trigger (Before Update) in SQL Server so that Before the Row > gets updated by any SQL Statement we should be able to check the old value > and new value and raise ERROR? Is that Possible. Can U give me some Idea. > > > RESOLUTION/ LINKS; > ===================== > Exploring SQL Server Triggers : > --------------------------------------------- > > Triggers are one of the core tools available in relational databases such > as SQL ServerT 2000. As one of the mainstays of SQL Server database > programming, triggers also happen to be one of the topics that I get most > of the questions about. In this month's installment of Data Points, I will > explore the different trigger types that SQL Server 2000 makes available > along with many of the features that they expose. When used properly, > triggers can play a key role in a data model and in implementing > enterprise-wide business rules. Triggers can be implemented to enforce > business rules or referential data integrity in database applications. > There are even types of triggers that open the doors to possibilities such > as allowing data modifications to multiple base tables of a view. > It is very important to evaluate your options when choosing to employ a > trigger. In deciding whether you'll use triggers, the key is to balance > functionality, scalability, maintenance, and performance. I'll explore some > of these factors and offer some insight on how to weigh them. In addition, > I'll explain the foundation of SQL Server triggers and the features that > they expose. I will examine the differences between the two types of > triggers while demonstrating the places where each can be useful. Then I'll > show examples of using AFTER triggers that serve an important role in SQL > Server-based applications. I will also walk through the use of triggers to > enforce referential integrity and to implement business rule validation at > the database level. Before wrapping up, I will discuss performance > considerations, features unique to triggers, and some limitations to keep > in mind. > > Trigger Types > The first ingredient in properly employing triggers is to understand the > differences between AFTER and INSTEAD OF triggers. AFTER triggers are the > same type of trigger that is available in previous versions of SQL Server. > They are also known as "FOR triggers" or even simply as "triggers" since > they were the only type of trigger available prior to SQL Server 2000. > Let's first look at FOR triggers. You'll notice that the following > trigger is created using the FOR keyword: > CREATE TRIGGER tr_Employees_U on Employees FOR UPDATE AS > IF UPDATE(lastname) > BEGIN > RAISERROR ('cannot change lastname', 16, 1) > ROLLBACK TRAN > RETURN > END > GO > > This trigger, tr_Employees_U will execute after an UPDATE statement is run > against the Employees table. It will then check to see if the lastname > field was modified and if so it will raise an error and undo the changes > that the UPDATE statement made. To accomplish this, this code uses three > very common features of triggers: UPDATE, RAISERROR, and ROLLBACK TRANS. I > will explain these in more detail later. > That syntax is also acceptable in older versions of SQL Server. However, > now that there are two types of triggers in SQL Server 2000, I prefer to > refer to FOR triggers as AFTER triggers. Thus, for the remainder of this > article I will refer to either AFTER or INSTEAD OF triggers. > AFTER triggers execute following the triggering action, such as an > insert, update, or delete. The example trigger you just saw will fire after > an UPDATE statement has been executed against the Employees table. > Therefore, the trigger does not fire until the row or rows have been > inserted and constraints have been checked and passed. Basically, AFTER > triggers fire very late in the process. > INSTEAD OF triggers, introduced with SQL Server 2000, are intended to be > employed in different situations. INSTEAD OF triggers fire in place of the > triggering action. For example, if an INSTEAD OF UPDATE trigger exists on > the Employees table and an UPDATE statement is executed against the > Employees table, the UPDATE statement will not change a row in the > Employees table. Instead, the UPDATE statement causes the INSTEAD OF UPDATE > trigger to be executed, which may or may not modify data in the Employees > table. > The following trigger will fire in place of any UPDATE statement made > against the Employees table: > CREATE TRIGGER tr_Employees_U_insteadof ON Employees INSTEAD OF UPDATE AS > IF UPDATE(lastname) > BEGIN > RAISERROR ('cannot change lastname (source = instead of)', 16, 1) > ROLLBACK TRAN > RETURN > END > ELSE > -- Go ahead and do the update or some other business rules here > > GO > > Like the AFTER trigger you saw earlier, this trigger prevents changes from > being made to the lastname field. However, it implements this business rule > differently than the previous example. Because the INSTEAD OF trigger fires > in place of the UPDATE statement, the INSTEAD OF trigger then evaluates if > the business rule test passes or not. If the business rule test passes, in > order for the update to occur the INSTEAD OF trigger must explicitly invoke > the UPDATE statement again. > In this situation, the AFTER trigger would be a more efficient mechanism > to enforce this business rule because there is no overwhelming benefit of > the INSTEAD OF technique and the AFTER trigger requires less complex code > logic. This is especially true if you wanted the INSTEAD OF trigger to > perform the original update since it would have to reconstruct the > statement. This is possible using the UPDATE function and the inserted and > deleted tables, but it is much more complicated than necessary. > One question I get frequently is whether you can have multiple triggers > hanging off of a single table. There can be several AFTER triggers
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |