about DOL table
There are several things to consider when implementing DATAROW (row level) locking in Sybase ASE:
1. Number of configured locks - The number of required locks is a function of the number of records impacted within a single transaction. For ALLPAGE locking, the number of required locks is a function of the distribution of the rows in a transaction, the number of indexes, the number of levels in the index(es), etc.
If an application is a "pure" OLTP application, it tends to have insert/update/delete transactions which operate on single rows. For these types of transactions, the application will will use FEWER locks with DATAROW than ALLPAGE. (No indexes will have locks.) Fewer rows will be affected by locks also, only those rows of interest.
If an application is a batch-type application which modifies (I/D/U) large number of rows per transaction, page locking MAY provide a savings in lock requirements. Consider the best case scenario of trying to transactionally lock 100 rows spread on five data pages and three levels in one index, you could see five locks in DATAPAGE locking, from five to eleven locks in ALLPAGE locking, and one hundred locks in DATAROW locking. Worst case scenario, the DATAROW locking would still be one hundred locks, DATAPAGE could grow to one hundred locks (for 100 pages) and ALLPAGE locking could grow to one hundred and ten locks.
You can see that the number of locks is more "predictable" with DATAROW, but can be more, same, or less depending upon the application transaction "style".
2. Clustered Indexes and Sorting - While Sybase has never recommended it, some applications take advantage of the inherent sorted state of data in ALLPAGE locked tables with clustered indexes. ANSI requires the order by clause for data that is required to be ordered, but, as stated, some applications used clustered indexes to their advantage.
In DATAROW and DATAPAGE, the concept of a clustered index is not the same and the ordering of data is not guaranteed. Therefore, any table using one of these lock models requires that order by clauses be used where data order is an application requirement.
3. max_rows_per_page is not supported on DOL, exp_row_size is set before conversion occurs.
You can use the following space management properties with DOL tables:
À reservepagegap
À fillfactor
À exp_row_size
Note: Use sp_help and sp_helpindex to view space management properties.
Use reservepagegap to set the ratio of empty to full pages used per extent allocated. The default setting for reservepagegap leaves no gaps in pages.
Use fillfactor to determine how much space to fill on pages when indexes are created. Note that the fill space is not maintained over time. The default setting of 0 for fillfactor fully fills leaf pages.
Use exp_row_size to reduce row forwarding in DOL tables by setting the expected row size. Adaptive Server automatically sets exp_row_size when an allpages locked table which has max_rows_per_page specified is converted to data-only locking. The max_rows_per_page value converts to expected row size. Otherwise, the default value for exp_row_size in DOL tables is complicated: It is 5% of the space available per data page for DOL tables that include variable-length columns.
4. On DOL tables update statistics will use isolation level 0 (dirty reads) and avoid locking the table while running.
5. There is more overhead on DOL tables; page header & row header are longer and variable length columns require an additional two bytes.
6. Chapters 26 - 29 of ASE 12.0 P&T Guide discusses locking in ASE. Customers should at least read chapter 29 which discusses locks and lock promotion.
7. When rows on the table are deleted the row is not physically removed from the DOL page at that time. A flag is set on the row saying that it is logically deleted. The row is usually removed by the Housekeeper's Garbage Collection. However there are some cases in which GC does not remove the row and reorg reclaim_space should be run to physically remove these rows. The size of the DOL tables should be monitored to assure that there are not too many deleted rows present. (Update may also result in rows that are flagged for delete if an update in place does not occur.)
1. Number of configured locks - The number of required locks is a function of the number of records impacted within a single transaction. For ALLPAGE locking, the number of required locks is a function of the distribution of the rows in a transaction, the number of indexes, the number of levels in the index(es), etc.
If an application is a "pure" OLTP application, it tends to have insert/update/delete transactions which operate on single rows. For these types of transactions, the application will will use FEWER locks with DATAROW than ALLPAGE. (No indexes will have locks.) Fewer rows will be affected by locks also, only those rows of interest.
If an application is a batch-type application which modifies (I/D/U) large number of rows per transaction, page locking MAY provide a savings in lock requirements. Consider the best case scenario of trying to transactionally lock 100 rows spread on five data pages and three levels in one index, you could see five locks in DATAPAGE locking, from five to eleven locks in ALLPAGE locking, and one hundred locks in DATAROW locking. Worst case scenario, the DATAROW locking would still be one hundred locks, DATAPAGE could grow to one hundred locks (for 100 pages) and ALLPAGE locking could grow to one hundred and ten locks.
You can see that the number of locks is more "predictable" with DATAROW, but can be more, same, or less depending upon the application transaction "style".
2. Clustered Indexes and Sorting - While Sybase has never recommended it, some applications take advantage of the inherent sorted state of data in ALLPAGE locked tables with clustered indexes. ANSI requires the order by clause for data that is required to be ordered, but, as stated, some applications used clustered indexes to their advantage.
In DATAROW and DATAPAGE, the concept of a clustered index is not the same and the ordering of data is not guaranteed. Therefore, any table using one of these lock models requires that order by clauses be used where data order is an application requirement.
3. max_rows_per_page is not supported on DOL, exp_row_size is set before conversion occurs.
You can use the following space management properties with DOL tables:
À reservepagegap
À fillfactor
À exp_row_size
Note: Use sp_help and sp_helpindex to view space management properties.
Use reservepagegap to set the ratio of empty to full pages used per extent allocated. The default setting for reservepagegap leaves no gaps in pages.
Use fillfactor to determine how much space to fill on pages when indexes are created. Note that the fill space is not maintained over time. The default setting of 0 for fillfactor fully fills leaf pages.
Use exp_row_size to reduce row forwarding in DOL tables by setting the expected row size. Adaptive Server automatically sets exp_row_size when an allpages locked table which has max_rows_per_page specified is converted to data-only locking. The max_rows_per_page value converts to expected row size. Otherwise, the default value for exp_row_size in DOL tables is complicated: It is 5% of the space available per data page for DOL tables that include variable-length columns.
4. On DOL tables update statistics will use isolation level 0 (dirty reads) and avoid locking the table while running.
5. There is more overhead on DOL tables; page header & row header are longer and variable length columns require an additional two bytes.
6. Chapters 26 - 29 of ASE 12.0 P&T Guide discusses locking in ASE. Customers should at least read chapter 29 which discusses locks and lock promotion.
7. When rows on the table are deleted the row is not physically removed from the DOL page at that time. A flag is set on the row saying that it is logically deleted. The row is usually removed by the Housekeeper's Garbage Collection. However there are some cases in which GC does not remove the row and reorg reclaim_space should be run to physically remove these rows. The size of the DOL tables should be monitored to assure that there are not too many deleted rows present. (Update may also result in rows that are flagged for delete if an update in place does not occur.)
posted by James Pei at
9:40 AM
0 Comments:
Post a Comment
<< Home