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 |
|
nomikhanxyz
Starting Member
6 Posts |
 Posted - 2013-06-27 : 09:30:43
|
| Hi There! I have some real time questions, if some can help me I really appreciate. thanks in advance.What is the effect of setting the transaction isolation level to Serializable ? if we assume the transaction does a select from Table A, then an update to Table B and an insert to Table C. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-27 : 10:27:10
|
| It is the most restrictive of the isolation levels - even though you are only selecting from table a, until the transaction is committed (assuming you are doing all three in a transaction), others cannot modify those rows in table a (or the range of rows that have been locked) until you commit or rollback the transaction.The description here is brief and to the point: http://msdn.microsoft.com/en-us/library/ms173763.aspx |
 |
|
|
nomikhanxyz
Starting Member
6 Posts |
Posted - 2013-06-27 : 10:55:57
|
| Thanks a lot James for your help. |
|
|
|
nomikhanxyz
Starting Member
6 Posts |
Posted - 2013-06-27 : 11:02:35
|
| so let say if locks does a SELECT statement place on tables we are assuming the transaction isolation level is set to Read Committed.If in case it does place a lock than what is the effect of that lock?. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-27 : 11:55:22
|
| Read Committed prevents dirty reads - i.e., you will not read data that another process has modified, but not yet committed. However, consider the scenario where you have 3 statements in a transaction. First, select from TableA. Second, select from Table B. Third, Select from TableA (again, using the same query in the first step). In this case, with read_committed, you may get different result sets in the first and third selects. What that means is that someone else could come and modify the table between the two selects. This is what they refer to as repeatable read.Google for some web pages that describe the isolation levels and dirty reads, repeatable reads, phantom reads etc. There are lot of good articles on the subject. For example: http://www.sqlservercentral.com/blogs/vivekssqlnotes/2012/08/19/isolation-levels-in-sql-server/ |
|
|
|
nomikhanxyz
Starting Member
6 Posts |
Posted - 2013-06-27 : 13:06:56
|
| James my friend u r great indeed. thanks a lot |
|
|
|
|
|
|
|
|
Serializable