Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
MS SQL Server: updates and locking (Read 2651 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
MS SQL Server: updates and locking
17.05.09 at 13:46:19
 
MS SQL Server: updates and locking
 
In this topic I would like to show how locking occurs in the MS SQL Server database when updates are made. When updates are done with SQL*XL or with any other tool, other users may notice these updates. It can lead to other users experiencing locked tables. It all has to do with read consistency.
 
Read consistency is a database feature where changes applied by one user are not yet visible to other users. You may ask yourself how it works if one user runs a long query summing a column of a table for example when other users at the same time are updating values that will affect the end result. This problem is fixed by implementing read consistency where it is avoided that the querying user will see results that are not yet finalised. As soon as the updating users issue a commit the changes are visible to other users. If they do not commit the changes are not visible. In Oracle users will not even notice that there are changes being made by others but in SQL Server, as we will see in a minute, row locks will be visible to other users that query the table.
 
To show you a concrete example please consider the following. I have used a MS SQL Server 2005. The tool to observe locks is the sp_locks SQL Server stored procedure. I have created a simpel table with 2 column. I am updating a varchar column. The SQL*XL program is used to apply the updates and more specifically, it is the multiple rows update feature in that software that is being used.
 
I am creating 2 sessions to the database. One using SQL*XL and one with the SQL Server Enterprise manager. In SQL Server I run 2 SQL statements: sp_locks and select * from mytable. In SQL*XL I run select * from mytable and after editing some values I am running the multiplr rows update feature to post the changes back to the database. Notice that I am not setting the commit each row option. The changes are therefore not committed to the database until I explicitly do so in SQL*XL.
 
When starting the exercise SQL server does not show any locks and the select * will work just fine. After running the SQL*XL updates locks will be shown in SQL server. This is the indication that another user - the SQL*XL user - has applied changes and these changes are not committed. SQL server has locked the rows. When I run the select * from mytable in SQL server the result cannot be shown as the session waits for the locks to clear. I can do a select * from mytable where id=123 if this row was not updated by the SQL*XL session. After issuing a commit in the SQL*XL session, the locks clear and the query select * from mytable can run again without problems.
 
It must therefore not be forgotten to commit after applying updates to a table in SQL Server. I do not think this is a problem in Oracle. I do not think Oracle will create row locks when other sessions are making updates. These updates are simply not visible.
 
I found that a notification at the end of the multiple rows update feature in SQL*XL should be handy warning the user that uncommitted rows remain. SQL*XL will offer the user to issue a final commit to make the changes effective and to lift any row locks created.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1