Question & Answer
Question
Why do rollbacks cause a read/write limit error?
Answer
If you attempt a SQL statement, you may receive the following error message:
ERROR: limit of concurrent read/write transactions reached, update operations not permitted
The current NPS architecture is such that only 31 concurrent read/write transactions are possible.
To understand the failure, first look at the results of Active Queries, either by using the NPS Admin GUI or by running nzsession from the command line.
Besides Active Queries, check to see if there are any current rollbacks. Multiple transaction rollbacks may sometimes be hiding in a single recovery interaction (type 233), especially after a state change (or fresh nzstart). To check for running rollbacks, run one of the following:
- nzdumptxjournal /nz/data/TxJournal
- nzsqa responders -sys | egrep "215|233"
The results of nzdumptxjournal /nz/data/TxJournal may appear similar to:
bash-2.05a$ nzdumptxjournal /nz/data/TxJournal
Reading entries needing recovery from journal file '/nz/data/TxJournal':
Op ObjectId TXid RecoveryAction
------ -------- -------- --------------
Delete 3168558 000f054a Abort:RollbackThis output indicates that one Delete statement is rolling back. Further details on this statement can be had by running the following, specifying the ID from the previous example:
nzsql -c "select t.objname as TableName, d.objname as DatabaseName from _t_object t, _t_object d where t.objdb=d.objid and t.objid=3168558"
tablename | databasename
-----------+--------------
invoices | proddb1
(1 row)This output shows that the running roll back is a DELETE statement on the table named 'invoices' in the database named 'proddb1'.
The results of nzsqa responders -sys | egrep "215|233" may appear similar to:
IntId Plan Id CmdCode - Count 1 - 8 9 - 16 17 - 24 25 - 32 33 - 40 41 - 48 49 - 56 57 - 64 65 - 72 73 - 80 81 - 88 89 - 96 97 - 104 105 - 112 113 - 120 121 - 128 129 - 136 137 - 144 145 - 152 153 - 160 161 - 168 169 - 176 177 - 184 185 - 192 193 - 200 201 - 208 209 - 216
-------- -------------- ------- ------- ----- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- ---------
962578 137393 (9/9) 215 Respond 210 11111111 11111111 11111111 11111111 11111111 11111111 11111111 11111011 11110111 11111111 11111111 11111111 11111111 11111111 11111111 11111111 11110111 11111111 11110111 11110111 11111111 11111111 11110111 11111111 11111111 11111111 11111111 This output indicates that there are 216 SPUs still processing the DELETE rollback. Over time, as SPUs completes their rollbacks, you will see the 'Respond ###' decrease. For example:
IntId Plan Id CmdCode - Count 1 - 8 9 - 16 17 - 24 25 - 32 33 - 40 41 - 48 49 - 56 57 - 64 65 - 72 73 - 80 81 - 88 89 - 96 97 - 104 105 - 112 113 - 120 121 - 128 129 - 136 137 - 144 145 - 152 153 - 160 161 - 168 169 - 176 177 - 184 185 - 192 193 - 200 201 - 208 209 - 216
-------- -------------- ------- ------- ----- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- ---------
962578 137393 (9/9) 215 Respond 210 11111111 11111111 11111111 11111111 11111111 11111111 11111111 11111011 11110111 11111111 11111111 11111111 11111111 11111111 11111111 11111111 11110111 11111111 11110111 11110111 11111111 11111111 11110111 11111111 11111111 11111111 11111111
Historical Number
NZ446169
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21516991